The FOREACH loop to define cursors

A FOREACH loop begins with the FOREACH keyword and ends with END FOREACH. Between FOREACH and END FOREACH, you can declare a cursor or use EXECUTE PROCEDURE or EXECUTE FUNCTION. The two examples in the following figure show the structure of FOREACH loops.

Figure 1. Structure of FOREACH loops.

FOREACH cursor FOR
   SELECT column INTO variable FROM table 
. . .
END FOREACH;

FOREACH 
   EXECUTE FUNCTION name() INTO variable;
END FOREACH;

The following figure creates a routine that uses a FOREACH loop to operate on the employee table.

Figure 2. A FOREACH loop that operates on the employee table.

CREATE_PROCEDURE increase_by_pct( pct INTEGER )
   DEFINE s INTEGER;

   FOREACH sal_cursor FOR
      SELECT salary INTO s FROM employee 
         WHERE salary > 35000
      LET s = s + s * ( pct/100 );
      UPDATE employee SET salary = s
         WHERE CURRENT OF sal_cursor;
   END FOREACH;

END PROCEDURE;

The routine in preceding figure performs these tasks within the FOREACH loop:

Declares a cursor
Selects one salary value at a time from employee
Increases the salary by a percentage
Updates employee with the new salary
Fetches the next salary value

The SELECT statement is placed within a cursor because it returns all the salaries in the table greater than 35000.

The WHERE CURRENT OF clause in the UPDATE statement updates only the row on which the cursor is currently positioned, and sets an update cursor on the current row. An update cursor places an update lock on the row so that no other user can update the row until your update occurs.

An SPL routine will set an update cursor automatically if an UPDATE or DELETE statement within the FOREACH loop uses the WHERE CURRENT OF clause. If you use WHERE CURRENT OF, you must explicitly reference the cursor within the FOREACH statement. If you are using an update cursor, you can add a BEGIN WORK statement before the FOREACH statement and a COMMIT WORK statement after END FOREACH, as the following figure shows.

Figure 3. Set an update cursor automatically.

BEGIN WORK;
   FOREACH sal_cursor FOR
      SELECT salary INTO s FROM employee WHERE salary > 35000;
      LET s = s + s * ( pct/100 );
      UPDATE employee SET salary = s WHERE CURRENT OF sal_cursor
   END FOREACH;
COMMIT WORK;

For each iteration of the FOREACH loop in the preceding figure, a new lock is acquired (if you use row level locking). The COMMIT WORK statement releases all of the locks (and commits all of the updated rows as a single transaction) after the last iteration of the FOREACH loop.

To commit an updated row after each iteration of the loop, you must open the cursor WITH HOLD, and include the BEGIN WORK and COMMIT WORK statements within the FOREACH loop, as in the following SPL routine.

Figure 4. Committing an updated row after each iteration of the loop.

CREATE PROCEDURE serial_update();
    DEFINE p_col2 INT;
    DEFINE i INT;
    LET i = 1;
    FOREACH cur_su WITH HOLD FOR
        SELECT col2 INTO p_col2 FROM customer WHERE 1=1
        BEGIN WORK;
            UPDATE customer SET customer_num = p_col2 WHERE CURRENT OF cur_su;
        COMMIT WORK;
        LET i = i + 1;
    END FOREACH;
END PROCEDURE;

SPL routine serial_update() commits each row as a separate transaction.