Updating the Entire Collection

If you want to update all the elements of a collection to the same value, or if the collection contains only one element, you do not need to use a cursor. The statements in Figure 441 show how you can retrieve the collection into a collection variable and then update it with one statement.

Figure 441.

DEFINE s SET (INTEGER NOT NULL);

SELECT numbers INTO s FROM orders
   WHERE order_num = 10;

UPDATE TABLE(s)(x) SET x = 0;

UPDATE orders SET numbers = s
   WHERE order_num = 10;

The first UPDATE statement in this example uses a derived column named x with the collection-derived table s and gives all the elements in the collection the value 0. The second UPDATE statement stores the new collection in the database.

Updating a Collection of Row Types

To update a collection of ROW types, you can use the name of the field you want to update in the UPDATE statement, instead of a derived column name.

The manager table in Figure 410 has a column named projects that contains a LIST of ROW types with the definition that Figure 442 shows.

Figure 442.

projects   LIST( ROW( pro_name VARCHAR(15),
           pro_members SET(VARCHAR(20) NOT NULL) ) NOT NULL)

To access the individual ROW types in the LIST, declare a cursor and select the LIST into a collection variable. After you retrieve an individual ROW type, you can update the pro_name or pro_members fields by supplying a field name and the new data, as Figure 443 shows.

Figure 443.

CREATE PROCEDURE update_pro( mgr VARCHAR(30),
   pro VARCHAR(15) )

   DEFINE p LIST(ROW(a VARCHAR(15), b SET(VARCHAR(20) 
          NOT NULL) ) NOT NULL);
   DEFINE r ROW(p_name VARCHAR(15), p_member SET(VARCHAR(20) NOT NULL) );
   LET r = ROW("project", "SET{'member'}");

SELECT projects INTO p FROM manager
      WHERE mgr_name = mgr;

   FOREACH cursor1 FOR
      SELECT * INTO r FROM TABLE(p)
      IF (r.p_name == 'Zephyr') THEN
         UPDATE TABLE(p) SET pro_name = pro
            WHERE CURRENT OF cursor1;
         EXIT FOREACH;
      END IF;
   END FOREACH

   UPDATE manager SET projects = p
      WHERE mgr_name = mgr;

END PROCEDURE;

Before you can use a row-type variable in an SPL program, you must initialize the row variable with a LET statement or a SELECTINTO statement. The UPDATE statement nested in the FOREACH loop of Figure 443 sets the pro_name field of the row type to the value supplied in the variable pro.

To update a value in a SET in the pro_members field of the ROW type, declare a cursor and use an UPDATE statement with a derived column, as Updating a Collection Element explains.

Updating a Nested Collection

If you want to update a collection of collections, you must declare a cursor to access the outer collection and then declare a nested cursor to access the inner collection.

For example, suppose that the manager table has an additional column, scores, which contains a LIST whose element type is a MULTISET of integers, as Figure 444 shows.

Figure 444.

scores         LIST(MULTISET(INT NOT NULL) NOT NULL)

To update a value in the MULTISET, declare a cursor that moves through each value in the LIST and a nested cursor that moves through each value in the MULTISET, as Figure 445 shows.

Figure 445.

CREATE FUNCTION check_scores ( mgr VARCHAR(30) )
   SPECIFIC NAME nested;
   RETURNING INT;

   DEFINE l LIST( MULTISET( INT NOT NULL ) NOT NULL );
   DEFINE m MULTISET( INT NOT NULL );
   DEFINE n INT;
   DEFINE c INT;

   SELECT scores INTO l FROM manager
      WHERE mgr_name = mgr;

   FOREACH list_cursor FOR
      SELECT * FROM TABLE(l) INTO m;

      FOREACH set_cursor FOR
         SELECT * FROM TABLE(m) INTO n;
         IF (n == 0) THEN
            DELETE FROM TABLE(m)
               WHERE CURRENT OF set_cursor;
         ENDIF;
      END FOREACH;
      LET c = CARDINALITY(m);
      RETURN c WITH RESUME;
   END FOREACH

END FUNCTION
   WITH LISTING IN '/tmp/nested.out';

The SPL function in Figure 445 selects each MULTISET in the scores column into l, and then each value in the MULTISET into m. If a value in m is 0, the function deletes it from the MULTISET. After the values of 0 are deleted, the function counts the remaining elements in each MULTISET and returns an integer.

Because this function returns a value for each MULTISET in the LIST, you must use a cursor to enclose the EXECUTE FUNCTION statement when you execute the function.