AntDB-Oracle Compatibility Developer’s Manual P4–26

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)