Oracle PL/SQL Programming, Third Edition

Chapter 4. Conditional and Sequential Control

This chapter describes two types of PL/SQL control statements: conditional control statements and sequential control statements. Almost every piece of code you write will require conditional control, which is the ability to direct the flow of execution through your program based on a condition. You do this with IF-THEN-ELSE and CASE statements (CASE statements are new in Oracle9i). There are also CASE expressions; while not the same as CASE statements, they can sometimes be used to eliminate the need for an IF or CASE statement altogether. Far less often, you will need to tell PL/SQL to transfer control unconditionally via the GOTO statement, or explicitly to do nothing via the NULL statement.

IF Statements

In your programs, you need to be able to implement requirements such as:

If the salary is between ten and twenty thousand, then apply a bonus of $1500. If the salary is between twenty and forty thousand, apply a bonus of $1000. If the salary is over forty thousand, give the employee a bonus of $500.

or:

If the user preference includes the toolbar, display the toolbar when the window first opens.

The IF statement allows you to design conditional logic into your programs. The IF statement comes in three flavors, as shown in the following table:

IF type	Characteristics
IF THEN END IF;	This is the simplest form of the IF statement. The condition between IF and THEN determines whether the set of statements between THEN and END IF should be executed. If the condition evaluates to FALSE, the code is not executed.
IF THEN ELSE END IF;	This combination implements an either/or logic: based on the condition between the IF and THEN keywords, execute the code either between THEN and ELSE or between ELSE and END IF. One of these two sections of executable statements is performed.
IF THEN ELSIF ELSE END IF;	This last and most complex form of the IF statement selects an action from a series of mutually exclusive conditions and then executes the set of statements associated with that condition. If you’re writing IF statements like this in Oracle9i, you should consider using searched CASE statements instead.

The IF-THEN Combination

The general format of the IF-THEN syntax is as follows:

IF condition
THEN
   ... sequence of executable statements ...
END IF;

The condition is a Boolean variable, constant, or expression that evaluates to TRUE, FALSE, or NULL. If condition evaluates to TRUE, then the executable statements found after the THEN keyword and before the matching END IF statement are executed. If condition evaluates to FALSE or NULL, those statements are not executed.

The following IF condition compares two different numeric values. Remember that if one of these two values is NULL, then the entire expression returns NULL; in the following example, the bonus is not given:

IF salary > 40000
THEN
   give_bonus (employee_id,500);
END IF;

It’s not necessary to put the IF, THEN, and END IF keywords on their own lines. In fact, line breaks don’t matter at all for any type of IF statement. We could just as easily write:

IF salary > 40000 THEN give_bonus (employee_id,500); END IF;

Putting everything on one line is perfectly fine for simple IF statements such as the one shown here. However, when writing IF statements of any complexity at all, you’ll find that readability is much greater when you format the statement such that each keyword begins a new line. For example, the following code would be very difficult to follow if it were all crammed on a single line. Actually, it’s difficult to follow as it appears on three lines:

IF salary > 40000 THEN INSERT INTO employee_bonus (eb_employee_id, eb_bonus_amt) 
VALUES (employee_id, 500); UPDATE emp_employee SET emp_bonus_given=1 WHERE emp_
employee_id=employee_id; END IF;

Ugh! Who’d want to spend time figuring that out? It’s much more readable when formatted nicely:

IF salary > 40000 
THEN 
   INSERT INTO employee_bonus 
      (eb_employee_id, eb_bonus_amt) 
      VALUES (employee_id, 500);

   UPDATE emp_employee
   SET emp_bonus_given=1
   WHERE emp_employee_id=employee_id; 
END IF;

This readability issue becomes even more important when using the ELSE and ELSIF keywords, and when nesting one IF statement inside the other. Take full advantage of indents and formatting to make the logic of your IF statements easily decipherable. Future maintenance programmers will thank you.

The IF-THEN-ELSE Combination

Use the IF-THEN-ELSE format when you want to choose between two mutually exclusive actions. The format of this either/or version of the IF statement is as follows:

IF condition
THEN
   ... TRUE sequence of executable statements ...
ELSE
   ... FALSE/NULL sequence of executable statements ...
END IF;

The condition is a Boolean variable, constant, or expression. If condition evaluates to TRUE, then the executable statements found after the THEN keyword and before the ELSE keyword are executed (the “TRUE sequence of executable statements”). If condition evaluates to FALSE or NULL, then the executable statements that come after the ELSE keyword and before the matching END IF keywords are executed (the “FALSE/NULL sequence of executable statements”).

Tip

Notice that the ELSE clause does not have a THEN associated with it.

The important thing to remember is that one of the two sequences of statements will always execute, because IF-THEN-ELSE is an either/or construct. Once the appropriate set of statements has been executed, control passes to the statement immediately following the END IF keyword.

Following is an example of the IF-THEN-ELSE construct that builds upon the IF-THEN example shown in the previous section:

IF salary <= 40000
THEN
   give_bonus (employee_id, 0);
ELSE 
   give_bonus (employee_id, 500);
END IF;

In this example, employees with a salary greater than 40,000 will get a bonus of 500 while all other employees will get no bonus at all. Or will they? What happens if salary, for whatever reason, happens to be NULL for a given employee? In that case, the statements following the ELSE will be executed, and the employee in question will get the bonus that is supposed to go only to highly paid employees. That’s not good! If we’re not sure that salary will never be NULL, we can protect ourselves against this problem using the NVL function:

IF NVL(salary,0) <= 40000
THEN
   give_bonus (employee_id, 0);
ELSE 
   give_bonus (employee_id, 500);
END IF;

The NVL function will return zero any time salary is NULL, ensuring that any employees with a NULL salary also get a zero bonus.

Using Boolean Flags

Often, it’s convenient to use Boolean variables as flags so that we don’t need to evaluate the same Boolean expression more than once. When doing so, remember that the result of a Boolean expression can be assigned directly to a Boolean variable. For example, rather than write:

IF :customer.order_total > max_allowable_order
THEN
   order_exceeds_balance := TRUE;
ELSE
   order_exceeds_balance := FALSE;
END IF;

we can instead write the following, much simpler, expression:

order_exceeds_balance := :customer.order_total > max_allowable_order;

Now, whenever we need to test in our code whether an order’s total exceeded the maximum, we can write the following, easily understandable, IF statement:

IF order_exceeds_balance
THEN
...

If you have not had much experience with Boolean variables, it may take you a little while to learn how to integrate them smoothly into your code. It is worth the effort, though. The result is cleaner, more readable code.

The IF-THEN-ELSIF Combination

This last form of the IF statement comes in handy when you have to implement logic that has many alternatives; it is not an either/or situation. The IF-ELSIF formulation provides a way to handle multiple conditions within a single IF statement. In general, you should use ELSIF with mutually exclusive alternatives (i.e., only one condition can be TRUE for any execution of the IF statement). The general format for this variation of IF is:

IF condition-1
THEN
   statements-1
ELSIF condition-N
THEN
   statements-N
[ELSE
   else_statements]
END IF;

Warning

Be very careful to use ELSIF, not ELSEIF. The inadvertent use of ELSEIF is a fairly common syntax error. ELSE IF (two words) doesn’t work either.

Logically speaking, the IF-THEN-ELSIF construct is one way of implementing CASE statement functionality in PL/SQL. Of course, if you are using Oracle9i or higher, you are probably better off actually using a CASE statement (discussed later in this chapter).

NULLs in IF Statements

Be very cognizant of the effect of NULL values in IF expressions. One NULL value in an expression will usually cause the entire expression to be NULL, and a NULL is neither TRUE nor FALSE. For example, while seemingly equivalent, the following two IF statements do not have the same effect:

IF x = 2 THEN
   DBMS_OUTPUT.PUT_LINE('x contains 2');
ELSE 
   DBMS_OUTPUT.PUT_LINE('x doesn''t contain 2');
END IF;

IF x <> 2 THEN
   DBMS_OUTPUT.PUT_LINE('x doesn''t contain 2');
ELSE 
   DBMS_OUTPUT.PUT_LINE('x contains 2');
END IF;

The difference between these two statements lies in what happens when the variable x is NULL. When x is NULL, the first IF-THEN-ELSE statement will display “x doesn’t contain 2”; the second statement will display “x contains 2”. Why? Because when x is NULL, the IF expression is not considered to be TRUE, and control passes to the ELSE clause.

Protect yourself from inadvertent NULL-related bugs by using functions such as NVL or by explicitly checking for the NULL case using the IS NULL predicate.

Each ELSIF clause must have a THEN after its condition. Only the ELSE keyword does not need the THEN keyword. The ELSE clause in the IF-ELSIF is the “otherwise” of the statement. If none of the conditions evaluate to TRUE, then the statements in the ELSE clause are executed. But the ELSE clause is optional. You can code an IF-ELSIF that has only IF and ELSIF clauses. In this case, if none of the conditions are TRUE, then no statements inside the IF block are executed.

Following is an implementation of the complete bonus logic described at the beginning of this chapter using the IF-THEN-ELSEIF combination:

IF salary BETWEEN 10000 AND 20000
THEN
   give_bonus(employee_id, 1500);
ELSIF salary BETWEEN 20000 AND 40000
THEN
   give_bonus(employee_id, 1000);
ELSIF salary > 40000
THEN
   give_bonus(employee_id, 500);
ELSE
   give_bonus(employee_id, 0);
END IF;

The conditions in the IF-ELSIF are always evaluated in the order of first condition to last condition. If two conditions evaluate to true, the statements for the first such condition are executed. With respect to the current example, a salary of 20,000 will result in a bonus of 1500 even though that 20,000 salary also satisfies the condition for a 1000 bonus (BETWEEN is inclusive). Once a condition evaluates to TRUE, the remaining conditions are not evaluated at all.

Tip

The CASE statement available beginning in Oracle9i represents a better solution to the bonus problem than the IF-THEN-ELSIF solution shown in this section. See the upcoming section Section 4.2.

Even though overlapping conditions are allowed in an IF-THEN-ELSIF statement, it’s best to avoid them when possible. In our case, the original spec is a bit ambiguous about how to handle boundary cases such as 20,000. Assuming that the intent is to give the highest bonuses to the lowest-paid employees (which seems like a reasonable approach to us), we would dispense with the BETWEEN operator and use the following less-than/greater-than logic. Note that we’ve also dispensed with the ELSE clause just to illustrate that it is optional:

IF salary >= 10000 AND salary <= 20000
THEN
   give_bonus(employee_id, 1500);
ELSIF salary > 20000 AND salary <= 40000
THEN
   give_bonus(employee_id, 1000);
ELSIF salary > 40000
THEN
   give_bonus(employee_id, 400);
END IF;

By taking steps to avoid overlapping conditions in an IF-THEN-ELSIF, we are eliminating a possible (probable?) source of confusion for programmers who come after us. We also eliminate the possibility of inadvertent bugs being introduced as a result of someone’s reordering the ELSIF clauses.

Warning

The language does not require that ELSIF conditions be mutually exclusive. Always be aware of the possibility that two or more conditions might apply to a given value, and that consequently the order of those ELSIF conditions might be important.

Nested IF Statements

You can nest any IF statement within any other IF statement. The following IF statement shows several layers of nesting:

IF condition1
THEN
   IF condition2
   THEN
      statements2
   ELSE
      IF condition3
      THEN
         statements3
      ELSIF condition4
      THEN
         statements4
      END IF;
   END IF;
END IF;

Nested IF statements are often necessary to implement complex logic rules, but you should use them carefully. Nested IF statements, like nested loops, can be very difficult to understand and debug. If you find that you need to nest more than three levels deep in your conditional logic, you should review that logic and see if there is a simpler way to code the same requirement. If not, then consider creating one or more local modules to hide the innermost IF statements.

A key advantage to the nested IF structure is that it defers evaluation of inner conditions. The conditions of an inner IF statement are evaluated only if the condition for the outer IF statement that encloses them evaluates to TRUE. Therefore, the obvious reason to nest IF statements is to evaluate one condition only when another condition is true. For example, in our code to award bonuses, we might write the following:

IF award_bonus(employee_id) THEN
   IF print_check (employee_id) THEN
      DBMS_OUTPUT.PUT_LINE('Check issued for ' || employee_id);
   END IF;
END IF;

This is reasonable, because we want to print a message for each bonus check issued, but we don’t want to print a bonus check for a zero amount in cases where no bonus was given.

Another situation in which you’d want to use nested IF statements is when the evaluation of a condition is very expensive in terms of CPU or memory utilization. In such a case, you may want to defer that processing to an inner IF statement so that it is executed only when absolutely necessary. This is especially true of code that will be performed frequently or in areas of the application where quick response time is critical. The following IF statement illustrates this concept:

IF condition1 AND condition2
THEN
   ...
END IF;

The PL/SQL runtime engine evaluates both conditions in order to determine whether the Boolean expression evaluates to TRUE. Suppose that condition2 is an expression that PL/SQL can process simply and efficiently, such as:

total_sales > 100000

but that condition1 is a much more complex and CPU-intensive expression, perhaps calling a stored function that executes a query against the database. If condition2 is evaluated in a tenth of a second to FALSE, and condition1 is evaluated in three seconds to TRUE, then it has taken more than three seconds to determine that the code inside the IF statement should not be executed.

Now consider this next version of the same IF statement:

IF condition2
THEN
   IF condition1
   THEN
      ...
   END IF;
END IF;

Now condition1 will be evaluated only if condition2 evaluates to TRUE. In those situations where total_sales <= 100000, the user will never have to wait the extra three seconds to continue.

Avoiding Syntax Gotchas

Keep in mind these points about IF statement syntax:

Always match up an IF with an END IF

In all three variations of the IF statement, you must close off the executable statements associated with the conditional structure with an END IF keyword.

You must have a space between the keywords END and IF

If you type ENDIF instead of END IF, the compiler will get very confused and give you the following hard-to-understand error messages:

  ORA-06550: line 14, column 4:
  PLS-00103: Encountered the symbol ";" when expecting one of the following:

The ELSIF keyword should not have an embedded “E”

If you type ELSEIF in place of ELSIF, the compiler will get very confused and not recognize the ELSEIF as part of the IF statement. It will interpret it as a variable or procedure name.

Place a semicolon (;) only after the END IF keywords

The keywords THEN, ELSE, and ELSIF should not have a semicolon after them. They are not standalone executable statements and, unlike END IF, do not complete a statement. If you include a semicolon after these keywords, the compiler will issue messages indicating that it is looking for a statement of some kind before the semicolon.