Using 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 Figure 404 show the structure of FOREACH loops.

Figure 404.

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

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

Figure 405 creates a routine that uses a FOREACH loop to operate on the employee table.

Figure 405.

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 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 Figure 406 shows.

Figure 406.

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 Figure 406, 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 407.

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 col2 = p_col2 WHERE CURRENT OF cur_su
         COMMIT WORK;
      LET i = i+1
   END FOREACH
END PROCEDURE;