DECLARE CURSOR

DECLARE CURSOR

Declares a cursor definition.

Usage

Embedded/Procedural.

Description

A cursor is declared in accordance with the select-statement or the result set procedure call specified in procedure-call-statement.

The select-statement may be specified explicitly in ordinary embedded SQL applications or by the name of a prepared SELECT, identified by dynamic-statement-name, in dynamic SQL statements, see the Mimer SQL Programmer's Manual, Dynamic SQL.

The cursor is identified by cursor-name, and may be used in FETCH, DELETE CURRENT and UPDATE CURRENT statements. The cursor must be activated with an OPEN statement before it can be used.

A cursor declared as REOPENABLE may be opened several times in succession, and previous cursor states are saved on a stack, see OPEN. Saved cursor states are restored when the current state is closed, see CLOSE.

A cursor declared as SCROLL will be a scrollable cursor. For a scrollable cursor, records can be fetched using an orientation specification. See the description of FETCH for a description of how the orientation can be specified.

A cursor declared WITH HOLD will be a holdable cursor. Open holdable cursors are not closed when a transaction is committed. A cursor will be non-holdable if WITHOUT HOLD is explicitly specified.

WITHOUT HOLD and NO SCROLL are default cursor attributes and do not have to be specified.

Language Elements

select-statement, see SELECT Statements.

procedure-call-statement, see CALL.

Restrictions

A cursor for a result-set procedure call must not be declared WITH HOLD.

If an execute-statement-command is used, the precompiled statement must be a select or a result-set procedure.

If a procedure-call-statement is specified, it must specify a result set procedure.

The following restrictions apply to procedural usage:

The cursor cannot be declared as REOPENABLE
The SELECT statement cannot be in the form of a prepared dynamic SQL statement, i.e. specifying dynamic-statement-name is not allowed
If the cursor declaration contains a select statement, the access-clause for the procedure must be READS SQL DATA or MODIFIES SQL DATA, see CREATE PROCEDURE
The execute-statement-command is not allowed.

Notes

The DECLARE CURSOR statement is declarative, not executable. In an embedded usage context, access rights for the current ident are checked when the cursor is opened, not when it is declared.

In a procedural usage context, access rights for the current ident are checked when the cursor is declared, i.e. when the procedure containing the declaration is created.

The value of cursor-name may not be the same as the name of any other cursor declared within the same compound statement (Procedural usage) or in the same compilation unit (Embedded usage).

The select-statement is evaluated when the cursor is opened, not when it is declared. This applies both to select-statement's identified by statement name, and to host variable references used anywhere in the select statement.

The execution of the result set procedure specified in a CALL statement is controlled by the opening of the cursor and subsequent fetches, see the Mimer SQL Programmer's Manual, Result Set Procedures.

REOPENABLE cannot be used if evaluation of select-statement uses a work table, or if the cursor declaration occurs within a procedure.

If the declared cursor is a dynamic cursor, the DECLARE statement must be placed before the PREPARE statement.

Cursors should normally be declared WITHOUT HOLD (default), because WITH HOLD cursors require more internal resources then ordinary cursors.

A reopenable cursor can be used to solve the `Parts explosion' problem. Refer to the Mimer SQL Programmer's Manual, The 'Parts explosion' Problem for a description.

Examples
 DECLARE cur2 SCROLL CURSOR FOR SELECT c1,c2 FROM tab1;
 
 DECLARE cur3 CURSOR WITH HOLD FOR stmt1;
 
 DECLARE cur1 CURSOR FOR EXECUTE STATEMENT seltaba;
Standard Compliance

Standard

Compliance

Comments

SQL-2003

Core

Fully compliant.

SQL-2003

Features outside core

Feature F431, "Read-only scrollable cursor", support for the SCROLL keyword

Feature F831, "Full cursor update"

Feature T551, "Optional keywords for default syntax" support for the WITHOUT HOLD clause.

Mimer SQL extension

The keyword REOPENABLE is a Mimer SQL extension.

Support for EXECUTE STATEMENT and CALL statement in a cursor definition is a Mimer SQL extension.

Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.
SQL-2003	Features outside core	Feature F431, "Read-only scrollable cursor", support for the `SCROLL` keyword Feature F831, "Full cursor update" Feature T551, "Optional keywords for default syntax" support for the WITHOUT HOLD clause.
	Mimer SQL extension	The keyword REOPENABLE is a Mimer SQL extension. Support for EXECUTE STATEMENT and CALL statement in a cursor definition is a Mimer SQL extension.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se