Fetch elements from the select cursor

To fetch elements, one at a time, from a collection variable, use the FETCH statement and the INTO clause.

The FETCH statement identifies the select cursor that is associated with the collection variable. The INTO clause identifies the host variable for the element value that is fetched from the collection variable. The data type of the host variable in the INTO clause must be compatible with the element type of the collection.

The following figure contains a code fragment that selects all elements from the set_col column (see Sample tables with collection columns) into the typed collection host variable called a_set then fetches these elements, one at a time, from the a_set collection variable.

Figure 1: Selection of many elements from a collection host variable

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) a_set;
   int an_element, set_size;
EXEC SQL END DECLARE SECTION;
int an_int
⋮

EXEC SQL allocate collection :a_set;
EXEC SQL select set_col, cardinality(set_col)
   into :a_set from tab_set 
   from tab_set where id_col = 3;

/* Step 1: declare the select cursor on the host variable */
EXEC SQL declare set_curs cursor for 
   select * from table(:a_set);
EXEC SQL open set_curs;

/* Step 2: fetch the SET elements from the select cursor */
for (an_int=0; an_int<set_size; an_int++)
{
   EXEC SQL fetch set_curs into :an_element;
   
⋮

};
EXEC SQL close set_curs;

/* Step 3: update the SET column with the host variable */
EXEC SQL update tab_list SET set_col = :a_set
   where id_col = 3

EXEC SQL deallocate collection :a_set;
EXEC SQL free set_curs;