Insert values into a nested collection

You do not need to specify the constructor keyword for the actual collection type. The following typed collection host variable can access the setlist_col column of the tab_setlist table:

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(list(integer not null) not null)
      nested_coll;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection nested_coll;

The following code fragment inserts literal values into the nested_coll collection variable and then updates the setlist_col column (which Sample column with nested collection defines):

EXEC SQL insert into table(:nested_coll) 
   values (list{1,2,3,4});
EXEC SQL insert into tab_setlist values (:nested_coll);

To insert non-literal values into a nested collection, you must declare a collection variable and an insert cursor for each level of collection. For example, the following code fragment uses the nested collection variable, nested_coll, to insert new elements into the nested-collection column, setlist_col.

EXEC SQL BEGIN DECLARE SECTION;
   client collection set(list(integer not null) not null) nested_coll;
   client collection list(integer not null) list_coll;
   int an_element;
EXEC SQL END DECLARE SECTION;
int num_elements = 1;
int keep_adding = 1;
int an_int;
⋮

EXEC SQL allocate collection :nested_coll;
EXEC SQL allocate collection :list_coll;

/* Step 1: declare the insert cursor on the SET collection variable */
EXEC SQL declare set_curs cursor for 
   insert into table(:nested_coll) values;

/* Step 2: declare the insert cursor on the LIST collection variable */
EXEC SQL declare list_curs cursor for
   insert into table(:list_coll) values;

/* Step 3: open the SET cursor */
EXEC SQL open set_curs;

while (keep_adding)
   {

/* Step 4: open the LIST cursor */
    SQL open list_curs;

/* Step 5: put the LIST elements into the LIST insert cursor */
   for (an_int=0; an_int<10; an_int++)
      {
      an_element = an_int * num_elements;
      EXEC SQL put list_curs from :an_element;
      
⋮

      };
   EXEC SQL close list_curs;
   num_elements++;

/* Step 6: put the SET elements into the SET insert cursor */
   EXEC SQL put set_curs from :list_coll;
   if (done_adding(num_elements)
      {
      EXEC SQL close set_curs;
      keep_adding = 0;
      }
   };
EXEC SQL free set_curs;
EXEC SQL free list_curs;

/* Step 7: insert the nested SET column with the host variable */
EXEC SQL insert into tab_setlist values (:nested_coll);

EXEC SQL deallocate collection :nested_coll;
EXEC SQL deallocate collection :list_coll;