Fetching from a Collection Cursor

A Collection cursor allows you to access the individual elements of collection variables. To declare a Collection cursor, use the DECLARE statement and include the Collection-Derived Table segment in the SELECT statement that you associate with the cursor. After you open the collection cursor with the OPEN statement, the cursor allows you to access the elements of the collection variable.

To fetch elements, one at a time, from a Collection cursor, use the FETCH statement and the INTO clause. The FETCH statement identifies the Collection cursor that is associated with the collection variable. The INTO clause identifies the host variable that holds the element value that is fetched from the Collection cursor. The data type of the host variable in the INTO clause must match the element type of the collection.

Suppose you have a table called children with the following structure:

CREATE TABLE children
(
   age         SMALLINT,
   name         VARCHAR(30),
   fav_colors            SET(VARCHAR(20) NOT NULL),
)

The following code fragment shows how to fetch elements from the child_colors collection variable:

EXEC SQL BEGIN DECLARE SECTION;
   client collection child_colors;
   varchar one_favorite[21];
   char child_name[31] = "marybeth";
EXEC SQL END DECLARE SECTION;
EXEC SQL allocate collection :child_colors;
/* Get structure of fav_colors column for untyped 
 * child_colors collection variable */
EXEC SQL select fav_colors into :child_colors
   from children
   where name = :child_name;
/* Declare select cursor for child_colors collection
 * variable */
EXEC SQL declare colors_curs cursor for 
   select * from table(:child_colors);
EXEC SQL open colors_curs;
do
{
   EXEC SQL fetch colors_curs into :one_favorite;
   ...
} while (SQLCODE == 0)
EXEC SQL close colors_curs;
EXEC SQL free colors_curs;
EXEC SQL deallocate collection :child_colors;

After you fetch a collection element, you can modify the element with the UPDATE or DELETE statements. For more information, see the UPDATE and DELETE statements in this document. You can also insert new elements into the collection variable with an INSERT statement. For more information, see the INSERT statement.