Use the FOR statement to initiate a controlled (definite) loop when you want to guarantee termination of the loop. The FOR statement uses expressions or range operators to specify a finite number of iterations for a loop.



1  left_expr TO right_expr?  STEP increment_expr
  • 1 See Statement Block
  • 2 Valid only if <<label>> precedes the first FOR keyword

Element Description Restrictions Syntax
expression Value to compare with loop_var Must match loop_var data type Expression
increment_expr Positive or negative value by which loop_var is incremented. Default is either 1 (if left_expr < right_expr), or else -1 (if left_expr > right_expr). Must return an integer. Cannot return 0. Expression
label Name of the loop label for this loop Must exist and must be unique among label names in this SPL routine Identifier
left_expr Starting expression of a range Value must match SMALLINT or INT data type of loop_var, but left_expr must not equal right_expr Expression
loop_var Variable that determines how many times the loop executes Must be defined and in scope within this statement block Identifier
right_expr Ending expression in the range Same as for left_expr Expression


The database server evaluates all expressions before the FOR statement executes. If one or more of the expressions are variables whose values change during the loop, the change has no effect on the iterations of the loop.

You can use the output from a SELECT statement as the expression.

The FOR loop terminates when loop_var is equal to the values of each element in the expression list or range in succession, or when it encounters an EXIT FOR statement. An error is issued, however, if an assignment within the body of the FOR statement attempts to modify the value of loop_var.

The size of right_expr relative to left_expr determine whether the range is stepped through by positive or by negative increments:
  • The increments are positive if left_expr < right_expr.
  • The increments are negative if left_expr > right_expr.

If you specify no increment_expr, the default size of each step is 1, with a positive or negative sign determined by the rules above.