Declares a cursor definition.
A cursor is declared in accordance with the
select-statementor the result set procedure call specified in
select-statementmay 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
UPDATE CURRENTstatements. The cursor must be activated with an
OPENstatement before it can be used.
A cursor declared as
REOPENABLEmay 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
SCROLLwill 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 HOLDwill be a holdable cursor. Open holdable cursors are not closed when a transaction is committed. A cursor will be non-holdable if
WITHOUT HOLDis explicitly specified.
NO SCROLLare default cursor attributes and do not have to be specified.
select-statement, see SELECT Statements.
procedure-call-statement, see CALL.
A cursor for a result-set procedure call must not be declared
execute-statement-commandis used, the precompiled statement must be a select or a result-set procedure.
procedure-call-statementis specified, it must specify a result set procedure.
The following restrictions apply to procedural usage:
- The cursor cannot be declared as
SELECTstatement cannot be in the form of a prepared dynamic SQL statement, i.e. specifying
dynamic-statement-nameis not allowed
- If the cursor declaration contains a select statement, the
access-clausefor the procedure must be
READS SQL DATAor
MODIFIES SQL DATA, see CREATE PROCEDURE
execute-statement-commandis not allowed.
DECLARE CURSORstatement 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-namemay 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).
select-statementis 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
CALLstatement is controlled by the opening of the cursor and subsequent fetches, see the Mimer SQL Programmer's Manual, Result Set Procedures.
REOPENABLEcannot be used if evaluation of
select-statementuses a work table, or if the cursor declaration occurs within a procedure.
If the declared cursor is a dynamic cursor, the
DECLAREstatement must be placed before the
Cursors should normally be declared
WITHOUT HOLD(default), because
WITH HOLDcursors 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.
ExamplesDECLARE cur2 SCROLL CURSOR FOR SELECT c1,c2 FROM tab1; DECLARE cur3 CURSOR WITH HOLD FOR stmt1; DECLARE cur1 CURSOR FOR EXECUTE STATEMENT seltaba;
SQL-2003 Core Fully compliant. SQL-2003 Features outside core Feature F431, "Read-only scrollable cursor", support for the
SCROLLkeywordFeature 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