SQL Reference

FOR Statement

The FOR statement executes a statement or group of statements for each row of a table.

Syntax

>>-+---------+---FOR--for-loop-name--AS------------------------->
   '-label:--'
 
>-----+--------------------------+--select-statement---DO------->
      '-cursor-name--CURSOR FOR--'
 
      .-------------------------------.
      V                               |
>--------SQL-procedure-statement--;---+--END FOR----+--------+-><
                                                    '-label--'
 

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 and 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 from the SELECT statement. If not specified, DB2 generates a unique cursor name.

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.

SQL-procedure-statement

Specifies a statement (or statements) to be invoked for each row of the table.

Rules

• The select list must consist of unique column names and the table specified in the select list must exist when the procedure is created, or it must be a table 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.

Examples

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
        DECLARE fullname CHAR(40);
        FOR vl AS
           SELECT firstnme, midinit, lastname FROM employee
              DO
              SET fullname = lastname || ',' || firstnme ||' ' || midinit;
              INSERT INTO tnames VALUE (fullname);
        END FOR
     END