Iterative Control: LOOP and EXIT Statements

LOOP

LOOP
   sequence_of_statements;
END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If further processing is undesirable or impossible, you can use the EXIT statement to complete the loop. You can place one or more EXIT statements anywhere inside a loop, but nowhere outside a loop. There are two forms of EXIT statements: EXIT and EXIT-WHEN.

EXIT

LOOP
   ...
   IF credit_rating < 3 THEN
       ...
      EXIT;  -- exit loop immediately
   END IF;
END LOOP;
-- control resumes here

The next example shows that you cannot use the EXIT statement to complete a PL/SQL block:

BEGIN
   ...
   IF credit_rating < 3 THEN
      ...
      EXIT;  -- illegal
   END IF;
END;

Remember, the EXIT statement must be placed inside a loop. To complete a PL/SQL block before its normal end is reached, you can use the RETURN statement. For more information, see "RETURN Statement" .

EXIT-WHEN

LOOP
   FETCH c1 INTO ...
   EXIT WHEN c1%NOTFOUND;  -- exit loop if condition is true
   ...
END LOOP;
CLOSE c1;

Until the condition yields TRUE, the loop cannot complete. So, statements within the loop must change the value of the condition. In the last example, if the FETCH statement returns a row, the condition yields FALSE. When the FETCH statement fails to return a row, the condition yields TRUE, the loop completes, and control passes to the CLOSE statement.

The EXIT-WHEN statement replaces a simple IF statement. For example, compare the following statements:

IF count > 100 THEN     |     EXIT WHEN count > 100;
   EXIT;                |
END IF;                 |

These statements are logically equivalent, but the EXIT-WHEN statement is easier to read and understand.

Loop Labels

<<label_name>>
LOOP
   sequence_of_statements;
END LOOP;

Optionally, the label name can also appear at the end of the LOOP statement, as the following example shows:

<<my_loop>>
LOOP
   ...
END LOOP my_loop;

When you nest labeled loops, you can use ending label names to improve readability.

With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement, as follows:

<<outer>>
LOOP
   ...
   LOOP
      ...
      EXIT outer WHEN ...  -- exit both loops
   END LOOP;
   ...
END LOOP outer;

Every enclosing loop up to and including the labeled loop is exited.

WHILE-LOOP

WHILE condition LOOP
   sequence_of_statements;
END LOOP;

Before each iteration of the loop, the condition is evaluated. If the condition yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the condition yields FALSE or NULL, the loop is bypassed and control passes to the next statement. An example follows:

WHILE total <= 25000 LOOP
   ...
   SELECT sal INTO salary FROM emp WHERE ...
   total := total + salary;
END LOOP;

The number of iterations depends on the condition and is unknown until the loop completes. Since the condition is tested at the top of the loop, the sequence might execute zero times. In the last example, if the initial value of total is greater than 25000, the condition yields FALSE and the loop is bypassed.

Some languages have a LOOP UNTIL or REPEAT UNTIL structure, which tests the condition at the bottom of the loop instead of at the top. Therefore, the sequence of statements is executed at least once. PL/SQL has no such structure, but you can easily build one, as follows:

LOOP
   sequence_of_statements;
   EXIT WHEN boolean_expression;
END LOOP;

To ensure that a WHILE loop executes at least once, use an initialized Boolean variable in the condition, as follows:

done := FALSE;
WHILE NOT done LOOP
   sequence_of_statements;
   done := boolean_expression;
END LOOP;

A statement inside the loop must assign a new value to the Boolean variable. Otherwise, you have an infinite loop. For example, the following LOOP statements are logically equivalent:

WHILE TRUE LOOP     |     LOOP
   ...              |        ...
END LOOP;           |     END LOOP;

FOR-LOOP

FOR counter IN [REVERSE] lower_bound..higher_bound LOOP
   sequence_of_statements;
END LOOP;

The range is evaluated when the FOR loop is first entered and is never re-evaluated. As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.

FOR i IN 1..3 LOOP  -- assign the values 1,2,3 to i
   sequence_of_statements;  -- executes three times
END LOOP;

The following example shows that if the lower bound equals the higher bound, the sequence of statements is executed once:

FOR i IN 3..3 LOOP  -- assign the value 3 to i
   sequence_of_statements;  -- executes one time
END LOOP;

By default, iteration proceeds upward from the lower bound to the higher bound. However, if you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound, as the example below shows. After each iteration, the loop counter is decremented.

FOR i IN REVERSE 1..3 LOOP  -- assign the values 3,2,1 to i
   sequence_of_statements;  -- executes three times
END LOOP;

Nevertheless, you write the range bounds in ascending (not descending) order.

Inside a FOR loop, the loop counter can be referenced like a constant. So, the loop counter can appear in expressions but cannot be assigned values, as the following example shows:

FOR ctr IN 1..10 LOOP
   ...
   IF NOT finished THEN
      INSERT INTO ... VALUES (ctr, ...);  -- legal
      factor := ctr * 2;  -- legal
   ELSE
      ctr := 10;  -- illegal
   END IF;
END LOOP;

Iteration Schemes

j IN -5..5
k IN REVERSE first..last
step IN 0..TRUNC(high/low) * 2
code IN ASCII('A')..ASCII('J')

As you can see, the lower bound need not be 1. However, the loop counter increment (or decrement) must be 1. Some languages provide a STEP clause, which lets you specify a different increment. An example written in BASIC follows:

FOR J = 5 TO 15 STEP 5  :REM assign values 5,10,15 to J
   sequence_of_statements  -- J has values 5,10,15
NEXT J

PL/SQL has no such structure, but you can easily build one. Consider the following example:

FOR j IN 5..15 LOOP  -- assign values 5,6,7,... to j
   IF MOD(j, 5) = 0 THEN  -- pass multiples of 5
      sequence_of_statements;  -- j has values 5,10,15
   END IF;
END LOOP;

This loop is logically equivalent to the previous BASIC loop. Within the sequence of statements, the loop counter has only the values 5, 10, and 15.

You might prefer the less elegant but more efficient method shown in the example below. Within the sequence of statements, each reference to the loop counter is multiplied by the increment.

FOR j IN 1..3 LOOP  -- assign values 1,2,3 to j
   sequence_of_statements;  -- each j becomes j*5
END LOOP;

Dynamic Ranges

SELECT COUNT(empno) INTO emp_count FROM emp;
FOR i IN 1..emp_count LOOP
   ...
END LOOP;

The value of emp_count is unknown at compile time; the SELECT statement returns the value at run time.

What happens if the lower bound of a loop range evaluates to a larger integer than the upper bound? As the following example shows, the sequence of statements within the loop is not executed and control passes to the next statement:

-- limit becomes 1
FOR i IN 2..limit LOOP
   sequence_of_statements;  -- executes zero times
END LOOP;
-- control passes here

Scope Rules

FOR ctr IN 1..10 LOOP
   ...
END LOOP;
sum := ctr - 1;  -- illegal

You need not explicitly declare the loop counter because it is implicitly declared as a local variable of type INTEGER. The next example shows that the local declaration hides any global declaration:

DECLARE
   ctr  INTEGER;
BEGIN
   ...
   FOR ctr IN 1..25 LOOP
      ...
      IF ctr > 10 THEN ...  -- refers to loop counter
   END LOOP;
END;

To reference the global variable in this example, you must use a label and dot notation, as follows:

<<main>>
DECLARE
   ctr  INTEGER;
   ...
BEGIN
   ...
   FOR ctr IN 1..25 LOOP
      ...
      IF main.ctr > 10 THEN ...  -- refers to global variable
   END LOOP;
END main;

The same scope rules apply to nested FOR loops. Consider the example below. Both loop counters have the same name. So, to reference the outer loop counter from the inner loop, you must use a label and dot notation, as follows:

<<outer>>
FOR step IN 1..25 LOOP
   FOR step IN 1..10 LOOP
      ...
      IF outer.step > 15 THEN ...
   END LOOP;
END LOOP outer;

Using the EXIT Statement

FOR j IN 1..10 LOOP
   FETCH c1 INTO emp_rec;
   EXIT WHEN c1%NOTFOUND;
   ...
END LOOP;

Suppose you must exit from a nested FOR loop prematurely. You can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement to specify which FOR loop to exit, as follows:

<<outer>>
FOR i IN 1..5 LOOP
   ...
   FOR j IN 1..10 LOOP
      FETCH c1 INTO emp_rec;
      EXIT outer WHEN c1%NOTFOUND;  -- exit both FOR loops
      ...
   END LOOP;
END LOOP outer;
-- control passes here