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 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.
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 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.
... sequence of executable statements ...END IF;
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
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.
... TRUE sequence of executable statements ...ELSE
... FALSE/NULL sequence of executable statements ...END IF;
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
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
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.
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:
else_statements] END IF;
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).
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.
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.
statements4END 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:
condition2THEN ... END IF;
The PL/SQL runtime engine evaluates both conditions in order to
determine whether the Boolean expression evaluates to TRUE. Suppose
condition2 is an expression that PL/SQL can
process simply and efficiently, such as:
total_sales > 100000
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
Now consider this next version of the same IF statement:
condition1THEN ... END IF; END IF;