1 FOR_Counted_Control 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. 2 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 --------------+ +--> : + | +---------------------------------<----------------------------+ +--> IN -+-------------+-> value-expr --> TO --> value-expr ---+ +-> REVERSE --+ | +--------------------------------------------------------------+ +-+--------------------+-> DO -+-> compound-use-statement --+--+ +-> STEP value-expr -+ +------------ <--------------+ | +-----------------------------------<--------------------------+ +--> END FOR -+------------------+------------------------------> +-> + 2 Arguments 3 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 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 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 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 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 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 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 select_expression See Select_Expressions in the Oracle Rdb SQL Reference Manual for a complete description of select expressions. 3 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 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. 2 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