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.