DEV Community

AntDB
AntDB

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)