Declaring a Dynamic Cursor in an SPL Routine

Use the DECLARE statement in an SPL routine to declare the name of a dynamic cursor, and to associate that cursor with the statement identifier of a prepared statement that the PREPARE statement has declared in the same SPL routine.

Dynamic cursors that the DECLARE statement of SQL can create in SPL routines are distinct from the direct sequential cursors that the FOREACH statement of SPL can create in SPL routines. (For the syntax and usage of direct sequential cursors, see FOREACH.)

Syntax

The syntax of the DECLARE statement in SPL routines is a subset of the syntax that DECLARE supports in routines.

Notes:

¹ HCL OneDB™ extension


Element	Description	Restrictions	Syntax
cursor_id	Name declared here for a new dynamic cursor	Must be unique among names of cursors, prepared statements, and SPL variables in the routine	Identifier
statement_id	Identifier of a single prepared SQL statement	Must have been declared in a PREPARE statement of the same SPL routine	Identifier

Usage

In UDRs written in the SPL language, the statement_id associated with the cursor must have been declared earlier in the same UDR by a PREPARE statement from the text of a single SQL statement of one of these statement types:

EXECUTE FUNCTION
EXECUTE PROCEDURE
SELECT.

This prepared statement text that statement_id specifies can include question mark ( ? ) symbols as placeholders for values that the user supplies at runtime, but these placeholders in the PREPARE statement can represent only data values, not SQL identifiers.

Dynamic cursors that the DECLARE statement can define in SPL routines resemble ESQL/C Select cursors or Function cursors in their functionality, but with these restrictions:

Cursors that DECLARE defines in an SPL routine can be Select cursors or Function cursors, but they cannot be Insert cursors nor Collection cursors.
The identifier of the cursor or of the prepared statement cannot be specified as an SPL variable, because in SPL, the identifiers of variables, cursors, and prepared objects all share the same namespace.
By default, dynamic cursors of SPL are sequential. They cannot be scroll cursors.
The semantics of dynamic cursors that you create with the WITH HOLD keywords are the same as for hold cursors that the FOREACH statement declares.
The WITH HOLD keywords are valid in SPL routines only for Select cursors. If statement_id references the prepared text of an EXECUTE FUNCTION or EXECUTE PROCEDURE statement, the DECLARE statement fails with error -26056.
The FOR UPDATE and FOR READ ONLY keywords that ESQL/C supports in DECLARE statements are not supported in SPL routines. Use the FOREACH statement of SPL to declare direct cursors that can emulate the functionality of ESQL/C update cursors. (But queries associated with direct cursors are defined when the UDR is compiled, rather than at runtime.)
The DECLARE statement in SPL routines does not support SELECT operations on collection-derived tables.
Syntax errors in DECLARE statements of SPL routines are reported at runtime, unlike syntax errors of ESQL/C, which are reported when the routine is compiled.

The names of the dynamic cursors that the DECLARE statement associates with a prepared statement in SPL routines can be referenced by the OPEN, CLOSE, FETCH, and FREE statements of dynamic SQL in the same SPL routine.

In the following program fragment, a cursor called equi_noctis is declared, opened, closed, and freed.

CREATE FUNCTION lente
   DEFINE first, last VARCHAR(30);
   . . .
   DATABASE stores_demo;
   LET first = "SELECT * FROM state";
   LET lsst = "WHERE code < ?";
   PREPARE stmt_1 FROM first || last;
   DECLARE equi_noctis FOR stmt_1;
   OPEN equi_noctis
   . . .
   CLOSE equi_noctis;
   FREE equi_noctis;
   FREE stmt_1;
   ...
   END FUNCTION;