10.2 How do I…Achieve conditional and looping control?Problem
I need to use conditional statements like IF…THEN…ELSE, to control program behavior. I also need to perform looping operations within my PL/SQL blocks. How do I achieve conditional and looping control in PL/SQL?
Technique
Conditional Control
Three types of IF statements exist in PL/SQL. The IF statement shown next is the simplest and enables you to incorporate conditional logic in your code:
IF <boolean expression> THEN
Sequence of statements
END IF;
The Boolean expression is an expression that evaluates to a Boolean value: TRUE, FALSE, or NULL. The statement evaluates the Boolean expression and executes the sequence of statements if the expression evaluates to TRUE. The second type is the IF…THEN…ELSE statement, shown next, which provides an alternate sequence of statements to execute if the Boolean expression evaluates to FALSE or NULL:
IF <boolean expression> THEN
Sequence of statements
ELSE
Alternate sequence of statements
END IF;
If the Boolean expression evaluates to TRUE, the first set of statements is executed; otherwise, the alternate statements are executed. The third type has an ELSIF construct that evaluates additional Boolean expressions when the first expression evaluates to FALSE or NULL. The syntax shown here presents the use of ELSIF:
IF <boolean expression> THEN
If the first Boolean expression evaluates to FALSE or NULL, the Boolean expression in the ELSIF clause is evaluated. If the alternate Boolean expression evaluates to TRUE, the alternate statements are executed. One or more ELSIF clauses can be included with the IF statement to simulate a case structure found in other programming languages. The final ELSE clause is optional.First sequence of statements
ELSIF <alternate boolean expression> THEN
Second sequence of statements
ELSE
Third sequence of statements
END IF;
Technique
Iterative Control
Four types of looping constructs exist in PL/SQL: simple loops, FOR loops, WHILE loops, and cursor FOR loops. Simple loops execute a sequence of statements repeatedly with no apparent end. The syntax of a simple loop is shown here:
LOOP
Sequence of statements
END LOOP;
The loop will continue to execute until it encounters an EXIT statement. Some forms of the EXIT statement include a WHEN clause, which includes a Boolean expression to be evaluated. A FOR loop has a defined number of iterations performed and includes a loop counter that can be used within the loop. The syntax of a FOR loop is:
FOR loop counter IN [REVERSE] lower_bound..upper_bound LOOP
Sequence of statements
END LOOP;
The loop is executed once for each value starting with the lower bound moving upwards to the upper bound. If the optional REVERSE keyword is used, the loop starts with the upper bound moving downwards to the lower bound. The WHILE loop evaluates a Boolean expression before each iteration. If the ex-pression evaluates to TRUE, the statements in the loop are executed. If the expression evaluates to FALSE or NULL, execution is passed to the statement following the loop. The syntax of the WHILE loop is shown here:
WHILE <boolean expression> LOOP
Sequence of statements
END LOOP;
If the Boolean expression evaluates to FALSE or NULL the first time it’s evaluated, the statements will never be executed. The cursor FOR loop is explored in How-To 11.2.
Anonymous PL/SQL blocks and loops can be labeled using the <<label_name>> syntax to enable explicit references to variables as label_name.variable_name to avoid ambiguity when referencing a variable with the same name in a nested block or a nested loop:
<<label_name>> -- could be used before a FOR or WHILE loop as well
LOOP
Sequence of statements
END LOOP LOOP_NAME;
Labels provide greater readability when using nested loops.
Technique
Sequential Control
With conditional and looping constructs, almost any logic can be coded, but PL/SQL also provides a GOTO statement in the rare event that you need to use it. The GOTO statement unconditionally transfers control to the labeled statement. The label must precede a start of a PL/SQL block or loop, or an executable statement. Otherwise, use a NULL statement after the label to avoid compilation errors. The GOTO statement and label can appear in any order within a block:
BEGIN
1. Run SQL*Plus and connect as the WAITE user account and run the PL/SQL block contained in the CHP10_4.SQL file that demonstrates the use of a simple IF statement with an ELSE clause. Figure 10.4 shows the code and output in SQL*Plus....
<<DO_QUERY>>
BEGIN
SELECT * FROM ...
...
END;
...
GOTO DO_QUERY;
... END;
Steps
Lines 1 and 2 contain the declarative section of the block. The variable declared in line 2 is evaluated within the IF statement in the executable section. Lines 3 through 10 contain the executable section of the block. Line 4 assigns a value of 3 to the variable X. The IF statement in line 5 evaluates the variable X using a Boolean expression. If the value of X is greater than 2, the statement in line 6 is executed; otherwise, the statement in line 8 is executed. The DBMS_OUTPUT stored package is used to display PL/SQL output in lines 6 and 8 in SQL*Plus. Because X is assigned a value of 3 in line 4, the Boolean expression in line 5 evaluates to TRUE, displaying the first message.
2. Run the CHP10_5.SQL file as shown in Figure 10.5. The PL/SQL block contained in the file uses the IF…THEN…ELSIF…ELSE construct to perform conditional logic.
Lines 5 through 11 present the IF…ELSIF…ELSE construct. If the Boolean expression contained in line 5 evaluates to TRUE, line 6 is executed. Otherwise, the Boolean expression in line 7 is evaluated. If the expression evaluates to TRUE, line 8 is executed; otherwise, line 10 is executed.
Line 4 assigns the value 4 to the variable X. The expression in line 5 evaluates to FALSE, causing the expression on line 7 to be tested. Because X is greater than 3, the expression evaluates to TRUE, and the message in line 8 is displayed.
3. Run the CHP10_6.SQL file in SQL*Plus. The PL/SQL block contained in the file executes a simple loop, using the EXIT WHEN statement to break out of the loop. The code and output are shown in Figure 10.6.
Lines 5 through 9 present a simple loop that continues to perform iterations until the Boolean expression contained in line 8 evaluates to TRUE. The PUT_LINE function contained in the loop displays a value within SQL*Plus for each iteration of the loop.
The loop executes three times before the expression in the EXIT WHEN statement evaluates to TRUE. When the expression is TRUE, the loop is exited.
4. Run the CHP10_7.SQL file in SQL*Plus. The PL/SQL block in the file contains a FOR loop, which counts from 1 to 3 and displays the loop index. The results of the operation are shown in Figure 10.7.
Lines 1 through 5 contain the execution section of the block. There is no declarative section because the loop counter variable is declared implicitly as an integer when a FOR loop is used. The loop counter variable cannot be referenced outside the FOR loop. The loop in lines 2 through 4 performs an iteration for each value between 1 and 3. Line 3 displays the value of the loop counter by calling the DBMS_OUTPUT.PUT_LINE procedure.
The loop is performed three times with the loop counter representing the number of the iteration performed.
5. Load CHP10_8.SQL into the SQL buffer. The PL/SQL block in the file uses a WHILE loop to perform iterations. The results of the operation are shown in Figure 10.8.
The variable declared in line 2 is used in the Boolean expression in the WHILE loop. The WHILE loop in lines 5 through 8 performs iterations as long as the value of K is less than 3. Line 6 displays a message using the DBMS_OUTPUT.PUT_LINE procedure, and line 7 increments the value of K. If line 7 does not exist, the loop is performed indefinitely.
The loop is executed two times because the value of K is less than 3 only for the first two iterations.
How It Works
The IF statement is used to perform conditional operations within PL/SQL by evaluating a Boolean expression and performing statements based on the result. Step 1 presents the basic IF…THEN…ELSE construct within a PL/SQL block. Step 2 uses the ELSIF clause to provide a case functionality.
PL/SQL contains four types of looping constructs, three of which are presented in this How-To. The simple loop presented in Step 3 performs a loop indefinitely until it encounters an EXIT statement. The FOR loop, presented in Step 4, performs a defined number of iterations, based on the value of a loop counter between a lower and upper bound. Bound values can be static or determined dynamically at runtime to provide a dynamic range, but they must evaluate to integers. The WHILE loop, presented in Step 5, evaluates a Boolean expression before each iteration of the loop. As long as the Boolean expression evaluates to TRUE, the loop is performed. If the expression never evaluates to TRUE, the statements contained in the loop are never performed. Loops can also be nested within other loops. An EXIT statement cannot be placed outside the loop, and multiple EXIT statements can be used in any loop.
Comments
Conditional statements give PL/SQL added flexibility not provided by SQL. As you develop applications using PL/SQL, you will find yourself using conditional statements often. Make use of the ELSIF clause instead of a nested IF statement, whenever possible. Looping operations are a fundamental part of procedural languages. You can choose a looping construct that best suits the type of operation in hand. GOTO statements should be used sparingly.