DB2 10.5 for Linux, UNIX, and Windows

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

The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

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


>>-+--------+--FOR--for-loop-name--AS--------------------------->
   '-label:-'                           

>--+-----------------------------------------------------------------+-->
   |              .-ASENSITIVE--.          .-WITHOUT HOLD-.      (1) |   
   '-cursor-name--+-------------+--CURSOR--+--------------+--FOR-----'   
                  '-INSENSITIVE-'          '-WITH HOLD----'              

>--select-statement--DO--| SQL-routine-statement |--END FOR----->

>--+-------+---------------------------------------------------><
   '-label-'   

SQL-routine-statement

     .----------------------------.        
     V                            |        
|--+---SQL-procedure-statement--;-+----+------------------------|
   | .-------------------------------. |   
   | V                               | |   
   '---| SQL-function-statement |--;-+-'   

SQL-function-statement

|--+-CALL----------------------------------------------+--------|
   +-FOR-----------------------------------------------+   
   +-+-----------------------------------+--fullselect-+   
   | |       .-,-----------------------. |             |   
   | |       V                         | |             |   
   | '-WITH----common-table-expression-+-'             |   
   +-GET DIAGNOSTICS-----------------------------------+   
   +-IF------------------------------------------------+   
   +-INSERT--------------------------------------------+   
   +-ITERATE-------------------------------------------+   
   +-LEAVE---------------------------------------------+   
   +-MERGE---------------------------------------------+   
   +-searched-delete-----------------------------------+   
   +-searched-update-----------------------------------+   
   +-SET Variable--------------------------------------+   
   +-SIGNAL--------------------------------------------+   
   '-WHILE---------------------------------------------'

Notes:

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