AntDB-Oracle Compatibility Developer’s Manual P4–25

IF-THEN-ELSE IF

IF statements can be nested so that a different inner IF statement can be called depending on the conditional return value of the outer IF statement.

In the following example, the outer IF-THEN-ELSE statement tests whether an employee has a commission. The inner IF-THEN-ELSE statement tests whether the employee's total compensation is more or less than the company average.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_3()
IS
    v_empno emp.empno%TYPE;
    v_sal emp.sal%TYPE;
    v_comm emp.comm%TYPE;
    v_avg NUMBER(7,2);
    CURSOR emp_cursor IS SELECT empno, sal, comm FROM emp order by 1;
BEGIN
--
--  Calculate the average yearly compensation in the company
--
    SELECT AVG((sal + NVL(comm,0)) * 24) INTO v_avg FROM emp;
    DBMS_OUTPUT.PUT_LINE('Average Yearly Compensation: ' ||TO_CHAR(v_avg,'$999,999.99'));
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO YEARLY COMP');
    DBMS_OUTPUT.PUT_LINE('----- -----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_sal, v_comm;
        EXIT WHEN emp_cursor%NOTFOUND;
        --
        --  Test whether or not the employee gets a commission
        --
        IF v_comm IS NOT NULL AND v_comm > 0 THEN
            --
            --  Test if the employee's compensation with commission exceeds the average
            --
            IF (v_sal + v_comm) * 24 > v_avg THEN
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Exceeds Average');
            ELSE
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Below Average');
            END IF;
        ELSE
            --
            --  Test if the employee's compensation without commission exceeds the average
            --
            IF v_sal * 24 > v_avg THEN
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Exceeds Average');
            ELSE
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Below Average');
            END IF;
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

Note: We can also use the NVL function in the cursor's SELECT command to calculate the employee's annual compensation amount, which simplifies the logic of the program. The purpose of this example is to demonstrate that the IF statement can achieve the same function.

The following is the output of this program.

postgres=# select emp_test_3();
NOTICE:  Average Yearly Compensation: $  36,287.73
NOTICE:  EMPNO YEARLY COMP
NOTICE:  ----- -----------
NOTICE:  1001 $  39,741.60 Exceeds Average
NOTICE:  1002 $  45,600.00 Exceeds Average
NOTICE:  1003 $  19,200.00 Below Average
NOTICE:  1004 $  19,200.00 Below Average
NOTICE:  1005 $  19,200.00 Below Average
NOTICE:  1006 $  19,200.00 Below Average
NOTICE:  1007 $  30,048.00 Below Average
NOTICE:  1008 $  79,200.00 Exceeds Average
NOTICE:  1009 $  55,200.00 Exceeds Average
 EMP_TEST_3
------------

(1 row)

When using this form of IF statement, there is actually an IF statement nested inside the ELSE of the outer IF statement. Therefore, it is necessary to provide an END IF statement for each nested IF statement and an END IF statement for the outermost IF-ELSE.