>>-FOREACH------------------------------------------------------>
 
                                                                                 (1)
>----+------------------------------------------+--| SELECT ... INTO Statement |--------->
     +-WITH HOLD--------------------------------+
     +-cursor--+-----------+--FOR---------------+
     |         '-WITH HOLD-'                    |
     '-| Routine Call |--+--------------------+-'
                         |       .-,--------. |
                         |       V          | |
                         '-INTO----data_var-+-'
 
                       (2)
>--| Statement Block |-------END FOREACH--+---+----------------><
                                          '-;-'
 
Routine Call:
 
|--EXECUTE--+-PROCEDURE--+-procedure-+------+------------------->
            |            +-SPL_var---+      |
            |            '-function--'      |
            |  (3)                          |
            '--------FUNCTION--+-SPL_var--+-'
                               '-function-'
 
>--(--+-----------------------+--)------------------------------|
      | .-,-----------------. |
      | V              (4)  | |
      '---| Argument |------+-'
 

1. See Using a SELECT ... INTO Statement
2. See Statement Block
3. Dynamic Server only
4. See Arguments

A FOREACH loop is the procedural equivalent of using a cursor. To execute a FOREACH statement, the database server takes these actions:

1. It declares and implicitly opens a cursor.
2. It obtains the first row from the query contained within the FOREACH loop, or else the first set of values from the called routine.
3. It assigns to each variable in the variable list the value of the corresponding value from the active set that the SELECT statement or the called routine creates.
4. It executes the statement block.
5. It fetches the next row from the SELECT statement or called routine on each iteration, and it repeats steps 3 and 4.
6. It terminates the loop when it finds no more rows that satisfy the SELECT statement or called routine. It closes the implicit cursor when the loop terminates.

Because the statement block can contain additional FOREACH statements, cursors can be nested. No limit exists on the number of nested cursors.

An SPL routine that returns more than one row, collection element, or set of values is called a cursor function. An SPL routine that returns only one row or value is a noncursor function.

This SPL procedure illustrates FOREACH statements with a SELECT ... INTO clause, with an explicitly named cursor, and with a procedure call:

CREATE PROCEDURE foreach_ex()
   DEFINE i, j INT;
   FOREACH SELECT c1 INTO i FROM tab ORDER BY 1
      INSERT INTO tab2 VALUES (i);
   END FOREACH
   FOREACH cur1 FOR SELECT c2, c3 INTO i, j FROM tab
      IF j > 100 THEN
         DELETE FROM tab WHERE CURRENT OF cur1;
         CONTINUE FOREACH;
      END IF
      UPDATE tab SET c2 = c2 + 10 WHERE CURRENT OF cur1;
   END FOREACH
   FOREACH EXECUTE PROCEDURE bar(10,20) INTO i
      INSERT INTO tab2 VALUES (i);
   END FOREACH
END PROCEDURE; -- foreach_ex

A select cursor is closed when any of the following situations occur:

• The cursor returns no further rows.
• The cursor is a select cursor without a HOLD specification, and a transaction completes using COMMIT or ROLLBACK statements.
• An EXIT statement executes, which transfers control out of the FOREACH statement.
• An exception occurs that is not trapped inside the body of the FOREACH statement. (See ON EXCEPTION.)
• A cursor in the calling routine that is executing this cursor routine (within a FOREACH loop) closes for any reason.