IF-THEN-ELSIF-ELSE
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END IF;
IF-THEN-ELSIF-ELSE is used to execute the corresponding statement by detecting multiple conditions in the IF statement. In general, it is equivalent to the nested use of the IF-THEN-ELSE-IF-THEN command, with the difference that only one END IF statement is required.
The following example uses the IF-THEN-ELSIF-ELSE statement to calculate the number of employees in the $25,000 compensation range.
\set PLSQL_MODE on CREATE OR REPLACE PROCEDURE emp_test_4() IS v_empno emp.empno%TYPE; v_comp NUMBER(8,2); v_lt_25K SMALLINT := 0; v_25K_50K SMALLINT := 0; v_50K_75K SMALLINT := 0; v_75K_100K SMALLINT := 0; v_ge_100K SMALLINT := 0; CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp order by 1; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_empno, v_comp; EXIT WHEN emp_cursor%NOTFOUND; IF v_comp < 25000 THEN v_lt_25K := v_lt_25K + 1; ELSIF v_comp < 50000 THEN v_25K_50K := v_25K_50K + 1; ELSIF v_comp < 75000 THEN v_50K_75K := v_50K_75K + 1; ELSIF v_comp < 100000 THEN v_75K_100K := v_75K_100K + 1; ELSE v_ge_100K := v_ge_100K + 1; END IF; END LOOP; CLOSE emp_cursor; DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation'); DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K); DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K); DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K); DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K); DBMS_OUTPUT.PUT_LINE('100,000 and over : ' || v_ge_100K); END; / \set PLSQL_MODE off Here is the output of this program:
postgres=# select emp_test_4(); NOTICE: Number of employees by yearly compensation NOTICE: Less than 25,000 : 4 NOTICE: 25,000 - 49,9999 : 3 NOTICE: 50,000 - 74,9999 : 1 NOTICE: 75,000 - 99,9999 : 1 NOTICE: 100,000 and over : 0 EMP_TEST_4 ------------ (1 row) 
Top comments (0)