IF-THEN-ELSIF-ELSE v15
Syntax
IF boolean-expression THEN <statements> [ ELSIF boolean-expression THEN <statements> [ ELSIF boolean-expression THEN <statements> ] ...] [ ELSE <statements> ] END IF;
IF-THEN-ELSIF-ELSE
provides a method of checking many alternatives in one statement. Formally it is equivalent to nested IF-THEN-ELSE-IF-THEN
commands, but only one END IF
is needed.
Example
The following example uses an IF-THEN-ELSIF-ELSE
statement to count the number of employees by compensation ranges of $25,000:
DECLARE 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; 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;
The following is the output from this program:
Output
Number of employees by yearly compensation Less than 25,000 : 2 25,000 - 49,9999 : 5 50,000 - 74,9999 : 6 75,000 - 99,9999 : 0 100,000 and over : 1