Use the FOR statement to execute a set of SQL statements
more than once.
Invocation
This statement can be embedded
within a PL/SQL procedure, function, or anonymous block statement.
Authorization
No privileges are required
to invoke the FOR statement; however, the authorization ID of the
statement must hold the necessary privileges to invoke the SQL statements
that are embedded in the FOR statement.
Syntax
>>-FOR--integer-variable--IN--+---------+----------------------->
'-REVERSE-'
>--expression1--..--expression2--LOOP--statements--END LOOP----><
Description
- integer-variable
- An automatically defined integer variable that is used during
loop processing. The initial value of integer-variable is expression1.
After the initial iteration, the value of integer-variable is
incremented at the beginning of each subsequent iteration. Both expression1 and expression2 are
evaluated when entering the loop, and loop processing stops when integer-variable is
equal to expression2.
- IN
- Introduces the optional REVERSE keyword and expressions that define
the range of integer variables for the loop.
- REVERSE
- Specifies that the iteration is to proceed from expression2 to expression1.
Note that expression2 must have a higher value
than expression1, regardless of whether the REVERSE
keyword is specified, if the statements in the loop are to be processed.
- expression1
- Specifies the initial value of the range of integer variables
for the loop. If the REVERSE keyword is specified, expression1 specifies
the end value of the range of integer variables for the loop.
- expression2
- Specifies the end value of the range of integer variables for
the loop. If the REVERSE keyword is specified, expression2 specifies
the initial value of the range of integer variables for the loop.
- statements
- Specifies the PL/SQL and SQL statements that are executed each
time that the loop is processed.
Examples
The following example shows a basic
FOR statement within an anonymous block:
BEGIN
FOR i IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
END LOOP;
END;
This example generates the following output:
Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10
If the start value is greater than the end
value, the loop body is not executed at all, but no error is returned,
as shown by the following example:
BEGIN
FOR i IN 10 .. 1 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
END LOOP;
END;
This example generates no output, because the loop
body is never executed.
The following example uses the REVERSE
keyword:
BEGIN
FOR i IN REVERSE 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
END LOOP;
END;
This example generates the following output:
Iteration # 10
Iteration # 9
Iteration # 8
Iteration # 7
Iteration # 6
Iteration # 5
Iteration # 4
Iteration # 3
Iteration # 2
Iteration # 1
DB2 Version 10.1 for Linux, UNIX, and Windows