FOR statement
The FOR statement executes a statement or group of statements for each row of a table.
Invocation
This statement
can be embedded in an:
- SQL procedure definition
- Compound SQL (compiled) statement
- Compound SQL (inlined) statement
Authorization
No privileges are required
to invoke the FOR statement. However, the authorization ID of the
statement must hold the necessary privileges to invoke the SQL statements
that are embedded in the FOR statement. For the authorization required
to use a cursor, see DECLARE CURSOR
.
Syntax
Notes:
- 1 This option can only be used in the context of an SQL procedure or a compound SQL (compiled) statement.
Description
-
label
- Specifies the label for the FOR statement. If the beginning label is specified, that label can be used in LEAVE and ITERATE statements. If the ending label is specified, it must be the same as the beginning label. for-loop-name
- Specifies a label for the implicit compound statement generated to implement the FOR statement. It follows the rules for the label of a compound statement except that it cannot be used with an ITERATE or LEAVE statement within the FOR statement. The for-loop-name is used to qualify the column names returned by the specified select-statement. cursor-name
- Names the cursor that is used to select rows from the result
table of the SELECT statement. If not specified, the database manager
generates a unique cursor name. For a description
of ASENSITIVE, INSENSITIVE, WITHOUT HOLD, or WITH HOLD, see
DECLARE CURSOR
.
select-statement
- Specifies the SELECT statement of the cursor. All columns in the
select list must have a name and there cannot be two columns with
the same name.
In a trigger, function, method, or compound SQL (inlined) statement, the select-statement must consist of only a fullselect with optional common table expressions.
SQL-procedure-statement
- Specifies one or more statements to be invoked for each row of
the table. SQL-procedure-statement is only
applicable when in the context of an SQL procedure or
within a compound SQL (compiled) statement. See SQL-procedure-statement in
Compound SQL (compiled)
statement.
SQL-function-statement
- Specifies one or more statements to be invoked for each row of the table. A searched-update, searched-delete, or INSERT operation on nicknames is not supported. SQL-function-statement is only applicable when in the context of an SQL function or SQL method.
Rules
- The select list must consist of unique column names and the objects specified in the select-statement must exist when the procedure is created, or the object must be created in a previous SQL procedure statement.
- The cursor specified in a for-statement cannot be referenced outside the for-statement and cannot be specified in an OPEN, FETCH, or CLOSE statement.
Example
In
the following example, the for-statement is used to iterate over the
entire
employee
table. For each row in the table,
the SQL variable fullname
is set to the last name
of the employee, followed by a comma, the first name, a blank space,
and the middle initial. Each value for fullname
is
inserted into table tnames
. BEGIN ATOMIC
DECLARE fullname CHAR(40);
FOR vl AS
SELECT firstnme, midinit, lastname FROM employee
DO
SET fullname = lastname CONCAT ','
CONCAT firstnme CONCAT ' ' CONCAT midinit;
INSERT INTO tnames VALUES (fullname);
END FOR;
END