DECLARE CURSOR statement

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 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.

Syntax

holdability

returnability

Notes:

• ¹ 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.

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.

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. 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, cursors declared using the WITH RETURN clause that are still open when the SQL procedure ends, define the result sets from the SQL procedure. All other open cursors in an SQL procedure are closed when the SQL procedure 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.

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 either directly or indirectly, result sets cannot be returned to the client and the cursor will be closed after the procedure 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

• A program called from another program, or from a different source file within the same program, cannot use the cursor that was opened by the calling program.
• Unnested procedures, with LANGUAGE other than SQL, will have WITH RETURN TO CALLER as the default behavior if DECLARE CURSOR is specified without a WITH RETURN clause, and the cursor is left open in the procedure. This provides compatibility with procedures from previous versions that allow procedures to return result sets to applicable client applications. To avoid this behavior, close all cursors opened in the procedure.
• If the SELECT statement of a cursor contains CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP, all references to these special registers will yield the same respective datetime value on each FETCH. This value is determined when the cursor is opened.
• For more efficient processing of data, the database manager can block data for read-only cursors when retrieving data from a remote server. The use of the FOR UPDATE clause helps the database manager decide whether a cursor is updatable or not. Updatability is also used to determine the access path selection as well. If a cursor is not going to be used in a Positioned UPDATE or DELETE statement, it should be declared as FOR READ ONLY.
• A cursor in the open state designates a result table and a position relative to the rows of that table. The table is the result table specified by the SELECT statement of the cursor.
• A cursor is deletable if each of the following conditions is true:
  • Each FROM clause of the outer fullselect identifies only one base table or deletable view (cannot identify a nested or common table expression or a nickname) without use of the OUTER clause
  • The outer fullselect does not include a VALUES clause
  • The outer fullselect does not include a GROUP BY clause or HAVING clause
  • The outer fullselect does not include column functions in the select list
  • The outer fullselect does not include SET operations (UNION, EXCEPT, or INTERSECT) with the exception of UNION ALL
  • The outer fullselect does not contain a FOR SYSTEM_TIME period specification.
  • The select list of the outer fullselect does not include DISTINCT
  • The outer fullselect does not include an ORDER BY clause (even if the ORDER BY clause is nested in a view), and the FOR UPDATE clause has not been specified
  • The select-statement does not include a FOR READ ONLY clause
  • The FROM clause of the outer fullselect does not include a data-change-table-reference
  • One or more of the following conditions is true:
    • The FOR UPDATE clause is specified
    • The cursor is statically defined, unless the STATICREADONLY bind option is YES
    • The LANGLEVEL bind option is MIA or SQL92E
  A column in the select list of the outer fullselect associated with a cursor is updatable if each of the following conditions is true:

  • The cursor is deletable
  • The column resolves to a column of the base table
  • The LANGLEVEL bind option is MIA, SQL92E or the select-statement includes the FOR UPDATE clause (the column must be specified explicitly or implicitly in the FOR UPDATE clause)

  A cursor is read-only if it is not deletable.

  A cursor is ambiguous if each of the following conditions is true:

  • The select-statement is dynamically prepared
  • The select-statement does not include either the FOR READ ONLY clause or the FOR UPDATE clause
  • The LANGLEVEL bind option is SAA1
  • The cursor otherwise satisfies the conditions of a deletable cursor

  An ambiguous cursor is considered read-only if the BLOCKING bind option is ALL, otherwise it is considered updatable.

• Cursors in procedures that are called by application programs written using CLI can be used to define result sets that are returned directly to the client application. Cursors in SQL procedures can also be returned to a calling SQL procedure only if they are defined using the WITH RETURN clause.
• Cursors declared in routines that are invoked directly or indirectly from a cursor declared WITH HOLD, do not inherit the WITH HOLD option. Thus, unless the cursor in the routine is explicitly defined WITH HOLD, a COMMIT in the application will close it.
  Consider the following application and two UDFs:

  Application:
  
     DECLARE APPCUR CURSOR WITH HOLD FOR SELECT UDF1() ...
     OPEN APPCUR
     FETCH APPCUR ...
     COMMIT
  
  UDF1:
  
     DECLARE UDF1CUR CURSOR FOR SELECT UDF2() ...
     OPEN UDF1CUR
     FETCH UDF1CUR ...
  
  UDF2:
  
     DECLARE UDF2CUR CURSOR WITH HOLD FOR SELECT UDF2() ...
     OPEN UDF2CUR
     FETCH UDF2CUR ...

  After the application fetches cursor APPCUR, all three cursors are open. When the application issues the COMMIT statement, APPCUR remains open, because it was declared WITH HOLD. In UDF1, however, the cursor UDF1CUR is closed, because it was not defined with the WITH HOLD option. When the cursor UDF1CUR is closed, all routine invocations in the corresponding select-statement complete (receiving a final call, if so defined). UDF2 completes, which causes UDF2CUR to close.

Examples

   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT DEPTNO, DEPTNAME, MGRNO
     FROM DEPARTMENT
     WHERE ADMRDEPT = 'A00';

   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;