SQL Reference

DECLARE CURSOR

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.

Authorization

The term "SELECT statement of the cursor" is used in order to specify the authorization rules. The SELECT statement of the cursor is one of the following:

• The prepared select-statement identified by the statement-name
• The specified select-statement.

For each table or view identified (directly or using an alias) in the SELECT statement of the cursor, the privileges held by the authorization ID of the statement must include at least one of the following:

• SYSADM or DBADM authority.
• For each table or view identified in the select-statement:
  • SELECT privilege on the table or view, or
  • CONTROL privilege of the table or view.

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

>>-DECLARE--cursor-name--CURSOR----+------------+--------------->
                                   '-WITH HOLD--'
 
>-----+------------------------------+-------------------------->
      |              .-TO CALLER--.  |
      '-WITH RETURN--+------------+--'
                     '-TO CLIENT--'
 
>----FOR--+-select-statement-+---------------------------------><
          '-statement-name---'
 

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 (see OPEN).

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.
• 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 may 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 may result in errors during subsequent execution.

WITH RETURN

This clause indicates that the cursor is intended for use as a result set from a stored procedure. WITH RETURN is relevant only if the DECLARE CURSOR statement is contained with the source code for a stored procedure. In other cases, the precompiler may 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 stored procedure (one not defined using LANGUAGE SQL), the WITH RETURN clause has no effect, and any cursors open at the end of an external procedure are considered the result sets.

TO CALLER

Specifies that the cursor can return a result set to the caller. For example, if the caller is another stored procedure, the result set is returned to that stored 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.

select-statement

Identifies the SELECT statement of the cursor. The select-statement must not include parameter markers, but may include references to host variables. The declarations of the host variables must precede the DECLARE CURSOR statement in the source program. See select-statement for an explanation of select-statement.

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 stored 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 stored procedures from previous versions that allow stored 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 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 all of the following are 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 select list of the outer fullselect does not include DISTINCT
  • the select-statement does not include an ORDER BY clause
  • the select-statement does not include a FOR READ ONLY clause ⁸⁷
  • one or more of the following is true:
    • the FOR UPDATE clause ⁸⁸ is specified
    • the cursor is statically defined
    • 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 all of the following are 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 all of the following are 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 deletable.

• Cursors in stored 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. See the Notes.

Example

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';

⁸⁷

The FOR READ ONLY clause is defined in read-only-clause.

⁸⁸

The FOR UPDATE clause is defined in update-clause.