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)
Top comments (0)