Declare an insert cursor for a collection variable
An insert cursor allows you to insert one or more elements in the collection.
- It must be a sequential cursor; the DECLARE statement cannot specify the SCROLL keyword.
- It cannot be a hold cursor; the DECLARE statement cannot specify the WITH HOLD cursor characteristic.
If you need to use input parameters, you must prepare the INSERT statement and specify the prepared statement identifier in the DECLARE statement.
You can use input parameters to specify the values in the VALUES clause of the INSERT statement.
EXEC SQL prepare ins_stmt from
'insert into table values';
EXEC SQL declare list_curs cursor for ins_stmt;
EXEC SQL open list_curs using :a_list;
- The name of the collection variable in the collection-derived
table clause The following DECLARE statement declares the list_curs2 insert cursor for the a_list variable:
EXEC SQL prepare ins_stmt2 from 'insert into table values'; EXEC SQL declare list_curs2 cursor for ins_stmt2; EXEC SQL open list_curs2 using :a_list; while (1) { EXEC SQL put list_curs2 from :an_element; ; }
The USING clause of the OPEN statement specifies the name of the collection variable. You can then use the PUT statement to specify the values to insert.
After you declare the insert cursor, you can open it with the OPEN statement. You can insert elements into the collection variable once the associated insert cursor is open.