| SQL support / SQL insert cursors | |
The DECLARE with an INSERT instruction defines an insert cursor.
DECLARE cid CURSOR [WITH HOLD] FOR { insert-statement | sid }
Use the DECLARE instruction with an INSERT instruction to define a new insert cursor in the current database session.
The INSERT statement is parsed, validated and the execution plan is created.
DECLARE must precede any other statement that refers to the cursor during program execution.
The scope of reference of the cid cursor identifier is local to the module where it is declared.
The static insert-statement statement can include a list of variables in the VALUES clause. These variables are automatically read by the PUT statement; you do not have to provide the list of variables in that statement. As an alternative, use the ? (question mark) SQL parameter placeholder in the VALUE clause to bind program variables provided in the FROM clause of the PUT instruction.
When declaring a cursor with a prepared sid statement, the statement can include ? (question mark) placeholders for SQL parameters. In this case you must provide a list of variables in the FROM clause of the PUT statement.
Use the WITH HOLD option to declare cursors that have uninterrupted inserts across multiple transactions.
Resources allocated by the DECLARE can be released later by the FREE instruction.
The number of declared cursors in a single program is limited by the database server and the available memory. Make sure that you free the resources when you no longer need the declared insert cursor.
The identifier of a cursor that was declared in one module cannot be referenced from another module.