PL/SQL User's Guide and Reference

LOOP Statements

Description

LOOP statements execute a sequence of statements multiple times. The loop encloses the sequence of statements that is to be repeated. PL/SQL provides the following types of loop statements:

basic loop

WHILE loop

FOR loop

cursor FOR loop

For more information, see "Iterative Control"

Syntax

basic_loop_statement ::=

[<<label_name>>]
LOOP
   seq_of_statements
END LOOP [label_name];

while_loop_statement ::=

[<<label_name>>]
WHILE boolean_expression 
LOOP
   seq_of_statements
END LOOP [label_name];

for_loop_statement ::=

[<<label_name>>]
FOR index_name IN [REVERSE] lower_bound..upper_bound 
LOOP
   seq_of_statements
END LOOP [label_name];

cursor_for_loop_statement ::=

[<<label_name>>]
FOR record_name IN 
 {  cursor_name [(cursor_parameter_name[,
      cursor_parameter_name]...)]
  | (select_statement)} 
LOOP
   seq_of_statements
END LOOP [label_name];

Keyword and Parameter Description

label_name

This is an undeclared identifier that optionally labels a loop. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the loop. Optionally, label_name can also appear at the end of the loop.

You can use label_name in an EXIT statement to exit the loop labelled by label_name.

You cannot reference the index of a FOR loop from a nested FOR loop if both indexes have the same name unless the outer loop is labeled by label_name and you use dot notation, as follows:

label_name.index_name

In the following example, you compare two loop indexes that have the same name, one used by an enclosing loop, the other by a nested loop:

<<outer>>
FOR ctr IN 1..20 LOOP
   ...
   <<inner>>
   FOR ctr IN 1..10 LOOP
      IF outer.ctr > ctr THEN ...
         ...
   END LOOP inner;
END LOOP outer;

basic_loop_statement

The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and 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, GOTO, or RAISE statement to complete the loop. A raised exception will also complete the loop.

while_loop_statement

The WHILE-LOOP statement associates a Boolean expression with a sequence of statements enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression yields FALSE or NULL, the loop is bypassed and control passes to the next statement.

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE. For the syntax of boolean_expression, see "Expressions"

for_loop_statement

Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. Numeric FOR loops iterate over a specified range of integers. (Cursor FOR loops, which iterate over the result set of a cursor, are discussed later.) The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

The range is evaluated when the FOR loop is first entered and is never re-evaluated. The sequence of statements in the loop is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.

index_name

This is an undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself. Therefore, you cannot reference the index outside the loop.

The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the loop unless a label is used, as follows:

<<main>>
DECLARE
   num NUMBER;
BEGIN
   ...
   FOR num IN 1..10 LOOP
      ...
      IF main.num > 5 THEN  -- refers to the variable num,
         ...                -- not to the loop index
      END IF;
   END LOOP;
END main;

Inside a loop, its index is treated like a constant. The index can appear in expressions, but cannot be assigned a value.

lower_bound, upper_bound

These are expressions that must yield integer values. The expressions are evaluated only when the loop is first entered.

By default, the loop index is assigned the value of lower_bound. If that value is not greater than the value of upper_bound, the sequence of statements in the loop is executed, then the index is incremented. If the value of the index is still not greater than the value of upper_bound, the sequence of statements is executed again. This process repeats until the value of the index is greater than the value of upper_bound. At that point, the loop completes.

REVERSE

By default, iteration proceeds upward from the lower bound to the upper bound. However, if you use the keyword REVERSE, iteration proceeds downward from the upper bound to the lower bound. After each iteration, the loop index is decremented.

In this case, the loop index is assigned the value of upper_bound. If that value is not less than the value of lower_bound, the sequence of statements in the loop is executed, then the index is decremented. If the value of the index is still not less than the value of lower_bound, the sequence of statements is executed again. This process repeats until the value of the index is less than the value of lower_bound. At that point, the loop completes. An example follows:

FOR i IN REVERSE 1..10 LOOP  -- i starts at 10, ends at 1
   -- statements here execute 10 times
END LOOP;

cursor_for_loop_ statement

A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed. Thus, the sequence of statements in the loop is executed once for each row that satisfies the query associated with cursor_name.

cursor_name

This identifies an explicit cursor previously declared within the current scope. When the cursor FOR loop is entered, cursor_name cannot refer to a cursor already opened by an OPEN statement or an enclosing cursor FOR loop.

record_name

This identifies an implicitly declared record. The record has the same structure as a row retrieved by cursor_name and is equivalent to a record declared as follows:

record_name cursor_name%ROWTYPE;

The record is defined only inside the loop. You cannot refer to its fields outside the loop. The implicit declaration of record_name overrides any other declaration outside the loop. So, another record with the same name cannot be referenced inside the loop unless a label is used.

Fields in the record store column values from the implicitly fetched row. The fields have the same names and datatypes as their corresponding columns. To access field values, you use dot notation, as follows:

record_name.field_name

Select-items fetched from the FOR loop cursor must have simple names or, if they are expressions, must have aliases. In the following example, wages is an alias for the select item sal+NVL(comm,0):

CURSOR c1 IS SELECT empno, sal+NVL(comm,0) wages, job ...

cursor_parameter_name

This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters. For the syntax of cursor_parameter_declaration, see "Cursors"

select_statement

This is a query associated with an internal cursor unavailable to you. PL/SQL automatically declares, opens, fetches from, and closes the internal cursor. Because select_statement is not an independent statement, the implicit SQL cursor does not apply to it.

The syntax of select_statement is like the syntax of select_into_ statement defined in "SELECT INTO Statement" , except that select_statement cannot have an INTO clause.

Usage Notes

You can use the EXIT WHEN statement to exit any loop prematurely. If the Boolean expression in the WHEN clause yields TRUE, the loop is exited immediately. For more information, see "EXIT Statement"

When you exit a cursor FOR loop, the cursor is closed automatically even if you use an EXIT or GOTO statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.

Example

The following cursor FOR loop calculates a bonus, then inserts the result into a database table:

DECLARE
   bonus  REAL;
   CURSOR c1 IS SELECT empno, sal, comm FROM emp;
BEGIN
   FOR c1rec IN c1 LOOP
      bonus := (c1rec.sal * 0.05) + (c1rec.comm * 0.25);
      INSERT INTO bonuses VALUES (c1rec.empno, bonus);
   END LOOP;
   COMMIT;
END;