Declare a select cursor for a collection variable
- The select cursor is an update cursor.
The DECLARE statement cannot include the FOR READ ONLY clause that specifies the read-only cursor mode.
- The select cursor must be a sequential cursor.
The DECLARE statement cannot specify the SCROLL or WITH HOLD cursor characteristics.
EXEC SQL BEGIN DECLARE SECTION;
client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;
;
EXEC SQL declare set_curs cursor for
select * from table(:a_set);
To select the element or elements from the collection variable, use the FETCH statement with the INTO clause.
If you want to modify the elements of the collection variable, declare the select cursor as an update cursor with the FOR UPDATE keywords. You can then use the WHERE CURRENT OF clause of the DELETE and UPDATE statements to delete or update elements of the collection.