Previous Topic: DEALLOCATE PREPARENext Topic: DECLARE EXTERNAL CURSOR

Statements › DECLARE CURSOR

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.

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.

Note: For more information about how the caller processes returned result sets, see ALLOCATE CURSOR and CALL.

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