The FOR Clause

By default, the entire array is processed by an SQL statement but you can use the optional FOR clause to limit the number of array elements processed to just those that you want. This is especially useful in UPDATE, INSERT and DELETE statements where you may not want to use the entire array.

The FOR clause must use an integer host variable, for example:

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 AUTH-REC-TABLES
    05 Auth-id       OCCURS 25 TIMES PIC X(12).
    05 Auth-Lname    OCCURS 25 TIMES PIC X(40).
 01 maxitems         PIC S9(4) COMP-5 VALUE 10.
 EXEC SQL
     END DECLARE SECTION
 END-EXEC.
   . . .
     EXEC SQL
         CONNECT USERID 'user' IDENTIFIED BY 'pwd' 
                 USING 'db_alias'
     END-EXEC
     EXEC SQL
         FOR :maxitems
          UPDATE   authors
           SET   au_lname = :Auth_Lname
           WHERE au_id  = :Auth_id
     END-EXEC
     display sqlerrd(3)

In this example, 10 rows (the value of :maxitems) are modified by the UPDATE statement.

The number of array elements processed is determined by comparing the dimension of the host array with the FOR clause variable. The lesser value is used.

If the value of the FOR clause variable is less than or equal to zero, no rows are processed.