PL/SQL User's Guide and Reference

Conditional Control: IF Statements

Often, it is necessary to take alternative actions depending on circumstances. The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

IF-THEN

The simplest form of IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF (not ENDIF), as follows:

IF condition THEN
   sequence_of_statements;
END IF;

The sequence of statements is executed only if the condition yields TRUE. If the condition yields FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement. An example follows:

IF sales > quota THEN
   compute_bonus(empid);
   UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;

You might want to place brief IF statements on a single line, as in

IF x > y THEN high := x; END IF;

IF-THEN-ELSE

The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements, as follows:

IF condition THEN
   sequence_of_statements1;
ELSE
   sequence_of_statements2;
END IF;

The sequence of statements in the ELSE clause is executed only if the condition yields FALSE or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed. In the following example, the first or second UPDATE statement is executed when the condition is true or false, respectively:

IF trans_type = 'CR' THEN
   UPDATE accounts SET balance = balance + credit WHERE ...
ELSE
   UPDATE accounts SET balance = balance - debit WHERE ...
END IF;

The THEN and ELSE clauses can include IF statements. That is, IF statements can be nested, as the following example shows:

IF trans_type = 'CR' THEN
   UPDATE accounts SET balance = balance + credit WHERE ...
ELSE
   IF new_balance >= minimum_balance THEN
      UPDATE accounts SET balance = balance - debit WHERE ...
   ELSE
      RAISE insufficient_funds;
   END IF;
END IF;

IF-THEN-ELSIF

Sometimes you want to select an action from several mutually exclusive alternatives. The third form of IF statement uses the keyword ELSIF (not ELSEIF) to introduce additional conditions, as follows:

IF condition1 THEN
   sequence_of_statements1;
ELSIF condition2 THEN
   sequence_of_statements2;
ELSE
   sequence_of_statements3;
END IF;

If the first condition yields FALSE or NULL, the ELSIF clause tests another condition. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom. If any condition yields TRUE, its associated sequence of statements is executed and control passes to the next statement. If all conditions yield FALSE or NULL, the sequence in the ELSE clause is executed. Consider the following example:

BEGIN
   ...
   IF sales > 50000 THEN
      bonus := 1500;
   ELSIF sales > 35000 THEN
      bonus := 500;
   ELSE
      bonus := 100;
   END IF;
   INSERT INTO payroll VALUES (emp_id, bonus, ...);
END;

If the value of sales is more than 50000, the first and second conditions are true. Nevertheless, bonus is assigned the proper value of 1500 because the second condition is never tested. When the first condition yields TRUE, its associated statement is executed and control passes to the INSERT statement.

Guidelines

Avoid clumsy IF statements like those in the following example:

DECLARE
   ...
   overdrawn  BOOLEAN;
BEGIN
   ...
   IF new_balance < minimum_balance THEN
      overdrawn := TRUE;
   ELSE
      overdrawn := FALSE;
   END IF;
   ...
   IF overdrawn = TRUE THEN
      RAISE insufficient_funds;
   END IF;
END;

This code disregards two useful facts. First, the value of a Boolean expression can be assigned directly to a Boolean variable. So, you can replace the first IF statement with a simple assignment, as follows:

overdrawn := new_balance < minimum_balance;

Second, a Boolean variable is itself either true or false. So, you can simplify the condition in the second IF statement, as follows:

IF overdrawn THEN ...

When possible, use the ELSIF clause instead of nested IF statements. That way, your code will be easier to read and understand. Compare the following IF statements:

IF condition1 THEN            |     IF condition1 THEN
   statement1;                |        statement1;
ELSE                          |     ELSIF condition2 THEN
   IF condition2 THEN         |        statement2;
      statement2;             |     ELSIF condition3 THEN
   ELSE                       |        statement3;
      IF condition3 THEN      |     END IF;
         statement3;          |
      END IF;                 |
   END IF;                    |
END IF;                       |

These statements are logically equivalent, but the first statement obscures the flow of logic, whereas the second statement reveals it.