IF boolean-expression THEN
<statements>
ELSE
<statements>
END IF; IF-THEN-ELSE
statements add to IF-THEN
by letting you specify an alternative set of statements that should be executed if the condition evaluates to false.
The previous example is modified so an IF-THEN-ELSE
statement is used to display the text Non-commission
if the employee does not get a commission.
DECLARE
v_empno emp.empno%TYPE;
v_comm emp.comm%TYPE;
CURSOR emp_cursor IS SELECT empno, comm FROM emp;
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; The following is the output from this program.
EMPNO COMM
----- -------
7369 Non-commission
7499 $ 300.00
7521 $ 500.00
7566 Non-commission
7654 $ 1400.00
7698 Non-commission
7782 Non-commission
7788 Non-commission
7839 Non-commission
7844 Non-commission
7876 Non-commission
7900 Non-commission
7902 Non-commission
7934 Non-commission