 | Chapter 5 |  |
| Chapter 5 | |
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 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 in your programs. The IF statement comes in three flavors:
IF THEN END IF; | This is the simplest form of the IF statement. The condition between the IF and THEN determines whether the set of statements between the THEN and END IF should be executed. If the condition evaluates to false, then the code is not executed. |
IF ELSE END IF; | This combination implements an either/or logic: based on the condition between the IF and THEN keywords, either execute the code between the THEN and ELSE or between the ELSE and END IF. One of these two sections of executable statements is performed. |
IF 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. |
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 the <condition> evaluates to FALSE or NULL, then those statements are not executed.
Here are some examples of the simple IF-THEN structure:
The following IF condition compares two different numeric values. Remember that if one of these two variables is NULL, then the entire Boolean expression returns NULL and the discount is not applied:
IF :company.total_sales > system_average_sales THEN apply_discount (:company.company_id); END IF;
Here is an example of an IF statement with a single Boolean variable (or function -- you really can't tell the difference just by looking at this line of code):
IF report_requested THEN print_report (report_id); END IF;
In the previous example, I used a single Boolean variable in my condition. If the variable report_requested evaluates to TRUE, then the report prints. Otherwise, the print step is skipped. I could code that same IF statement as follows:
IF report_requested = TRUE THEN print_report (report_id); END IF;
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.
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 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:
In this example, if there is a VIP caller, I generate an express response; otherwise, I use normal delivery:
IF caller_type = 'VIP'
THEN
generate_response ('EXPRESS');
ELSE
generate_response ('NORMAL');
END IF;You can put an entire IF-THEN-ELSE on a single line if you wish, as shown below:
IF new_caller THEN get_next_id; ELSE use_current_id; END IF;
This example sets the order_exceeds_balance Boolean variable based on the order total:
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:
| If <condition1> is true then execute <statements1>. |
| Otherwise ... if <condition n> is true then execute <statements n>. |
| Otherwise execute the <else_statements>. |
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:
I have three different caller types to check. If the caller type is not one of VIP, BILL_COLLECTOR, or INTERNATIONAL, then send the response with normal delivery:
IF caller_type = 'VIP'
THEN
generate_response ('EXPRESS');
ELSIF caller_type = 'BILL_COLLECTOR'
THEN
generate_response ('THROUGH_CHICAGO');
ELSIF caller_type = 'INTERNATIONAL'
THEN
generate_response ('AIR');
ELSE
generate_response ('NORMAL');
END IF;Here is an IF-ELSIF without an ELSE clause. If none of the conditions are TRUE, then this block of code does not run any of its executable statements:
IF new_caller AND caller_id IS NULL THEN confirm_caller; ELSIF new_company AND company_id IS NULL THEN confirm_company; ELSIF new_call_topic AND call_id IS NULL THEN confirm_call_topic; END IF;
Here's an IF-ELSIF with just a single ELSIF -- and no ELSE. In this case, however, you could just as well have used an IF-THEN-ELSE structure because the two conditions are mutually exclusive. The ELSIF statements execute only if the IF condition is FALSE. The advantage to including the ELSIF is that it documents more clearly the condition under which its executable statements will be run.
IF start_date > SYSDATE AND order_total >= min_order_total THEN fill_order (order_id); ELSIF start_date <= SYSDATE OR order_total < min_order_total THEN queue_order_for_addtl_parts (order_id); END IF;
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:
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.
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:
queue_order_for_addtl_parts (order_id);
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 order was placed today or earlier and the total for the order is under the minimum.
The reason that queue_order_for_addtl_parts will never be executed lies in the second condition:
An order is filled with LOW PRIORITY whenever the order date was no later than the system date.
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.
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.
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.
|  | |
| 4.7 Tips for Creating and Using Variables |  | 5.2 Sequential Control Statements |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.