Updating a Collection Element

You can update a collection element by accessing the collection within a cursor just as you select or delete an individual element.

If you want to update the collection SET{100, 200, 300, 500} to change the value 500 to 400, retrieve the SET from the database into a collection variable and then declare a cursor to move through the elements in the SET, as Figure 439 shows.

DEFINE s SET(INTEGER NOT NULL);
DEFINE n INTEGER;

SELECT numbers INTO s FROM orders
   WHERE order_num = 10;

FOREACH cursor1 FOR
   SELECT * INTO n FROM TABLE(s)
   IF ( n == 500 ) THEN
      UPDATE TABLE(s)(x)
         SET x = 400 WHERE CURRENT OF cursor1;
      EXIT FOREACH;
   ELSE
      CONTINUE FOREACH;
   END IF;
END FOREACH

The UPDATE statement uses the collection variable s as a collection-derived table. To specify a collection-derived table, use the TABLE keyword. The value (x) that follows (s) in the UPDATE statement is a derived column, a column name you supply because the SET clause requires it, even though the collection-derived table does not have columns.

Think of the collection-derived table as having one row and looking something like the following example:

100      200      300      500

In this example, x is a fictitious column name for the "column" that contains the value 500. You only specify a derived column if you are updating a collection of built-in, opaque, distinct, or collection type elements. If you are updating a collection of row types, use a field name instead of a derived column, as Updating a Collection of Row Types describes.

Updating 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 Figure 440 uses statements that are similar to the ones that Figure 439 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 410 defines the manager table.

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.