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: the ability to direct the flow of execution through your program based on a condition; you do this with IF-THEN-ELSE statements. Far less often, you will need to tell PL/SQL to transfer control unconditionally via the GOTO statement, or to do nothing via the NULL statement.
You need to be able to implement requirements such as:
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 the <condition> evaluates to FALSE or NULL, then those statements are not executed.
Here are some examples of the simple IF-THEN structure:
While the code in the third example is logically equivalent to the IF report_requested formulation, it is superfluous and works against the nature of a Boolean variable. A Boolean variable itself evaluates to TRUE, FALSE, or NULL; you don't have to test the variable against those values. If you name your Boolean variables properly, you will be able to easily read the logic and intent of your IF-THEN logic by leaving out the unnecessary parts of the statement.
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 the <condition> evaluates to FALSE or NULL, then the executable statements that come after the ELSE keywords and before the matching END IF keywords are executed (the "FALSE/NULL sequence of executable statements").
The important thing to remember is that one of these sequences of statements will always execute, because it is an either/or construct. Once the appropriate set of statements has been executed, control passes to the statement immediately following the END IF statement.
Notice that the ELSE clause does not have a THEN associated with it.
Here are some examples of the IF-THEN-ELSE construct:
IF :customer.order_total > max_allowable_order THEN order_exceeds_balance := TRUE; ELSE order_exceeds_balance := FALSE; END IF;
In the last example, the IF statement is not only unnecessary, but confusing. Remember: you can assign a TRUE/FALSE value directly to a Boolean variable. You do not need the IF-THEN-ELSE construct to decide how to set order_exceeds_balance. Instead, you can assign the value directly, as follows:
order_exceeds_balance := :customer.order_total > max_allowable_order;
This assignment sets the order_exceeds_balance variable to TRUE if the customer's order total is greater than the maximum allowed, and sets it to FALSE otherwise. In other words, it achieves exactly the same result as the IF-THEN-ELSE and does it more clearly and with less code.
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.
This last form of the IF statement comes in handy when you have to implement logic which has many alternatives; it is not an either/or situation. The IF-ELSIF formulation provides the most straightforward and natural way to handle multiple, mutually exclusive alternatives. The general format for this variation of the IF statement is:
IF <condition-1> THEN <statements-1> ... ELSIF <condition-N> THEN <statements-N> [ELSE <else_statements>] END IF;
Logically speaking, the IF-ELSIF implements the CASE statement in PL/SQL. The sequence of evaluation and execution for this statement is:
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 also 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.
The conditions in the IF-ELSIF are always evaluated in the order of first condition to last condition. Once a condition evaluates to TRUE, the remaining conditions are not evaluated at all.
Here are some examples of the possible variations in the format of the IF-ELSIF structure:
Make sure that your IF-ELSIF conditions are mutually exclusive. The conditions in the IF-ELSIF are always evaluated in the order of first to last. Once a condition evaluates to TRUE, the remaining conditions are not evaluated at all. If you have an overlap in the conditions so that more than one condition could be TRUE, you probably have an error in your logic. Either the conditions in the IF statement should be changed to make them exclusive, or you'll sometimes run the risk of not executing the right set of code in your IF statement. The following example illustrates these points. Translate the following rules:
into this code:
IF salary BETWEEN 10000 AND 20000 THEN bonus := 1500; ELSIF salary BETWEEN 20000 AND 40000 THEN bonus := 1000; ELSIF salary > 40000 THEN bonus := 500; END IF;
What if the salary is $20,000? Should the person receive a bonus of $1000 or $1500? The way the IF-ELSIF is currently written, a person making $20,000 will always receive a bonus of $1500. This might not be the intent of the specification, the overall approach of which seems to be "let the poorer catch up a little with the richer." Actually, the problem here is that the original phrasing of the specification is ambiguous -- and even incomplete. It assumes that no one ever has a salary of less than $10,000 (or, if we did not want to give the author of this specification the benefit of the doubt, we would say, "Anyone with a salary under $10,000 gets no bonus").
We can close up the holes in this logic by moving away from the BETWEEN operator and instead relying on < and > . This clarifies those break-points in the salary:
IF salary < 10000 THEN bonus := 2000; ELSIF salary < 20000 THEN bonus := 1500; ELSIF salary < 40000 THEN bonus := 1000; ELSE -- same as ELSIF salary >= 40000 bonus := 500; END IF;
Now the conditions are mutually exclusive, and the people who make the lowest salary get the largest bonus. That seems fair to me.
Here is an example of an IF-ELSIF with a condition that will never evaluate to TRUE; the code associated with it, therefore, will never be executed. See if you can figure out which condition that is:
IF order_date > SYSDATE AND order_total >= min_order_total THEN fill_order (order_id, 'HIGH PRIORITY'); ELSIF order_date < SYSDATE OR order_date = SYSDATE THEN fill_order (order_id, 'LOW PRIORITY'); ELSIF order_date <= SYSDATE AND order_total < min_order_total THEN queue_order_for_addtl_parts (order_id); ELSIF order_total = 0 THEN MESSAGE (' No items have been placed in this order!'); END IF;
The only executable statement that we can say with complete confidence will never be executed is:
An order is put in the queue to wait for additional parts (to boost up the order total) only if the third condition evalutes to TRUE:
The reason that queue_order_for_addtl_parts will never be executed lies in the second condition:
The second condition is a logical subset of the third condition. Whenever the second condition is FALSE, the third condition will also be FALSE. Whenever the third condition evaluates to TRUE, the second condition will also evaluate to TRUE. Because it comes before the third condition in the evaluation sequence, the second condition will catch any scenarios that would otherwise satisfy the third condition.
When you write an IF-ELSIF, especially one with more than three alternatives, review your logic closely and make sure there is no overlap in the conditions. For any particular set of values or circumstances, at most one -- and perhaps none -- of the conditions should evalute to TRUE.
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.
If the evaluation of a condition is very expensive (in CPU or memory terms), 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 run-time engine evalutes both conditions in order to determine if the Boolean expression A AND B evaluates to TRUE. Suppose that condition2 is an expression which 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 which executes a query against the database. If condition2 is evaluated in a tenth of a second to TRUE and condition1 is evaluated in three seconds to FALSE, then it would take 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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.