Update a collection element
You can update a collection element by accessing the collection within a cursor just as you select or delete an individual element.
SET{100, 200, 300, 500}
to
change the value 500
to 400
, retrieve
the SET from the database into a collection variable and then declare
a cursor to move through the elements in the SET, as the following
figure shows.DEFINE s SET(INTEGER NOT NULL);
DEFINE n INTEGER;
SELECT numbers INTO s FROM orders
WHERE order_num = 10;
FOREACH cursor1 FOR
SELECT * INTO n FROM TABLE(s)
IF ( n == 500 ) THEN
UPDATE TABLE(s)(x)
SET x = 400 WHERE CURRENT OF cursor1;
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH
The UPDATE statement uses the collection variable s as
a collection-derived table. To specify a collection-derived table,
use the TABLE keyword. The value (x)
that follows (s)
in
the UPDATE statement is a derived column, a column name
you supply because the SET clause requires it, even though the collection-derived
table does not have columns.
100 200 300 500
In this
example, x
is a fictitious column name for the “column”
that contains the value 500
. You only specify a derived
column if you are updating a collection of built-in, opaque, distinct,
or collection type elements. If you are updating a collection of row
types, use a field name instead of a derived column, as Update a collection of row types describes.