DECLARE (insert cursor)
The DECLARE
with an INSERT
instruction defines an
insert cursor.
Syntax
DECLARE cid CURSOR [WITH HOLD] FOR { insert-statement | sid }
- cid is the identifier of the insert cursor.
- insert-statement is an
INSERT
statement defined in static SQL. - sid is the identifier of a prepared
INSERT
statement.
Usage
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.
After declaring the insert cursor, it must be opened with the OPEN
instruction.
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.