FOR statement
The FOR statement executes a statement for each row of a table. An implicit compound statement is generated to implement the FOR statement.
Syntax
>>-+--------+--FOR--+-------------------+-----------------------> '-label:-' '-for-loop-name--AS-' >--+--------------------------------------------+---------------> | .-WITHOUT HOLD-. | '-cursor-name--CURSOR--+--------------+--FOR-' '-WITH HOLD----' .----------------------------. V | >--select-statement--DO----SQL-procedure-statement--;-+---------> >--END FOR--+-------+------------------------------------------>< '-label-'
Description
- label
- Specifies the label for the FOR statement. If the ending label is specified, it must be the same as the beginning label. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to labels.
- for-loop-name
- Specifies the label for the implicit compound statement that is
generated to implement the FOR statement. for-loop-name follows
the rules for the label of a compound statement except that it cannot
be used with an ITERATE, GOTO, or LEAVE statement within the FOR statement. for-loop-name must
not be the same as any label within the same scope.
for-loop-name can be used to qualify generated SQL variables that correspond to the columns that are returned by select-statement.
- cursor-name
- Names a cursor that is generated to select rows from the result
table of select-statement. If cursor-name is
not specified, a unique cursor name is generated.
cursor-name cannot be referenced outside of the FOR statement and cannot be specified on an OPEN, FETCH, or CLOSE statement.
- WITH HOLD or WITHOUT HOLD
- Specifies whether the cursor should be prevented from being closed
as a consequence of a commit operation.
- WITHOUT HOLD
- Specifies that the cursor is not prevented from being closed as a consequence of a commit operation. evaWITHOUT HOLD is the default.
- WITH HOLD
- Specifies that the cursor should not be closed as a consequence of a commit operation. A cursor that is declared using the WITH HOLD clause is implicitly closed at commit time only if the connection that is associated with the cursor is ended during the commit operation. For more information, see DECLARE CURSOR.
- select-statement
- Specifies the select statement of the cursor. Each expression in the SELECT list must have a
name. If an expression is not a simple column name, the AS clause must be used to name the
expression. If the AS clause is specified, that name is used for the variable and must be
unique.
select-statement must not include a values-clause.
- SQL-procedure-statement
- Specifies the SQL statements to be executed for each row of the table. The SQL statements must not include an OPEN, FETCH, or CLOSE statement that specifies the cursor name of the FOR statement.
Notes
FOR statement rules: The FOR statement executes one or multiple statements for each row in the result table of the cursor. The cursor is defined by specifying a SELECT list that describes the columns and rows selected. The statements within the FOR statement are executed for each row selected.
The SELECT list must consist of unique column names and the objects referenced in the select-statement must exist when the routine is created. All objects referenced in the select-statement must exist at the current server.
The cursor specified in a FOR statement cannot be referenced outside the FOR statement and cannot be specified on an OPEN, FETCH, or CLOSE statement.
Handler warning: Handlers can be used to handle errors that might occur on the open of the cursor or fetch of a row using the cursor in the FOR statement. Handlers defined to handle these open or fetch conditions should not be CONTINUE handlers as they might cause the FOR statement to loop indefinitely.
Examples
In the following example, the FOR statement is used to specify a cursor that selects three columns from the employee table. For every row selected, SQL variable fullname is set to the last name followed by a comma, the first name, a blank, and the middle initial. Each value for fullname is inserted into table TNAMES.
BEGIN
DECLARE fullname CHAR(40);
FOR v1 AS
c1 CURSOR FOR
SELECT firstname, midinit, lastname FROM employee
DO
SET fullname =
lastname CONCAT ', '
CONCAT firstname
CONCAT ' '
CONCAT midinit;
INSERT INTO TNAMES VALUES ( fullname );
END FOR;
END;