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 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.