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)