Oracle PL/SQL by Example, Fifth Edition (2015)
Chapter 6. Iterative Control: Part I
In this chapter, you will learn about
Simple Loops
WHILE Loops
Numeric FOR Loops
Generally, computer programs are written because certain tasks must be executed a number of times. For example, many companies need to process transactions on a monthly basis. A program allows the completion of this task by being executed at the end of each month.
Similarly, programs incorporate instructions that need to be executed repeatedly. For example, a program may need to write a number of records to a table. Through the use of a loop, the program can write the desired number of records to a table. In other words, loops are programming facilities that allow a set of instructions to be executed repeatedly.
In PL/SQL, there are four types of loops: simple loops, WHILE loops, numeric FOR loops, and cursor FOR loops. In this chapter, you will explore simple loops, WHILE loops, and numeric FOR loops. In Chapter 7, you will see how these types of loops can be nested within one another. In addition, you will learn about the CONTINUE and CONTINUE WHEN statements, which were introduced in Oracle 11g. Cursor FOR loops are discussed in Chapters 11 and 12.
Lab 6.1: Simple Loops
After this lab, you will be able to
Use Simple Loops with EXIT Conditions
Use Simple Loops with EXIT WHEN Conditions
A simple loop, as you can see from its name, is the most basic kind of loop and has the structure shown in Listing 6.1.
Listing 6.1 Simple Loop Structure
LOOP
STATEMENT 1;
STATEMENT 2;
...
STATEMENT N;
END LOOP;
The reserved word LOOP marks the beginning of the simple loop. Statements 1 through N are a sequence of statements that is executed repeatedly. These statements consist of one or more of the standard programming structures. END LOOP is a reserved phrase that indicates the end of the loop construct. The flow of logic from this structure is illustrated in Figure 6.1.
Figure 6.1 Simple Loop
Every time the simple loop is iterated, a sequence of statements is executed, and then control passes back to the top of the loop. The sequence of statements will execute an infinite number of times, because there is no statement specifying when the loop must terminate. Hence, a simple loop is called an infinite loop because there is no means to exit the loop. A properly constructed loop needs to have an exit condition that determines when the loop is complete. This exit condition has two forms: EXIT and EXIT WHEN.
EXIT Statement
The EXIT statement causes a loop to terminate when the exit condition evaluates to TRUE. The exit condition is evaluated with the help of an IF statement. When the exit condition evaluates to TRUE, control passes to the first executable statement after the END LOOP statement. The structure of this type of loop is shown in Listing 6.2.
Listing 6.2 Simple Loop Structure with an EXIT Statement
LOOP
STATEMENT 1;
STATEMENT 2;
IF EXIT CONDITION THEN
EXIT;
END IF;
END LOOP;
STATEMENT 3;
In this code listing, you can see that after the EXIT CONDITION evaluates to TRUE, control passes to STATEMENT 3, which is the first executable statement after the END LOOP statement. This flow of logic is illustrated in Figure 6.2.
Figure 6.2 Simple Loop with the Exit Condition
As shown in Figure 6.2, during each iteration, the loop executes a sequence of statements. Control then passes to the exit condition of the loop. If the exit condition evaluates to FALSE, control passes to the top of the loop. The sequence of statements will be executed repeatedly until the exit condition evaluates to TRUE. At that point, the loop is terminated via the EXIT statement, and control passes to the next executable statement following the loop.
Figure 6.2 also shows that the exit condition is included in the body of the loop. Therefore, the decision about loop termination is made inside the body of the loop, and the body of the loop, or a part of it, will always be executed at least once. However, the number of iterations of the loop depends on the evaluation of the exit condition and is not known until the loop completes.
This behavior is further illustrated by the following example:
For Example ch06_1a.sql
DECLARE
v_counter BINARY_INTEGER := 0;
BEGIN
LOOP
-- increment loop counter by one
v_counter := v_counter + 1;
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
-- if exit condition yields TRUE exit the loop
IF v_counter = 5
THEN
EXIT;
END IF;
END LOOP;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Done...');
END;
In this example, the variable v_counter keeps count of the loop iterations and is often referred to as a loop counter. The statement
v_counter := v_counter + 1;
is used frequently when working with loops, as it increments the value of v_counter by 1. Once the value of the v_counter reaches 5, the exit condition
v_counter = 5
evaluates to TRUE and the loop terminates. As mentioned previously, as soon as the loop terminates, the control passes to the first executable statement after the END LOOP statement.
When executed, this example produces the following output:
v_counter = 1
v_counter = 2
v_counter = 3
v_counter = 4
v_counter = 5
Done...
Watch Out!
It is very important to initialize the variable v_counter for successful termination of the loop. If v_counter is not initialized, its value remains NULL and the statement
v_counter := v_counter + 1;
never increments the value of v_counter by 1 because NULL + 1 evaluates to NULL. As a result, the exit condition never evaluates to TRUE, and the loop becomes an infinite loop.
As mentioned previously, when working with the loops, the placement of the exit condition affects whether statements inside the body of the loop are executed during the last iteration of the loop. Consider a modified version of the previous example with the exit condition placed immediately after the value of v_counter is incremented by 1. Affected statements are shown in bold.
For Example ch06_1b.sql
DECLARE
v_counter BINARY_INTEGER := 0;
BEGIN
LOOP
-- increment loop counter by one
v_counter := v_counter + 1;
-- if exit condition yields TRUE exit the loop
IF v_counter = 5
THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
END LOOP;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Done...');
END;
This version of the example produces slightly different output:
v_counter = 1
v_counter = 2
v_counter = 3
v_counter = 4
Done...
In this version of the script, the portion of the loop before the exit condition executed 5 times. In other words, the variable v_counter was incremented by 1 five times. However, on the fifth iteration of the loop, the exit condition evaluated to TRUE, so the
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
statement was not executed. Instead, control passed to the first executable statement after the END LOOP. In essence, this placement of the exit condition caused partial execution of the loop body on the last iteration of the loop.
Did You Know?
The EXIT statement is valid only when placed inside of a loop. When placed outside of a loop, it will cause a syntax error. To avoid this error, use the RETURN statement to terminate a PL/SQL block before its normal end is reached as follows:
BEGIN
DBMS_OUTPUT.PUT_LINE ('Line 1');
RETURN;
DBMS_OUTPUT.PUT_LINE ('Line 2');
END;
This example produces the following output:
Line 1
Because the RETURN statement terminates the PL/SQL block, the second DBMS_OUTPUT.PUT_LINE statement is never executed.
If used without an exit condition, the EXIT statement will cause the simple loop to execute only once. Consider the following example:
DECLARE
v_counter NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
EXIT;
END LOOP;
END;
This example produces the following output:
v_counter = 0
Because the EXIT statement is used without an exit condition, the loop terminates as soon as the EXIT statement executes.
EXIT WHEN Statement
The EXIT WHEN statement causes a loop to terminate only if the exit when condition evaluates to TRUE. Control then passes to the first executable statement after the END LOOP statement. The structure of a loop using an EXIT WHEN statement is shown in Listing 6.3.
Listing 6.3 Simple Loop Structure with an EXIT WHEN Statement
LOOP
STATEMENT 1;
STATEMENT 2;
EXIT WHEN EXIT CONDITION;
END LOOP;
STATEMENT 3;
Figure 6.2 also illustrates the logic of the EXIT WHEN statement, as the flow of logic for the structure of EXIT and EXIT WHEN statements is the same even though two different forms of exit condition are used. In other words,
IF EXIT CONDITION THEN
EXIT;
END IF;
is equivalent to
EXIT WHEN EXIT CONDITION;
This is further illustrated by the following modified version of the example given earlier in this lab (changes are highlighted in bold).
For Example ch06_1c.sql
DECLARE
v_counter BINARY_INTEGER := 0;
BEGIN
LOOP
-- increment loop counter by one
v_counter := v_counter + 1;
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
-- if exit condition yields TRUE exit the loop
EXIT WHEN v_counter = 5;
END LOOP;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Done...');
END;
In this version, the IF and EXIT statements have been replaced by the EXIT WHEN statement. As expected, this version produces the same output as the original example:
v_counter = 1
v_counter = 2
v_counter = 3
v_counter = 4
v_counter = 5
Done...
Lab 6.2: WHILE Loops
After this lab, you will be able to
Use WHILE loops
Terminate WHILE loops prematurely
Using WHILE Loops
A WHILE loop has the structure as shown in Listing 6.4.
Listing 6.4 WHILE Loop Structure
WHILE TEST CONDITION LOOP
STATEMENT 1;
STATEMENT 2;
...
STATEMENT N;
END LOOP;
The reserved word WHILE marks the beginning of a loop construct. The TEST CONDITION is the test condition of the loop that evaluates to TRUE or FALSE. The result of this evaluation determines whether the loop is executed. Statements 1 through N are a sequence of statements that is executed repeatedly. END LOOP is a reserved phrase that indicates the end of the loop construct. This flow of the logic is illustrated in Figure 6.3.
Figure 6.3 WHILE Loop
Figure 6.3 shows that the test condition is evaluated prior to each iteration of the loop. If the TEST CONDITION evaluates to TRUE, the sequence of statements is executed, and control passes to the top of the loop for the next evaluation of the test condition. If the TEST CONDITIONevaluates to FALSE, the loop is terminated, and control passes to the next executable statement following the loop.
As mentioned earlier, before the body of the loop can be executed, the test condition must be evaluated. The decision as to whether to execute the statements in the body of the loop is made prior to entering the loop. As a result, the loop will not be executed at all if the test condition yieldsFALSE.
For Example ch06_2a.sql
DECLARE
v_counter NUMBER := 5;
BEGIN
WHILE v_counter < 5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
-- decrement the value of v_counter by one
v_counter := v_counter - 1;
END LOOP;
END;
In this example, the body of the loop is not executed at all because the test condition
v_counter < 5
of the loop evaluates to FALSE as the variable v_counter is initialized to 5.
The test condition must evaluate to TRUE at least once for the statements in the loop to execute. However, it is also important to ensure that the test condition will eventually evaluate to FALSE. Otherwise, the WHILE loop will execute continually, as demonstrated by the following example (changes are shown in bold).
For Example ch06_2b.sql
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter < 5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
-- decrement the value of v_counter by one
v_counter := v_counter - 1;
END LOOP;
END;
This is an example of an infinite WHILE loop. The test condition always evaluates to TRUE, because the value of v_counter is decremented by 1 and is always less than 5.
Now consider a modified version of this example, where the loop executes four times. In this example, the test condition eventually evaluates to FALSE because the value of v_counter is incremented by 1. Affected statements are shown in bold.
For Example ch06_2c.sql
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter < 5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
-- increment the value of v_counter by one
v_counter := v_counter + 1;
END LOOP;
END;
This version of the example produces the following output:
v_counter = 1
v_counter = 2
v_counter = 3
v_counter = 4
Did You Know?
Boolean expressions can also be used to determine when the loop should terminate.
DECLARE
v_test BOOLEAN := TRUE;
BEGIN
WHILE v_test
LOOP
STATEMENTS;
IF TEST CONDITION
THEN
v_test := FALSE;
END IF;
END LOOP;
END;
When using a Boolean expression as a test condition of a loop, you must ensure that a different value is eventually assigned to the Boolean variable to exit the loop. Otherwise, the loop will become infinite.
Premature Termination of the WHILE Loop
The EXIT and EXIT WHEN statements can be used inside the body of a WHILE loop. If the exit condition evaluates to TRUE before the test condition evaluates to FALSE, the loop is terminated prematurely. If the test condition evaluates to FALSE before the exit condition evaluates toTRUE, there is no premature termination of the loop. This structure is shown in Listing 6.5.
Listing 6.5 Premature Termination of the WHILE Loop
WHILE TEST CONDITION LOOP
STATEMENT 1;
STATEMENT 2;
IF EXIT CONDITION
THEN
EXIT;
END IF;
END LOOP;
STATEMENT 3;
Or
WHILE TEST CONDITION
LOOP
STATEMENT 1;
STATEMENT 2;
EXIT WHEN EXIT CONDITION;
END LOOP;
STATEMENT 3;
Consider the following example:
For Example ch06_3a.sql
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
IF v_counter = 2
THEN
EXIT;
END IF;
v_counter := v_counter + 1;
END LOOP;
END;
Before the statements in the body of the WHILE loop are executed, the test condition
v_counter <= 5
must evaluate to TRUE. Then, the value of v_counter is displayed on the screen and incremented by 1. Next, the exit condition
v_counter = 2
is evaluated. As soon as the value of v_counter reaches 2, the loop is terminated.
According to the test condition, the loop should execute five times. However, the loop is executed only twice, because the exit condition is present inside the body of the loop. Therefore, the loop terminates prematurely.
Now try to reverse the test condition and the exit condition, as shown in the following example (all changes are shown in bold).
For Example ch06_3b.sql
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 2
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
v_counter := v_counter + 1;
IF v_counter = 5
THEN
EXIT;
END IF;
END LOOP;
END;
In this version of the example, the test condition is
v_counter <= 2
and the exit condition is
v_counter = 5
In this case, the loop is executed twice. However, it does not terminate prematurely, because the exit condition never evaluates to TRUE. As soon as the value of v_counter reaches 3, the test condition evaluates to FALSE, and the loop is terminated.
Both examples, when run, produce the following output:
v_counter = 1
v_counter = 2
These examples demonstrate not only the use of the EXIT statement inside the body of the WHILE loop, but also a bad programming practice. In the first example, the test condition can be changed so that there is no need to use an exit condition, because essentially both conditions are used to terminate the loop. In the second example, the exit condition is useless, because its terminal value is never reached. You should never include unnecessary code in your programs.
Lab 6.3: Numeric FOR Loops
After this lab, you will be able to
Use numeric FOR loops with the IN option
Use numeric FOR loops with the REVERSE option
Terminate numeric FOR loops prematurely
A numeric FOR loop is called numeric because it requires an integer as its terminating value. The structure of such a loop is shown in Listing 6.6.
Listing 6.6 Numeric FOR Loop Structure
FOR loop_counter IN [REVERSE] lower_limit..upper_limit
LOOP
STATEMENT 1;
STATEMENT 2;
...
STATEMENT N;
END LOOP;
The reserved word FOR marks the beginning of the FOR loop construct. The variable loop_counter is an implicitly defined index variable. There is no need to define the loop counter in the declaration section of the PL/SQL block; instead, this variable is defined by the loop construct. The lower_limit and upper_limit are integer numbers or expressions that evaluate to integer values at run time, and the double dot (..) serves as the range operator. The lower_limit and upper_limit define the number of iterations for the loop, and their values are evaluated once, for the first iteration of the loop. At this point, it is determined how many times the loop will iterate. Statements 1 through N are a sequence of statements that is executed repeatedly. END LOOP is a reserved phrase that marks the end of the loop construct.
One of the reserved words IN or IN REVERSE must be present when defining the loop. When the REVERSE keyword is used, the loop counter will iterate from the upper limit to the lower limit. However, the syntax for the limit specification does not change. The lower limit is always referenced first. The flow of this logic is illustrated in Figure 6.4.
Figure 6.4 Numeric FOR Loop
Figure 6.4 shows that the loop counter is initialized to the lower limit for the first iteration of the loop only. However, the value of the loop counter is tested for each iteration of the loop. As long as the value of v_counter ranges from the lower limit to the upper limit, the statements inside the body of the loop are executed. When the value of the loop counter falls outside the range specified by the lower limit and the upper limit, control passes to the first executable statement outside the loop.
Using the IN Option in the Loop
Consider the following example, which illustrates a numeric FOR loop that employs the IN option.
For Example ch06_4a.sql
BEGIN
FOR v_counter IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
END LOOP;
END;
In this example, there is no declaration section for the PL/SQL block because the only variable used, v_counter, is the loop counter. Numbers 1..5 specify the range of the integer numbers for which this loop is executed.
Notice that there is no statement
v_counter := v_counter + 1;
anywhere, either inside or outside the body of the loop. The value of v_counter is incremented implicitly by the FOR loop itself.
This example produces the following output when run:
v_counter = 1
v_counter = 2
v_counter = 3
v_counter = 4
v_counter = 5
If you include the statement
v_counter := v_counter + 1;
in the body of the loop, the PL/SQL script will report errors when you try to compile it. Consider the following example (newly added statement is shown in bold).
For Example ch06_4b.sql
BEGIN
FOR v_counter IN 1..5
LOOP
v_counter := v_counter + 1;
DBMS_OUTPUT.PUT_LINE ('v_counter = '|| v_counter);
END LOOP;
END;
When this example is run, it produces the following error message:
ORA-06550: line 4, column 7:
PLS-00363: expression 'V_COUNTER' cannot be used as an assignment target
ORA-06550: line 4, column 7:
PL/SQL: Statement ignored
Watch Out!
The loop counter is implicitly defined and incremented when a numeric FOR loop is used. As a result, it cannot be referenced outside the body of the FOR loop. Consider the following example:
BEGIN
FOR v_counter IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Counter outside the loop is '||v_counter);
END;
When this example is run, it produces the following error message:
('Counter outside the loop is '||v_counter);
*
ORA-06550: line 6, column 58:
PLS-00201: identifier 'V_COUNTER' must be declared
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored
Because the loop counter is declared implicitly by the loop, the variable v_counter cannot be referenced outside the loop. As soon as the loop completes, the loop counter ceases to exist.
Using the REVERSE Option in the Loop
Earlier in this lab, you encountered two options that are available when the value of the loop counter is evaluated, IN and IN REVERSE. You have already seen examples that demonstrate the usage of the IN option for the loop. The next example demonstrates the usage of the IN REVERSEoption for the loop.
For Example ch06_5a.sql
BEGIN
FOR v_counter IN REVERSE 1..5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
END LOOP;
END;
When this example is run, it produces the following output:
v_counter = 5
v_counter = 4
v_counter = 3
v_counter = 2
v_counter = 1
As mentioned earlier, even though the REVERSE keyword is present, the lower limit of the loop is referenced first. However, the loop counter is evaluated from the upper limit to the lower limit. For the first iteration of the loop, v_counter (in our case, it is a loop counter) is initialized to 5 (the upper limit). Then its value is displayed on the screen. For the second iteration of the loop, the value of v_counter is decreased by 1, and displayed on the screen.
The number of times the body of the loop is executed is not affected by which option is used, IN or IN REVERSE. Only the values assigned to the lower limit and the upper limit determine how many times the body of the loop executes.
Premature Termination of the Numeric FOR Loop
The EXIT and EXIT WHEN statements covered in the previous labs can be used inside the body of a numeric FOR loop as well. If the exit condition evaluates to TRUE before the loop counter reaches its terminal value, the FOR loop is terminated prematurely. If the loop counter reaches its terminal value before the exit condition yields TRUE, there is no premature termination of the FOR loop. This structure is shown in Listing 6.7.
Listing 6.7 Premature Termination of the Numeric FOR Loop
FOR loop_counter IN lower_limit..upper_limit
LOOP
STATEMENT 1;
STATEMENT 2;
IF EXIT CONDITION THEN
EXIT;
END IF;
END LOOP;
STATEMENT 3;
Or
FOR loop_counter IN lower_limit..upper_limit
LOOP
STATEMENT 1;
STATEMENT 2;
EXIT WHEN EXIT CONDITION;
END LOOP;
STATEMENT 3;
Consider the following example of a FOR loop that uses the exit when condition. This condition causes the loop to terminate prematurely.
For Example ch06_6a.sql
BEGIN
FOR v_counter IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE ('v_counter = '||v_counter);
EXIT WHEN v_counter = 3;
END LOOP;
END;
According to the range specified, the loop should execute five times. However, the loop executes only three times because the exit condition appears inside the body of the loop. Thus, the loop terminates prematurely, as indicated by the example’s output:
v_counter = 1
v_counter = 2
v_counter = 3
Summary
In this chapter, you explored three types of loops supported in PL/SQL. You also learned how to employ exit conditions to prevent infinite loops and how to terminate loops prematurely. In the next chapter, you will continue to learn about loops and discover how they can be nested inside one another. Furthermore, you will learn about other loop features, CONTINUE and CONTINUE WHEN, that were introduced in Oracle 11g.
By the Way
The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.