DECLARE CURSOR is not an executable statement. It can only be embedded in an application language.
For each table (or view) identified in the SELECT portion of the DECLARE CURSOR statement, users must possess either DBADM authority for the corresponding database (tables only), the SELECT privilege on the table or view, overall SYSADM or SYSCTRL authority for the location, or be the owner of the table or view.
The SELECT statement specified as an argument within the DECLARE CURSOR statement can be either a standard SELECT statement (select-statement) or a dynamically prepared (PREPARE command) select statement stored in a declared SQL statement (statement-name). Each of these distinct SELECT statement types must have access to different sets of authorization privileges in order to execute.
If a select-statement is specified, then the privileges available for this SELECT statement match those held by the CURRENT SQLID when the cursor is opened.
If a statement-name is specified, the available privileges for the prepared SELECT statement are a combination of the privileges held by the AuthID of the application process. The authorization check is performed when the SELECT statement is prepared. The cursor cannot be opened (OPEN command) unless the SELECT statement has been successfully prepared.
DECLARE cursor-name
[[NOSCROLL] | {ASENSITIVE | INSENSITIVE} SCROLL | SENSITIVE [DYNAMIC | STATIC] SCROLL]
CURSOR [holdability | returnability | rowset-positioning [...]]
FOR {select-statement | statement-name}
holdability
[WITH HOLD | WITHOUT HOLD]
returnability
[WITH RETURN [TO CALLER] | WITHOUT RETURN]
rowset-positioning
[WITH ROWSET POSITIONING | WITHOUT ROWSET POSITIONING]
| cursor-name | Any valid identifier that names the new cursor and does not identify an already declared cursor in the source program. |
| select-statement | Specifies an actual query (SELECT statement) represented by the cursor. This SELECT statement can include host variables, the declarations for which must precede the DECLARE CURSOR statement in the source program. |
| statement-name | Indicates that the query occurring when opening the cursor originated from a prepared SELECT statement identified by statement-name. The statement-name must not match any other statement-name specified within any other DECLARE CURSOR statement in the source program. |
The DECLARE CURSOR command is only available when SQL is embedded in a host language such as COBOL or C. The DECLARE CURSOR command is checked for the following syntactical elements:
Specifies that the cursor is scrollable. For a scrollable cursor, whether the cursor has sensitivity to inserts, updates, or deletes depends on the cursor sensitivity option in effect for the cursor. If a sensitivity option is not specified, ASENSITIVE is the default. The sensitivity options include the following ones:
Specifies the desired sensitivity of the cursor to inserts, updates, or deletes that made to the rows underlying the result table. The sensitivity of the cursor determines whether XDB can materialize the rows of the result into a temporary table.
Specifies that the cursor should be as sensitive as possible. This is the default.
A cursor that defined as ASENSITIVE will be either insensitive or sensitive dynamic; it will not be sensitive static.
Specifies that the cursor does not have sensitivity to inserts, updates, or deletes that are made to the rows underlying the result table. 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. In addition, the cursor is read-only. The SELECT statement or attribute-string of the PREPARE statement cannot contain a FOR UPDATE clause, and the cursor cannot be used for positioned updates or deletes.
Specifies that the cursor has sensitivity to changes that are made to the database after the result table is materialized. The cursor is always sensitive to updates and deletes that are made using the cursor (that is, positioned updates and deletes using the same cursor). When the current value of a row no longer satisfies the select-statement or statement-name, that row is no longer visible through the cursor. When a row of the result table is deleted from the underlying base table, the row is no longer visible through the cursor.
If XDB cannot make changes visible to the cursor, then an error is issued at bind time for OPEN CURSOR. XDB cannot make changes visible to the cursor when the cursor implicitly becomes read-only. Such is the case when the result table must be materialized, as when the FROM clause of the SELECT statement contains more than one table or view.
The default is DYNAMIC.
Specifies that the result table of the cursor is dynamic, meaning that the size of the result table may change after the cursor is opened as rows are inserted into or deleted from the underlying table, and the order of the rows may change. Rows that are inserted, deleted, or updated by statements that are executed by the same application process as the cursor are visible to the cursor immediately. Rows that are inserted, deleted, or updated by statements that are executed by other application processes are visible only after the statements are committed. If a column for an ORDER BY clause is updated via a cursor or any means outside the process, the next FETCH statement behaves as if the updated row was deleted and re-inserted into the result table at its correct location. At the time of a positioned update, the cursor is positioned before the next row of the original location and there is no current row, making the row appear to have moved.
If a SENSITIVE DYNAMIC cursor is not possible, an error is returned. For example, if a temporary table is needed (such as for processing a FETCH FIRST n ROWS ONLY clause), an error is returned. The SELECT statement of a cursor that is defined as SENSITIVE DYNAMIC cannot contain an INSERT statement.
Specifies that the size of the result table and the order of the rows do not change after the cursor is opened. Rows inserted into the underlying table are not added to the result table regardless of how the rows are inserted. Rows in the result table do not move if columns in the ORDER BY clause are updated in rows that have already been materialized. Positioned updates and deletes are allowed if the result table is updatable. The SELECT statement of a cursor that is defined as SENSITIVE STATIC cannot contain an INSERT statement.
A STATIC cursor has visibility to changes made by this cursor using positioned updates or deletes. Committed changes made outside this cursor are visible with the SENSITIVE option of the FETCH statement. A FETCH SENSITIVE can result in a hole in the result table (that is, a difference between the result table and its underlying base table). If an updated row in the base table of a cursor no longer satisfies the predicate of its SELECT statement, an update hole occurs in the result table. If a row of a cursor was deleted in the base table, a delete hole occurs in the result table. When a FETCH SENSITIVE detects an update hole, no data is returned (a warning is issued), and the cursor is left positioned on the update hole. When a FETCH SENSITIVE detects a delete hole, no data is returned (a warning is issued), and the cursor is left positioned on the delete hole.
Updates through a cursor result in an automatic re-fetch of the row. This re-fetch means that updates can create a hole themselves. The re-fetched row also reflects changes as a result of triggers updating the same row. It is important to reflect these changes to maintain the consistency of data in the row.
Using a nondeterministic function (built-in or user-defined) in the WHERE clause of the select-statement or statement-name of a SENSITIVE STATIC cursor can cause misleading results. This situation occurs because XDB constructs a temporary result table and retrieves rows from this table for FETCH INSENSITIVE statements. When XDB processes a FETCH SENSITIVE statement, rows are fetched from the underlying table and predicates are re-evaluated. Using a nondeterministic function can yield a different result on each FETCH SENSITIVE of the same row, which could also result in the row no longer being considered a match.
A FETCH INSENSITIVE on a SENSITIVE STATIC SCROLL cursor is not sensitive to changes made outside the cursor, unless a previous FETCH SENSITIVE has already refreshed that row; however, positioned updates and delete changes with the cursor are visible.
STATIC cursors are insensitive to insertions.
This optional clause prevents a commit operation from closing the cursor. Instead, a COMMIT statement logs all the changes from the current unit of work, leaving in place those locks required to maintain the cursor. After the commit operation, a single FETCH statement must initially be executed before a subsequently positioned UPDATE or DELETE statement can be executed on the cursor. After this FETCH, the cursor pointer can be advanced onto the next cursor row (or the row following the current row before the COMMIT). A ROLLBACK command, however, will close the cursor.
Omitting the WITH HOLD option from the cursor declaration causes that cursor to be implicitly closed by a COMMIT operation. A CONNECT or ROLLBACK operation also implicitly closes all cursors.
Closing a cursor before a COMMIT operation, has the same effect as declaring the cursor without the WITH HOLD option.
This optional clause specifies that the cursor, if it is declared in a stored procedure, can return a result set to the caller.
A result table is a temporary table of row data created when an OPEN command is issued against a previously declared cursor. This temporary table remains in memory until a CLOSE command is issued against the cursor (or a COMMIT or ROLLBACK occurs). The query criteria specified in the cursor's original PREPARE statement determines the row data retrieved into the result table. An opened (OPEN command) cursor consists of both a result table and a position pointer that moves among the table rows.
A read-only result table occurs in any of the following situations:
In COBOL source programs, a DECLARE CURSOR command is checked to verify whether host variables exist in either the LINKAGE or WORKING STORAGE section. In COBOL applications (as mentioned above), an application called from another application cannot use a cursor that was declared by the calling application. The scope of the cursor is the source program in which it is defined, which limits cursor references to statements precompiled along with the cursor declaration.
The following COBOL language examples illustrate PREPARE, DECLARE, OPEN, and FETCH cursor statements:
EXEC SQL
DECLARE d1 CURSOR FOR
SELECT deptno, dname
FROM dept
WHERE mgrno = :mgrno
END-EXEC
or:
EXEC SQL
PREPARE emp_in_dept FROM
SELECT e_no, lname
FROM employee
WHERE dept = ?
END-EXEC
EXEC SQL
DECLARE e1 CURSOR FOR emp_in_dept
END-EXEC
EXEC SQL
OPEN e1 USING :deptnum
END-EXEC
EXEC SQL
FETCH e1 INTO :enum, :last_name
END-EXEC