Executes a block of SQL statements while the FOR loop variable is
incremented (or decremented) from a user-specified starting value
to a user-specified ending value.
1 – Environment
You can use the FOR counted control statement in a compound
statement of a multistatement procedure:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
counted-for-statement =
-+------------------------+-> FOR <variable-name> --------------+
+--> <beginning-label> : + |
+---------------------------------<----------------------------+
+--> IN -+-------------+-> value-expr --> TO --> value-expr ---+
+-> REVERSE --+ |
+--------------------------------------------------------------+
+-+--------------------+-> DO -+-> compound-use-statement --+--+
+-> STEP value-expr -+ +------------ <--------------+ |
+-----------------------------------<--------------------------+
+--> END FOR -+------------------+------------------------------>
+-> <ending-label> +
3 – Arguments
3.1 – AS EACH ROW OF select-expression
Creates a simple result table.
After SQL creates the result table from the select expression,
the DO clause executes a set of SQL statements (compound-use-
statement) for each result table row. See Select_Expressions
in the Oracle Rdb SQL Reference Manual for more information on
select expressions.
3.2 – beginning-label:
Assigns a name to the FOR statement. A named FOR loop is called
a labeled FOR loop statement. If you include an ending label,
it must be identical to its corresponding beginning label. A
beginning label must be unique within the procedure in which the
label is contained.
3.3 – compound-use-statement
Identifies the SQL statements allowed in a compound statement
block. See Compound_Stmt for a complete description of a compound
statement.
3.4 – DO compound-use-statement
Executes a block of SQL statements once for each execution of the
loop as defined by the starting and ending value expressions.
3.5 – END_FOR
Syntax:
END FOR | END FOR ending-label
Marks the end of a FOR loop. If you choose to include the
optional ending label, it must match exactly its corresponding
beginning label. An ending label must be unique within the
procedure in which the label is contained. The optional end-label
argument makes the FOR loops of multistatement procedures easier
to read, especially in very complex procedure blocks.
3.6 – FOR variable name
Specifies a variable to hold a value that is incremented each
time the FOR loop is executed. The variable is decremented if the
REVERSE keyword is specified. The starting value for the variable
is the first value expression. Execution of the FOR loop ends
when the variable has been incremented (or decremented) to the
value specified with the second value expression.
Marks the end of a FOR loop. If you choose to include the
optional ending label, it must match exactly its corresponding
beginning label. An ending label must be unique within the
procedure in which the label is contained. The optional end-label
argument makes the FOR loops of multistatement procedures easier
to read, especially in very complex procedure blocks.
3.7 – IN value-expression-option
Syntax options:
IN value-expr TO value-expr
IN REVERSE value-expr TO value-expr
Specifies how often the compound-use-statement should be
executed. When the REVERSE keyword is not specified, the variable
contained in the FOR variable-name is incremented at the end of
each execution of the FOR loop body. When the REVERSE keyword
is specified, the variable contained in the FOR variable-name is
decremented at the end of each execution of the FOR loop body.
Both value expressions are evaluated once before the loop
executes. The TO value-expression is evaluated first to ensure
that references to the FOR loop variable do not cause side
effects.
3.8 – select_expression
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a complete description of select expressions.
3.9 – STEP value-expr
Controls the size of the increment between loop interations. The
step size is specified using a numeric value expression.
If omitted the default step size is 1.
SQL> begin
cont> declare :i integer;
cont> for :i in 1 to 20 step 5
cont> do
cont> trace :i;
cont> end for;
cont> end;
~Xt: 1
~Xt: 6
~Xt: 11
~Xt: 16
NOTE
Even if the loop control variable is an INTERVAL type the
STEP must be numeric type. In addition the value must be
greater than zero - use the REVERSE keyword to decrement the
loop control variable.
3.10 – value-expr
Syntax:
IN value-expr TO value-expr
IN REVERSE value-expr TO value-expr
Specifies how often the compound-use-statement should be
executed. When the REVERSE keyword is not specified, the variable
contained in the FOR variable-name is incremented at the end of
each execution of the FOR loop body. When the REVERSE keyword
is specified, the variable contained in the FOR variable-name is
decremented at the end of each execution of the FOR loop body.
Both value expressions are evaluated once before the loop
executes. The TO value-expression is evaluated first to ensure
that references to the FOR loop variable do not cause side
effects.
4 – Examples
Example 1: Using a Reverse Loop
SQL> SET FLAGS 'TRACE';
SQL> BEGIN
cont> DECLARE :LOOP_VAR INTEGER;
cont> FOR :LOOP_VAR IN REVERSE 1 TO 5
cont> DO
cont> TRACE :LOOP_VAR;
cont> END FOR;
cont> END;
~Xt: 5
~Xt: 4
~Xt: 3
~Xt: 2
~Xt: 1
Example 2: Using an INTERVAL type as the loop variable
SQL> begin
cont> declare :i interval year;
cont> for :i in (interval'1' year) to (interval'4'year)
cont> do
cont> trace :i;
cont> end for;
cont> end;
~Xt: 01
~Xt: 02
~Xt: 03
~Xt: 04
Example 3: Using a complex expression as the STEP expression
SQL> begin
cont> declare :i interval year;
cont> declare :k interval year = interval'18'year;
cont> declare :j integer = 2;
cont>
cont> for :i in (interval'1' year) to :k/2 step :j*2
cont> do
cont> trace :i;
cont> end for;
cont> end;
~Xt: 01
~Xt: 05
~Xt: 09