FOREACH

The FOREACH statement applies a series of actions to each row of data that is returned from a query by a cursor.

cursor is the name of a previously declared SQL cursor.

statement is an SQL statement or other 4GL statement.

Use the FOREACH statement to retrieve and process rows selected by a query. The FOREACH statement is equivalent to using the OPEN, FETCH and CLOSE statements.

The FOREACH statement has these effects:

Opens the specified cursor

Fetches the rows selected

Closes the cursor

You must declare the cursor (by using the DECLARE statement) before the FOREACH statement can retrieve the rows. A compile-time error occurs unless the cursor was declared prior to this point in the source module. You can reference a sequential cursor, a SCROLL cursor, a cursor WITH HOLD, or FOR UPDATE, but FOREACH only processes rows in sequential order.

FOREACH does not have the equivalent of the USING clause that the OPEN statement of SQL supports. The FOREACH statement can only open cursors for SELECT statements that do not contain unknown parameters.

The FOREACH statement performs successive fetches until all rows specified by the SELECT statement are retrieved. Then the cursor is automatically closed. It is also closed if a WHENEVER NOT FOUND statement within the FOREACH loop detects a NOTFOUND condition (that is, status = 100).

Implicit FETCH statements that FOREACH executes with an Update cursor can support promotable locks.

The INTO Clause

The INTO clause specifies a variable or a comma-separated list of variables in which to store values from each row that is returned by the query:

array is the name of a program array.

first is the name of a member variable in which to store a value.

last is another member of record that was declared later than member.

record is the name of a variable of the RECORD data type.

• ariable is the name of a simple variable to store the retrieved value.

The FOREACH Statement Block

These statements are executed after each row of the active set is fetched:

statement is an SQL statement or other 4GL statement.

This block is sometimes called "the FOREACH loop." If the cursor returns no rows, then no statements in this loop are executed, and program control passes to the first statement that follows the END FOREACH keywords.

If the specified cursor is an Update cursor, the statement block can include statements to modify retrieved rows.

References

CONTINUE

FOR

WHENEVER

WHILE