|
|
|
|
|
|
DECLARE CURSOR
Usage
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-statement, identified by statement-name, in dynamic SQL statements, see the Mimer SQL Programmer's Manual, chapter 11, 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 will be non-scrollable if NO SCROLL is explicitly specified. The cursor will be non-scrollable and not reopenable by default.
Language Elements
select-statement, see SELECT Statements.
procedure-call-statement, see CALL.
Restrictions
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 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.
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, chapter 12, 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.
A reopenable cursor can be used to solve the 'Parts explosion' problem. Refer to the Mimer SQL Programmer's Manual, chapter 8, The 'Parts explosion' Problem for a description.
Example
DECLARE cur1 CURSOR FOR EXECUTE STATEMENT seltabaStandard Compliance
Standard
Compliance
Comments
SQL-92Note: See also standard compliance for SELECT.
 Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se |
|
|
|
|
|
|
