PL/SQL IF Statement

Summary: in this tutorial, you will learn how to use PL/SQL IF statement to control the code execution conditionally.

Introduction to PL/SQL IF Statement

The PL/SQL IF statement allows you to execute a sequence of statements conditionally. The IF statement evaluates a condition. The condition can be anything that evaluates to a logical value of true or false such as comparison expression or a combination of multiple comparison expressions. You can compare two variables of the same type or convertible type. You can compare two literals. In addition, a Boolean variable can be used as a condition.

The PL/SQL IF statement has three forms: IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF.

PL/SQL IF-THEN Statement

The following illustrates the IF-THEN statement:

IF condition THEN
   sequence_of_statements;
END IF;Code language: SQL (Structured Query Language) (sql)

This is the simplest form of the IF statement. If the condition evaluates to true, the sequence of statements will execute. If the condition is false or NULL, the IF statement does nothing. Note that END IF is used to close the IF statement, not ENDIF.

The following example demonstrates the PL/SQL IF statement. It updates employee’s salary to mid-range if employee’s salary is lower than the mid-range.

DECLARE
  n_min_salary NUMBER(6,0);
  n_max_salary NUMBER(6,0);
  n_mid_salary NUMBER(6,2);
  n_salary     EMPLOYEES.SALARY%TYPE;
  n_emp_id     EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
BEGIN
  -- get salary range of the employee
  -- based on job
  SELECT min_salary,
         max_salary
  INTO n_min_salary,
       n_max_salary
  FROM JOBS
  WHERE JOB_ID = (SELECT JOB_ID
                 FROM EMPLOYEES
                 WHERE EMPLOYEE_ID = n_emp_id);

  -- calculate mid-range
  n_mid_salary := (n_min_salary + n_max_salary) / 2;
  -- get salary of the given employee
  SELECT salary
  INTO n_salary
  FROM employees
  WHERE employee_id = n_emp_id;

  -- update employee's salary if it is lower than
  -- the mid range
  IF n_salary < n_mid_salary THEN
    UPDATE employees
    SET salary = n_mid_salary
    WHERE employee_id = n_emp_id;
  END IF;
END;Code language: SQL (Structured Query Language) (sql)

PL/SQL IF-THEN-ELSE Statement

This is the second form of the IF statement. The ELSE clause is added with the alternative sequence of statements. Below is the syntax of the IF-ELSE statement.

IF condition THEN
   sequence_of_if_statements;
ELSE
   sequence_of_else_statements;
END IF;Code language: SQL (Structured Query Language) (sql)

If the condition is NULL or false, the sequence of else statements will execute.

Suppose you want to increase salary for an employee to mid-range if the current salary is lower than the mid-range of the job otherwise, increase it by 5%. In this case, you can change the code above using PL/SQL IF-THEN-ELSE statement as follows:

  -- update employee's salary if it is lower than
  -- the mid range, otherwise increase 5%
  IF n_salary < n_mid_salary THEN
    UPDATE employees
    SET salary = n_mid_salary
    WHERE employee_id = n_emp_id;
  ELSE
    UPDATE employees
    SET salary = salary + salary * 5 /100
    WHERE employee_id = n_emp_id;
  END IF;Code language: SQL (Structured Query Language) (sql)

PL/SQL IF-THEN-ELSIF Statement

PL/SQL supports IF-THEN-ELSIF statement to allow you to execute a sequence of statements based on multiple conditions.

The syntax of PL/SQL IF-THEN-ELSIF is as follows:

IF condition1  THEN
   sequence_of_statements1
ELSIF condition2 THEN
   sequence_of_statements2
ELSE
   sequence_of_statements3
END IF;Code language: SQL (Structured Query Language) (sql)

Note that an IF statement can have any number of ELSIF clauses. If the first condition is false or NULL, the second condition in  ELSIF is checked and so on. If all conditions are NULL or false, the sequence of statements in the ELSE clause will execute.

Notice that the final ELSE clause is optional so if can omit it. If any condition from top to bottom is true, the corresponding sequence of statements will execute.

The following example illustrates the PL/SQL IF-THEN-ELSIF statement to print out the corresponding message when employee’s salary is higher than mid-range, lower than mid-range or equal to mid-range.

DECLARE
  n_min_salary NUMBER(6,0);
  n_max_salary NUMBER(6,0);
  n_mid_salary NUMBER(6,2);
  n_salary     EMPLOYEES.SALARY%TYPE;
  n_emp_id     EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
BEGIN
  -- get salary range of the employee
  -- based on job
  SELECT min_salary,
         max_salary
  INTO n_min_salary,
       n_max_salary
  FROM JOBS
  WHERE JOB_ID = (SELECT JOB_ID
                 FROM EMPLOYEES
                 WHERE EMPLOYEE_ID = n_emp_id);

  -- calculate mid-range
  n_mid_salary := (n_min_salary + n_max_salary) / 2;
  -- get salary of the given employee
  SELECT salary
  INTO n_salary
  FROM employees
  WHERE employee_id = n_emp_id;

  -- update employee's salary if it is lower than
  -- the mid range, otherwise increase 5%
  IF n_salary > n_mid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || TO_CHAR(n_emp_id) ||
                         ' has salary $' || TO_CHAR(n_salary) ||
                         ' higher than mid-range $' || TO_CHAR(n_mid_salary));
  ELSIF n_salary < n_mid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || TO_CHAR(n_emp_id) ||
                         ' has salary $' || TO_CHAR(n_salary) ||
                         ' lower than mid-range $' || TO_CHAR(n_mid_salary));

  ELSE
    DBMS_OUTPUT.PUT_LINE('Employee ' || TO_CHAR(n_emp_id) ||
                         ' has salary $' || TO_CHAR(n_salary) ||
                         ' equal to mid-range $' || TO_CHAR(n_mid_salary));
  END IF;
END;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you’ve learned how to use various forms of the PL/SQL IF statement including IF-THEN IF-THEN-ELSE and IF-THEN-ELSIF statements.