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--'
 
>-----+-------------+--FOR--+-select-statement-+---------------><
      '-WITH RETURN-'       '-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

Option is available for applications that require compatibility with DB2 Universal Database for OS/390. This option should only be used when declaring a cursor in the code for a stored procedure. When specified, it is ignored by DB2 Universal Database.

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.

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

• Cursors referencing a nickname as a potential target are assumed to be read only.

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 the read-only-clause.

⁸⁰

The FOR UPDATE clause is defined in the update-clause.

[ DB2 List of Books | Search the DB2 Books ]