IF statement (PL/SQL)
Use the IF statement within PL/SQL contexts to execute SQL statements on the basis of certain criteria.
The four forms of the IF statement are:
- IF...THEN...END IF
- IF...THEN...ELSE...END IF
- IF...THEN...ELSE IF...END IF
- IF...THEN...ELSIF...THEN...ELSE...END IF
IF...THEN...END IF
The syntax of this statement
is:
IF boolean-expression THEN
statements
END IF;
IF...THEN statements are the simplest form
of IF. The statements between THEN and END IF are executed only if
the condition evaluates to TRUE. In the following example, an IF...THEN
statement is used to test for and to display those employees who have
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'));
END IF;
END LOOP;
CLOSE emp_cursor;
END;
This program generates the following sample output:EMPNO COMM
----- -------
7499 $300.00
7521 $500.00
7654 $1400.00
IF...THEN...ELSE...END IF
The syntax of this statement
is:
IF boolean-expression THEN
statements
ELSE
statements
END IF;
IF...THEN...ELSE statements specify an alternative
set of statements that should be executed if the condition evaluates
to FALSE. In the following example, the previous example is modified
so that an IF...THEN...ELSE statement is used to display the text
Non-commissionif an employee does not have 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;
This program generates the following sample output: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
IF...THEN...ELSE IF...END IF
The syntax of this
statement is:
IF boolean-expression THEN
IF boolean-expression THEN
statements
ELSE
IF boolean-expression THEN
statements
END IF;
You can nest IF statements so that alternative
IF statements are invoked, depending on whether the conditions of
an outer IF statement evaluate to TRUE or FALSE. In the following
example, the outer IF...THEN...ELSE statement tests whether or not an
employee has a commission. The inner IF...THEN...ELSE statements subsequently
test whether the employee's total compensation exceeds or is
less than the company average. When you use this form of the IF statement,
you are actually nesting an IF statement inside of the ELSE part of
an outer IF statement. You therefore need one END IF for each nested
IF and one for the parent IF...ELSE. (Note that the logic in this program
can be simplified considerably by calculating each employee's
yearly compensation using an NVL function within the SELECT statement
of the cursor declaration; however, the purpose of this example is
to demonstrate how IF statements can be used.)
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
--
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 whether the employee's compensation with commission exceeds
-- the company 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 whether the employee's compensation without commission exceeds
-- the company 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;
This program generates the following sample output: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
IF...THEN...ELSIF...THEN...ELSE...END IF
The syntax
of this statement is:
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END IF;
IF...THEN...ELSIF...ELSE statements provide the
means for checking many alternatives in one statement. Formally, this
statement is equivalent to nested IF...THEN...ELSE...IF...THEN statements,
but only one END IF is needed. The following example uses an IF...THEN...ELSIF...ELSE
statement to count the number of employees by compensation, in steps
of $25,000.
DECLARE
v_empno emp.empno%TYPE;
v_comp NUMBER(8,2);
v_lt_25K SMALLINT := 0;
v_25K_50K SMALLINT := 0;
v_50K_75K SMALLINT := 0;
v_75K_100K SMALLINT := 0;
v_ge_100K SMALLINT := 0;
CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno, v_comp;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_comp < 25000 THEN
v_lt_25K := v_lt_25K + 1;
ELSIF v_comp < 50000 THEN
v_25K_50K := v_25K_50K + 1;
ELSIF v_comp < 75000 THEN
v_50K_75K := v_50K_75K + 1;
ELSIF v_comp < 100000 THEN
v_75K_100K := v_75K_100K + 1;
ELSE
v_ge_100K := v_ge_100K + 1;
END IF;
END LOOP;
CLOSE emp_cursor;
DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
DBMS_OUTPUT.PUT_LINE('100,000 and over : ' || v_ge_100K);
END;
This program generates the following sample output:Number of employees by yearly compensation
Less than 25,000 : 2
25,000 - 49,9999 : 5
50,000 - 74,9999 : 6
75,000 - 99,9999 : 0
100,000 and over : 1