IF-THEN-ELSE IF v16
You can nest IF statements. This allows you to invoke alternative IF statements once it's determined whether the conditional of an outer IF statement is TRUE or FALSE.
In this example, the outer IF-THEN-ELSE statement tests whether an employee has a commission. The inner IF-THEN-ELSE statements then test whether the employee’s total compensation exceeds or is less than the company average.
Note
The logic in this program can be simplified by calculating the employee’s yearly compensation using the NVL function in the SELECT command of the cursor declaration. However, the purpose of this example is to show the use of IF statements.
DECLARE
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;
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;The following is the output from this program:
Average Yearly Compensation: $ 53,528.57 EMPNO YEARLY COMP ----- ----------- 7369 $ 19,200.00 Below Average 7499 $ 45,600.00 Below Average 7521 $ 42,000.00 Below Average 7566 $ 71,400.00 Exceeds Average 7654 $ 63,600.00 Exceeds Average 7698 $ 68,400.00 Exceeds Average 7782 $ 58,800.00 Exceeds Average 7788 $ 72,000.00 Exceeds Average 7839 $ 120,000.00 Exceeds Average 7844 $ 36,000.00 Below Average 7876 $ 26,400.00 Below Average 7900 $ 22,800.00 Below Average 7902 $ 72,000.00 Exceeds Average 7934 $ 31,200.00 Below Average
When you use this form, you're actually nesting an IF statement inside the ELSE part of an outer IF statement. Thus you need one END IF statement for each nested IF and one for the parent IF-ELSE.