|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
LOOP statements execute a sequence of statements multiple times. The LOOP and END LOOP keywords enclose the statements. PL/SQL provides four kinds of loop statements: basic loop,
FOR loop, and cursor
FOR loop. For usage information, see "Controlling Loop Iterations: LOOP and EXIT Statements".
Keyword and Parameter Description
A loop that executes an unlimited number of times. It encloses a sequence of statements between the keywords
LOOP. With each iteration, the sequence of statements is executed, then control resumes at the top of the loop. An
RAISE statement branches out of the loop. A raised exception also ends the loop.
An expression that returns the Boolean value
NULL. It is associated with a sequence of statements, which is executed only if the expression returns
TRUE. For the syntax of
boolean_expression, see "Expressions".
Issues a SQL query and loops through the rows in the result set. This is a convenient technique that makes processing a query as simple as reading lines of text in other programming languages.
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.
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
A variable declared as the formal parameter of a cursor. (For the syntax of
cursor_parameter_declaration, see "Cursors".) A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be
FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords
The range is evaluated when the
FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by
lower_bound..upper_bound. After each iteration, the loop index is incremented.
An undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself; you cannot reference the index outside the loop.
The implicit declaration of
index_name overrides any other declaration outside the loop. To refer to another variable with the same name, use a label:
<<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, the index is treated like a constant: it can appear in expressions, but cannot be assigned a value.
An optional undeclared identifier that labels a loop.
label_name must be enclosed by double angle brackets and must appear at the beginning of the loop. Optionally,
label_name (not enclosed in angle brackets) 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 can exit not only the current loop, but any enclosing loop.
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:
The following example compares 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;
Expressions that return numbers. (Otherwise, PL/SQL raises the predefined exception
VALUE_ERROR.) The expressions are evaluated only when the loop is first entered. The lower bound need not be 1, as the example below shows. The loop counter increment (or decrement) must be 1.
FOR i IN -5..10 LOOP ... END LOOP;
Internally, PL/SQL assigns the values of the bounds to temporary
PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a
2**31. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment.
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.
An implicitly declared record. The record has the same structure as a row retrieved by
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. You cannot refer to another record with the same name inside the loop unless you qualify the reference using a block label.
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:
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
CURSOR c1 IS SELECT empno, sal+comm wages, job ...
By default, iteration proceeds upward from the lower bound to the upper bound. If you use the keyword
REVERSE, iteration proceeds downward from the upper bound to the lower bound.
An example follows:
FOR i IN REVERSE 1..10 LOOP -- i starts at 10, ends at 1 -- statements here execute 10 times END LOOP;
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.
A query associated with an internal cursor unavailable to you. Its syntax is like that of
select_into_statement without the
INTO clause. See "SELECT INTO Statement". PL/SQL automatically declares, opens, fetches from, and closes the internal cursor. Because
select_statement is not an independent statement, the implicit cursor
SQL does not apply to it.
WHILE-LOOP statement associates a Boolean expression with a sequence of statements enclosed by the keywords
LOOP. Before each iteration of the loop, the expression is evaluated. If the expression returns
TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression returns
NULL, the loop is bypassed and control passes to the next statement.
You can use the
WHEN statement to exit any loop prematurely. If the Boolean expression in the
WHEN clause returns
TRUE, the loop is exited immediately.
When you exit a cursor
FOR loop, the cursor is closed automatically even if you use an
GOTO statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.
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;