FOR EACH ROW

Syntax

FOR EACH ROW variable_name in database_name.table_name
 [ WHERE condition ]
BEGIN
   action_command_list;
END;

In this statement, the variable name is declared implicitly as a row reference. Therefore, you do not need to declare the variable at the start of the procedure. This means that any changes made to the columns referenced by the variable directly affect the referenced rows in the ObjectServer. When the END is reached, the implicitly-declared variable is discarded and cannot be used elsewhere in the procedure.

Only base tables (not views) can be updated in the FOR EACH ROW loop. You cannot insert into the table being processed within the FOR EACH ROW loop.

If an error is encountered while the FOR EACH ROW loop is scanning for rows, scanning is stopped. The exception to this behavior is if an error is encountered from an ALTER SYSTEM DROP CONNECTION command: that is, if the client has disconnected. In this case, the ALTER SYSTEM DROP CONNECTION command logs an error message and the FOR EACH ROW command continues scanning.

If you include a WHERE clause, only rows meeting the criteria that are specified in the condition are returned.

A BREAK command exits from the current loop, and the next statement in the procedure starts to run.

A CANCEL command stops the running of a procedure.

Example

The following example increases the severity of all alerts in the alerts.status table that have a severity of 3 to a severity of 4.

FOR EACH ROW alert_row in alerts.status WHERE alert_row.Severity=3
 BEGIN
   SET alert_row.Severity = 4;
END;

When this statement runs, the ObjectServer reads each row of the alerts.status table and tests to see if the value in the Severity column is 3. For each row that matches this condition, the statements within the BEGIN and END are run, until all the rows are processed.