1. Database PL/SQL Language Reference
  2. PL/SQL Control Statements
  3. Conditional Selection Statements

Conditional Selection Statements

The conditional selection statements, IF and CASE, run different statements for different data values.

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms:

  • IF THEN

  • IF THEN ELSE

  • IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE statement has these forms:

  • Simple CASE statement, which evaluates a single expression and compares it to several potential values.

  • Searched CASE statement, which evaluates multiple conditions and chooses the first one that is true.

The CASE statement is appropriate when a different action is to be taken for each alternative.

IF THEN Statement

The IF THEN statement either runs or skips a sequence of one or more statements, depending on a condition.

The IF THEN statement has this structure: 

IF condition THEN
  statements
END IF;

If the condition is true, the statements run; otherwise, the IF statement does nothing.

For complete syntax, see "IF Statement".

Tip:

Avoid clumsy IF statements such as: 

IF new_balance < minimum_balance THEN
  overdrawn := TRUE;
ELSE
  overdrawn := FALSE;
END IF;

Instead, assign the value of the BOOLEAN expression directly to a BOOLEAN variable: 

overdrawn := new_balance < minimum_balance;

A BOOLEAN variable is either TRUE, FALSE, or NULL. Do not write: 

IF overdrawn = TRUE THEN
  RAISE insufficient_funds;
END IF;

Instead, write:

IF overdrawn THEN
  RAISE insufficient_funds;
END IF;

Example 5-1 IF THEN Statement

In this example, the statements between THEN and END IF run if and only if the value of sales is greater than quota+200. 

DECLARE
  PROCEDURE p (
    sales  NUMBER,
    quota  NUMBER,
    emp_id NUMBER
  )
  IS
    bonus    NUMBER := 0;
    updated  VARCHAR2(3) := 'No';
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
 
      UPDATE employees
      SET salary = salary + bonus 
      WHERE employee_id = emp_id;
 
      updated := 'Yes';
    END IF;
 
    DBMS_OUTPUT.PUT_LINE (
      'Table updated?  ' || updated || ', ' || 
      'bonus = ' || bonus || '.'
    );
  END p;
BEGIN
  p(10100, 10000, 120);
  p(10500, 10000, 121);
END;
/

Result:

Table updated?  No, bonus = 0.
Table updated?  Yes, bonus = 125.

IF THEN ELSE Statement

The IF THEN ELSE statement has this structure: 

IF condition THEN
  statements
ELSE
  else_statements
END IF;

If the value of condition is true, the statements run; otherwise, the else_statements run.

IF statements can be nested, as in Example 5-3.

For complete syntax, see "IF Statement".

Example 5-2 IF THEN ELSE Statement

In this example, the statement between THEN and ELSE runs if and only if the value of sales is greater than quota+200; otherwise, the statement between ELSE and END IF runs. 

DECLARE
  PROCEDURE p (
    sales  NUMBER,
    quota  NUMBER,
    emp_id NUMBER
  )
  IS
    bonus  NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
    ELSE
      bonus := 50;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
 
    UPDATE employees
    SET salary = salary + bonus 
    WHERE employee_id = emp_id;
  END p;
BEGIN
  p(10100, 10000, 120);
  p(10500, 10000, 121);
END;
/

Result:

bonus = 50
bonus = 125

Example 5-3 Nested IF THEN ELSE Statements

DECLARE
  PROCEDURE p (
    sales  NUMBER,
    quota  NUMBER,
    emp_id NUMBER
  )
  IS
    bonus  NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
    ELSE
      IF sales > quota THEN
        bonus := 50;
      ELSE
        bonus := 0;
      END IF;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
 
    UPDATE employees
    SET salary = salary + bonus 
    WHERE employee_id = emp_id;
  END p;
BEGIN
  p(10100, 10000, 120);
  p(10500, 10000, 121);
  p(9500, 10000, 122);
END;
/

Result:

bonus = 50
bonus = 125
bonus = 0

IF THEN ELSIF Statement

The IF THEN ELSIF statement has this structure: 

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]...
[ ELSE
    else_statements
]
END IF;

The IF THEN ELSIF statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the else_statements run, if they exist; otherwise, the IF THEN ELSIF statement does nothing.

A single IF THEN ELSIF statement is easier to understand than a logically equivalent nested IF THEN ELSE statement: 

-- IF THEN ELSIF statement

IF condition_1 THEN statements_1;
  ELSIF condition_2 THEN statements_2;
  ELSIF condition_3 THEN statement_3;
END IF;

-- Logically equivalent nested IF THEN ELSE statements

IF condition_1 THEN
  statements_1;
ELSE
  IF condition_2 THEN
    statements_2;
  ELSE
    IF condition_3 THEN
      statements_3;
    END IF;
  END IF;
END IF;

For complete syntax, see "IF Statement".

Example 5-4 IF THEN ELSIF Statement

In this example, when the value of sales is larger than 50000, both the first and second conditions are true. However, because the first condition is true, bonus is assigned the value 1500, and the second condition is never tested. After bonus is assigned the value 1500, control passes to the DBMS_OUTPUT.PUT_LINE invocation. 

DECLARE
  PROCEDURE p (sales NUMBER)
  IS
    bonus  NUMBER := 0;
  BEGIN 
    IF sales > 50000 THEN
      bonus := 1500;
    ELSIF sales > 35000 THEN
      bonus := 500;
    ELSE
      bonus := 100;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE (
      'Sales = ' || sales || ', bonus = ' || bonus || '.'
    );
  END p;
BEGIN
  p(55000);
  p(40000);
  p(30000);
END;
/

Result:

Sales = 55000, bonus = 1500.
Sales = 40000, bonus = 500.
Sales = 30000, bonus = 100.

Example 5-5 IF THEN ELSIF Statement Simulates Simple CASE Statement

This example uses an IF THEN ELSIF statement with many ELSIF clauses to compare a single value to many possible values. For this purpose, a simple CASE statement is clearer—see Example 5-6. 

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  
  IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('Excellent');
  ELSIF grade = 'B' THEN
    DBMS_OUTPUT.PUT_LINE('Very Good');
  ELSIF grade = 'C' THEN
    DBMS_OUTPUT.PUT_LINE('Good');
  ELSIF grade = 'D' THEN
    DBMS_OUTPUT. PUT_LINE('Fair');
  ELSIF grade = 'F' THEN
    DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No such grade');
  END IF;
END;
/

Result:

Very Good

Simple CASE Statement

The simple CASE statement has this structure: 

CASE selector
WHEN { selector_value_1a | dangling_predicate_1a } 
	[, …, { selector_value_1n | dangling_predicate_1n }] THEN statements_1
WHEN { selector_value_2a | dangling_predicate_2a }
       [, …, { selector_value_2n | dangling_predicate_2n }] THEN statements_2
...
WHEN { selector_value_na | dangling_predicate_na }
       [, …, { selector_value_nn | dangling_predicate_nn }] THEN statements_n
[ ELSE
  else_statements ]
END CASE;

The selector is an expression (typically a single variable). Each selector_value can be either a literal or an expression. A dangling_predicate can also be used either instead of or in combination with one or multiple selector_values. (For complete syntax, see "CASE Statement".)

A dangling_predicate is an ordinary expression with its left operand missing, for example, < 2. Using a dangling_predicate allows for more complicated comparisons that would otherwise require a searched CASE statement.

The simple CASE statement runs the first statements for which selector_value equals selector or dangling_predicate is true. Remaining conditions are not evaluated. If no selector_value equals selector and no dangling_predicate is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Example 5-6 uses a simple CASE statement to compare a single value to many possible values. The CASE statement in Example 5-6 is logically equivalent to the IF THEN ELSIF statement in Example 5-5.

Note:

As in a simple CASE expression, if the selector in a simple CASE statement has the value NULL, it cannot be matched by WHEN NULL (see Example 3-51). Instead, use a searched CASE statement with WHEN condition IS NULL (see Example 3-55).

Example 5-6 Simple CASE Statement

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';

  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/

Result:

Very Good

Example 5-7 Simple CASE Statement with Dangling Predicates

DECLARE
  grade NUMBER;
BEGIN
  grade := '85';

  CASE grade
    WHEN < 0, > 100 THEN DBMS_OUTPUT.PUT_LINE('No such grade');
    WHEN > 89 THEN DBMS_OUTPUT.PUT_LINE('A');
    WHEN > 79 THEN DBMS_OUTPUT.PUT_LINE('B');
    WHEN > 69 THEN DBMS_OUTPUT.PUT_LINE('C');
    WHEN > 59 THEN DBMS_OUTPUT.PUT_LINE('D');
    ELSE DBMS_OUTPUT.PUT_LINE('F');
  END CASE;
END;
/

Result:

B

Searched CASE Statement

The searched CASE statement has this structure: 

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;

The searched CASE statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise. (For complete syntax, see "CASE Statement".)

The searched CASE statement in Example 5-8 is logically equivalent to the simple CASE statement in Example 5-6.

In both Example 5-8 and Example 5-6, the ELSE clause can be replaced by an EXCEPTION part. Example 5-9 is logically equivalent to Example 5-8.

Example 5-8 Searched CASE Statement

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/

Result:

Very Good

Example 5-9 EXCEPTION Instead of ELSE Clause in CASE Statement

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such grade');
END;
/

Result:

Very Good