AntDB-Oracle Compatibility Developer’s Manual P4–24

IF-THEN-ELSE

IF boolean-expression THEN

statements

ELSE

statements

END IF;

If the return value of the conditional expression in the IF-THEN-ELSE statement is FALSE, then the statement after ELSE will be executed.

Now we have modified the previous example by replacing the IF-THEN statement with the IF-THEN-ELSE statement. This will display the text message 'Non-commission' when it detects that the employee has no commission.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_2()
IS
    v_empno emp.empno%TYPE;
    v_comm emp.comm%TYPE;
    CURSOR emp_cursor IS SELECT empno, comm FROM emp order by 1;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
    DBMS_OUTPUT.PUT_LINE('----- -------');
    LOOP
        FETCH emp_cursor INTO v_empno, 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
            DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
        ELSE
            DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || 'Non-commission');
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of this program.

postgres=# select emp_test_2();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1003 Non-commission
NOTICE:  1004 Non-commission
NOTICE:  1005 Non-commission
NOTICE:  1006 Non-commission
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_2
------------

(1 row)