DECLARE CURSOR

Declares a cursor definition.

 

Usage

Embedded, Module, 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 CALL.

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