DECLARE CURSOR

The DECLARE CURSOR data manipulation statement defines a cursor for a specified result table. Use this statement only in SQL that is embedded in a program.
idmscu19
The DECLARE CURSOR data manipulation statement defines a cursor for a specified result table. Use this statement only in SQL that is embedded in a program.
This article describes the following information:
2
2
Authorization
To issue a DECLARE CURSOR statement that includes a cursor specification, you must own or have the SELECT privilege on each table, view, table procedure, and function explicitly named in the cursor specification. Authorization checking for cursors that reference a statement is done during execution of the corresponding PREPARE statement.
Additional authorization requirements apply to each view explicitly named in the cursor specification, to each view explicitly named in the definition of such a view, to each view explicitly named in the definition of those views, and so forth.
For any such view, the owner of the view must own or have the grantable SELECT privilege on each table, view, table procedure, and function explicitly named in the view definition.
Syntax
  ►►── 
cursor-declaration
 ──────────────────────────────────────────────────────►◄  
Expansion of cursor-declaration
  ►►── DECLARE 
static-cursor-name
 ─┬──────────┬─ CURSOR ─────────────────────────►                                  └─ GLOBAL ─┘  ►───┬───────────────────┬── FOR ──┬─ 
cursor-specification
 ──┬─────────────────►◄      ├─ WITH RETURN ─────┤         └─ 
static-statement-name
 ─┘      └─ WITHOUT RETURN ◄─┘  
Parameters
Parameters for Expansion of cursor-declaration
  • static-cursor-name
    Assigns a name to the cursor.
    Cursor-name
    must be a 1- through 18-character name that follows the conventions for SQL identifiers.
  • GLOBAL
    Specifies the cursor can be used by other application programs sharing the access module that contains the cursor definition.
    The GLOBAL parameter is not valid for cursors associated with result tables defined by dynamically compiled SELECT statements.
    The GLOBAL parameter is a CA IDMS extension of the SQL standard.
  • WITH RETURN
    Defines the cursor as a returnable cursor. If a returnable cursor is declared in an SQL-invoked procedure and is in the open state when the procedure returns to its caller, a result set is returned to the caller.
  • WITHOUT RETURN
    Specifies that the cursor is not a returnable cursor. This is the default.
  • FOR
    Defines the result table associated with the cursor.
  • cursor-specification
    Specifies the result table in the form of a cursor definition. For expanded
    cursor-specification
    syntax, see Expansion of Cursor-specification.
  • static-statement-name
    Specifies the result table in the form of a dynamically compiled SELECT statement.
    Statement-name
    must identify a statement named in a PREPARE statement.
    You cannot use a dynamically-compiled SELECT statement to define the result table associated with a global cursor using the DECLARE CURSOR statement. This can be achieved using an ALLOCATE CURSOR statement.
Usage
Uniqueness of Cursor Names
Each cursor name must be unique within an application program. Global cursor names must be unique within an access module.
Updateable Cursors
The cursor defined by a DECLARE CURSOR statement is updateable if the cursor specification, contained in the DECLARE CURSOR statement or represented by the
static-statement-name
is updateable.
Defining Returnable Cursors
While any cursor can be defined as a returnable cursor using WITH RETURN, it only makes sense to do so in programs that are invoked as SQL-invoked procedures and that are defined with a non-zero dynamic result set attribute.
The invoker must use the ALLOCATE CURSOR statement to associate returned result sets with received cursors for further processing.
For more information about how the caller processes returned result sets, see ALLOCATE CURSOR and CALL Statement.
Examples
Declaring a Global Cursor with a Specified Row Order
The following DECLARE CURSOR statement defines a global cursor for a result table containing information about all current employees and consultants. The rows in the table are ordered first by last name, then by department, and then by employee identifier.
EXEC SQL    DECLARE ALL_EMP_CURSOR GLOBAL CURSOR       FOR SELECT DEPT_ID, EMP_ID, 'EMPLOYEE', EMP_LNAME,             EMP_FNAME, STREET, CITY, STATE, ZIP_CODE          FROM EMPLOYEE          WHERE STATUS IN ('A', 'L', 'S')          UNION SELECT DEPT_ID, CON_ID, 'CONSULTANT', CON_LNAME,                CON_FNAME, STREET, CITY, STATE, ZIP_CODE             FROM CONSULTANT       ORDER BY 4, 5, 1, 2 END-EXEC
Naming an Updateable Column
The following DECLARE CURSOR statement defines a cursor for a result table containing fiscal year 1999 bonus information for each employee. The statement specifies that the BONUS_AMOUNT column of the BENEFITS table can be updated through the cursor.
EXEC SQL    DECLARE BONUS_CURSOR CURSOR       FOR SELECT EMP_ID, BONUS_AMOUNT          FROM BENEFITS          WHERE FISCAL_YEAR = '99'       FOR UPDATE OF BONUS_AMOUNT END-EXEC
Associating a Cursor with a Dynamically Compiled SELECT Statement
The DECLARE CURSOR statement shown next, defines a cursor for the result table derived from a dynamically compiled SELECT statement named DYN_PROJ_SELECT. The application program must include a PREPARE statement for DYN_PROJ_SELECT.
EXEC SQL DECLARE PROJECT_CURSOR CURSOR    FOR DYN_PROJ_SELECT END-EXEC
Defining Returnable Cursors
The following DECLARE CURSOR statement is specified in an SQL-invoked procedure written in SQL. The cursor RET_COVERAGE returns a result set consisting of the rows of the table DEMOEMPL.COVERAGE for which the column EMP_ID equals the value of the parameter P_EMP_ID. To effectively return the result set, the cursor must be left open on the return from the procedure.
declare RET_COVERAGE cursor with return for     select * from DEMOEMPL.COVERAGE      where EMP_ID = P_EMP_ID;
More Information