You can also update a collection with the value stored
in a variable instead of a literal value.
The
SPL procedure in the following figure uses statements that are similar
to the ones that Update the collection element. shows,
except that this procedure updates the SET in the direct_reports column
of the manager table with a variable, rather than with a literal
value. Define the manager table. defines
the manager table.Figure 1: Update a collection with a variable.
CREATE PROCEDURE new_report(mgr VARCHAR(30),
old VARCHAR(30), new VARCHAR(30) )
DEFINE s SET (VARCHAR(30) NOT NULL);
DEFINE n VARCHAR(30);
SELECT direct_reports INTO s FROM manager
WHERE mgr_name = mgr;
FOREACH cursor1 FOR
SELECT * INTO n FROM TABLE(s)
IF ( n == old ) THEN
UPDATE TABLE(s)(x)
SET x = new WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH
UPDATE manager SET mgr_name = s
WHERE mgr_name = mgr;
END PROCEDURE;
The UPDATE statement nested in the FOREACH loop uses
the collection- derived table s and the derived column x.
If the current value of n is the same as old, the UPDATE
statement changes it to the value of new. The second UPDATE
statement stores the new collection in the manager table.