Update a collection with a variable

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 Figure 1 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. Figure 2 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.