DB2 Version 9.7 for Linux, UNIX, and Windows

DECLARE CURSOR statement

The DECLARE CURSOR statement defines a cursor.

Invocation

Although an interactive SQL facility might provide an interface that gives the appearance of interactive execution, this statement can only be embedded within an application program. It is not an executable statement and cannot be dynamically prepared.

When invoked using the command line processor, additional options can be specified. For more information, refer to "Using command line SQL statements and XQuery statements".

Authorization

The term "SELECT statement of the cursor" is used to specify the authorization rules. The SELECT statement of the cursor is one of the following:
  • The prepared select-statement identified by statement-name
  • The specified select-statement

The privileges held by the authorization ID of the statement must include the privileges necessary to execute the select-statement. See the Authorization section in "SQL queries".

If statement-name is specified:
  • The authorization ID of the statement is the run-time authorization ID.
  • The authorization check is performed when the select-statement is prepared.
  • The cursor cannot be opened unless the select-statement is successfully prepared.
If select-statement is specified:
  • GROUP privileges are not checked.
  • The authorization ID of the statement is the authorization ID specified during program preparation.

Syntax

Read syntax diagramSkip visual syntax diagram
                         .-ASENSITIVE------.              
>>-DECLARE--cursor-name--+-----------------+--CURSOR--●--------->
                         |             (1) |              
                         '-INSENSITIVE-----'              

>--| holdability |--●--| returnability |--●--------------------->

>--FOR--+-select-statement-+-----------------------------------><
        '-statement-name---'   

holdability

   .-WITHOUT HOLD-.   
|--+--------------+---------------------------------------------|
   '-WITH HOLD----'   

returnability

   .-WITHOUT RETURN-------------.   
|--+----------------------------+-------------------------------|
   |              .-TO CALLER-. |   
   '-WITH RETURN--+-----------+-'   
                  '-TO CLIENT-'     

Notes:
  1. This option can be used only in the context of a compound SQL (compiled) statement

Description

cursor-name
Specifies the name of the cursor created when the source program is run. The name must not be the same as the name of another cursor declared in the source program. The cursor must be opened before use.
ASENSITIVE or INSENSITIVE
Specifies whether the cursor is asensitive or insensitive to changes.
ASENSITIVE
Specifies that the cursor should be as sensitive as possible to insert, update, or delete operations made to the rows underlying the result table, depending on how the select-statement is optimized. This option is the default.
INSENSITIVE
Specifies that the cursor does not have sensitivity to insert, update, or delete operations that are made to the rows underlying the result table. If INSENSITIVE is specified, the cursor is read-only and the result table is materialized when the cursor is opened. As a result, the size of the result table, the order of the rows, and the values for each row do not change after the cursor is opened. The SELECT statement cannot contain a FOR UPDATE clause, and the cursor cannot be used for positioned updates or deletes.
WITHOUT HOLD or WITH HOLD
Specifies whether or not the cursor should be prevented from being closed as a consequence of a commit operation.
WITHOUT HOLD
Does not prevent the cursor from being closed as a consequence of a commit operation. This is the default.
WITH HOLD
Maintains resources across multiple units of work. The effect of the WITH HOLD cursor attribute is as follows:
  • For units of work ending with COMMIT:
    • Open cursors defined WITH HOLD remain open. The cursor is positioned before the next logical row of the results table.

      If a DISCONNECT statement is issued after a COMMIT statement for a connection with WITH HOLD cursors, the held cursors must be explicitly closed or the connection will be assumed to have performed work (simply by having open WITH HELD cursors even though no SQL statements were issued) and the DISCONNECT statement will fail.

    • All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table, and for parallel environments, the locks on rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held.
    • Valid operations on cursors defined WITH HOLD immediately following a COMMIT request are:
      • FETCH: Fetches the next row of the cursor.
      • CLOSE: Closes the cursor.
    • UPDATE and DELETE CURRENT OF CURSOR are valid only for rows that are fetched within the same unit of work.
    • LOB locators are freed.
    • The set of rows modified by:
      • A data change statement
      • Routines that modify SQL data embedded within open WITH HOLD cursors
      is committed.
  • For units of work ending with ROLLBACK:
    • All open cursors are closed.
    • All locks acquired during the unit of work are released.
    • LOB locators are freed.
  • For special COMMIT case:
    • Packages can be recreated either explicitly, by binding the package, or implicitly, because the package has been invalidated and then dynamically recreated the first time it is referenced. All held cursors are closed during package rebind. This might result in errors during subsequent execution.
WITHOUT RETURN or WITH RETURN
Specifies whether or not the result table of the cursor is intended to be used as a result set that will be returned from a procedure or dynamically prepared compound SQL (compiled) statement.
WITHOUT RETURN
Specifies that the result table of the cursor is not intended to be used as a result set that will be returned from a procedure or dynamically prepared compound SQL (compiled) statement.
WITH RETURN
Specifies that the result table of the cursor is intended to be used as a result set that will be returned from a procedure or dynamically prepared compound SQL (compiled) statement. For procedures, WITH RETURN is relevant only if the DECLARE CURSOR statement is contained with the source code for a procedure. In other cases, the precompiler might accept the clause, but it has no effect.

Within an SQL procedure or dynamically prepared compound SQL (compiled) statement, cursors declared using the WITH RETURN clause that are still open when the SQL procedure or compound SQL (compiled) statement ends, define the result sets from the SQL procedure or compound SQL (compiled) statement. All other open cursors in an SQL procedure or compound SQL (compiled) statement are closed when the SQL procedure or compound SQL (compiled) statement ends. Within an external procedure (one not defined using LANGUAGE SQL), the default for all cursors is WITH RETURN TO CALLER. Therefore, all cursors that are open when the procedure ends will be considered result sets. Cursors that are returned from a procedure cannot be declared as scrollable cursors.

TO CALLER
Specifies that the cursor can return a result set to the caller. For example, if the caller is another procedure, the result set is returned to that procedure. If the caller is a client application, the result set is returned to the client application. The TO CALLER clause must not be specified when the DECLARE CURSOR statement is embedded in a dynamically prepared compound SQL (compiled) statement (SQLSTATE 42601).
TO CLIENT
Specifies that the cursor can return a result set to the client application. This cursor is invisible to any intermediate nested procedures. If a function, method, or trigger called the procedure or compound SQL (compiled) statement either directly or indirectly, result sets cannot be returned to the client and the cursor will be closed after the procedure or compound SQL (compiled) statement finishes.
select-statement
Identifies the SELECT statement of the cursor. The select-statement must not include parameter markers, but can include references to host variables. The declarations of the host variables must precede the DECLARE CURSOR statement in the source program.
statement-name
The SELECT statement of the cursor is the prepared SELECT statement identified by the statement-name when the cursor is opened. The statement-name must not be identical to a statement-name specified in another DECLARE CURSOR statement of the source program.

For an explanation of prepared SELECT statements, see "PREPARE".

Notes

Examples

Example 1:  The DECLARE CURSOR statement associates the cursor name C1 with the results of the SELECT.
   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT DEPTNO, DEPTNAME, MGRNO
     FROM DEPARTMENT
     WHERE ADMRDEPT = 'A00';
Example 2:  Assume that the EMPLOYEE table has been altered to add a generated column, WEEKLYPAY, that calculates the weekly pay based on the yearly salary. Declare a cursor to retrieve the system-generated column value from a row to be inserted.
   EXEC SQL DECLARE C2 CURSOR FOR
     SELECT E.WEEKLYPAY
     FROM NEW TABLE
       (INSERT INTO EMPLOYEE
        (EMPNO, FIRSTNME, MIDINIT, LASTNAME, EDLEVEL, SALARY)
        VALUES('000420', 'Peter', 'U', 'Bender', 16, 31842) AS E;