FOR (integer variant) statement (PL/SQL)

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

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