Declare a select cursor for a collection variable

To declare a select cursor for a collection variable, include the collection-derived table clause with the SELECT statement that you associate with the cursor. The DECLARE for this select cursor has the following restrictions:

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.

When you declare a select cursor for a collection variable, the collection-derived table clause of the SELECT statement must contain the name of the collection variable. For example, the following DECLARE statement declares a select cursor for the collection variable, a_set:

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.