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