Update 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 the following figure shows.

Figure 1. Update a collection of collections.

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 the following figure shows.

Figure 2. Update a value in the MULTISET.

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

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