FOREACH

Use the FOREACH statement to declare a direct cursor that can select and manipulate more than one row from a the result set of a query, or more than one element from a collection.

Syntax

Direct sequential cursors that the FOREACH statement of SPL can create are distinct from the dynamic cursors that the DECLARE statement of SQL can create in SPL routines. (For the syntax and usage of dynamic cursors in SPL routines, see Declaring a Dynamic Cursor in an SPL Routine.)

(explicit id frch003) frch003 (explicit id frch004) frch004 (explicit id frch005) frch005 FOREACH { [ { WITH HOLD | cursor [ WITH HOLD ] FOR | <Routine Call> [ INTO data_var ] } ] <SELECT ... INTO Statement>[] } <Statement Block>[] END FOREACH [ ; ]

Routine Call

EXECUTE { PROCEDURE { procedure | SPL_var | function } | FUNCTION { SPL_var | function } }
( [ <Argument>[] ] )
Element Description Restrictions Syntax
cursor Identifier that you declare here as the name of this direct cursor Must be unique among names of cursors, prepared statements, and SPL variables in the routine Identifier
data_var SPL variable in the calling routine that receives the returned values Data type of data_var must be appropriate for returned value Identifier
function, procedure SPL function or procedure to execute Function or procedure must exist Database Object Name
SPL_var SPL variable that contains the name of a routine to execute Must be of type CHAR, VARCHAR, NCHAR, or NVARCHAR Identifier

Usage

To execute a FOREACH statement, the database server takes these actions:
  1. It declares and implicitly opens a direct sequential 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 direct sequential 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 called 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 the COMMIT or ROLLBACK statement.
  • 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.
Note:
The FOREACH statement cannot define a SCROLL cursor. Each FOREACH cursor is a sequential cursor, which can fetch only the next row in sequence from the active set. A cursor that FOREACH defines can read through the active set only once each time it is opened.
1 See Using a SELECT ... INTO Statement
2 See Arguments
3 See Statement Block