Update a collection of row types

To update a collection of ROW types, you can take these steps:

Declare a collection variable whose field data types match those of the ROW types in the collection.
Set the individual fields of the collection variable to the correct data values for the ROW type.
For each ROW type, update the entire row of the collection derived table using the collection variable.

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

Figure 1. LIST of ROW types definition.

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

To access the ROW types in the LIST, declare a cursor and select the LIST into a collection variable. After you retrieve each ROW type value in the projects column, however, you cannot update the pro_name or pro_members fields individually. Instead, for each ROW value that needs to be updated in the collection, you must replace the entire ROW with values from a collection variable that include the new field values, as the following figure shows.

Figure 2. Access the ROW types in the LIST.

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
         LET r.p_name = pro;
         UPDATE TABLE(p)(x) SET x = r
            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 SELECT INTO statement. The UPDATE statement nested in the FOREACH loop of the previous figure sets the pro_name field of the row type to the value supplied in the variable pro.

Tip: 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 Update a collection element explains.