Informix Guide to SQL: Syntax SPL Statements

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 establish a finite number of iterations for a loop.

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

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.

The database server generates an error if an assignment within the body of the FOR statement attempts to modify the value of loop_var.

The TO keyword implies a range operator. The range is defined by left_expression and right_expression, and the STEP increment_expr option implicitly sets the number of increments. If you use the TO keyword, loop_var must be an INT or SMALLINT data type. The following example shows two equivalent FOR statements. Each uses the TO keyword to define a range. The first statement uses the IN keyword, and the second statement uses an equal sign (=). Each statement causes the loop to execute five times.

FOR index_var IN (12 TO 21 STEP 2)

	-- statement block

END FOR



FOR index_var = 12 TO 21 STEP 2

	-- statement block

END FOR

If you omit the STEP option, the database server gives increment_expr the value of -1 if right_expression is less than left_expression, or +1 if right_expression is more than left_expression. If increment_expr is specified, it must be negative if right_expression is less than left_expression, or positive if right expression is more than left_expression. The two statements in the following example are equivalent. In the first statement, the STEP increment is explicit. In the second statement, the STEP increment is implicitly 1.

FOR index IN (12 TO 21 STEP 1)

	-- statement block

END FOR



FOR index = 12 TO 21

	-- statement block

END FOR

The database server initializes the value of loop_var to the value of left_expression. In subsequent iterations, the server adds increment_expr to the value of loop_var and checks increment_expr to determine whether the value of loop_var is still between left_expression and right_expression. If so, the next iteration occurs. Otherwise, an exit from the loop takes place. Or, if you specify another range, the variable takes on the value of the first element in the next range.

The following example shows a statement that traverses a loop forward and backward and uses different increment values for each direction:

FOR index_var IN (15 to 21 STEP 2, 21 to 15 STEP -3)

	-- statement body

END FOR

The database server initializes the value of loop_var to the value of the first expression specified. In subsequent iterations, loop_var takes on the value of the next expression. When the server has evaluated the last expression in the list and used it, the loop stops.

The expressions in the IN list do not have to be numeric values, as long as you do not use range operators in the IN list. The following example uses a character expression list:

FOR c IN ('hello', (SELECT name FROM t), 'world', v1, v2)

	INSERT INTO t VALUES (c);

	END FOR

The following FOR statement shows the use of a numeric expression list:

FOR index IN (15,16,17,18,19,20,21)

	-- statement block

END FOR

If loop_var is an INT or SMALLINT value, you can mix ranges and expression lists in the same FOR statement. The following example shows a mixture that uses an integer variable. Values in the expression list include the value that is returned from a SELECT statement, a sum of an integer variable and a constant, the values that are returned from an SPL function named p_get_int, and integer constants.

CREATE PROCEDURE for_ex ()

	DEFINE i, j INT;

	LET j = 10;

	FOR i IN (1 TO 20, (SELECT c1 FROM tab WHERE id = 1),

	j+20 to j-20, p_get_int(99),98,90 to 80 step -2)

		INSERT INTO tab VALUES (i);

	END FOR

END PROCEDURE