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 Define the manager table. 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.