A cursor is defined with a DECLARE CURSOR statement. During definition, the cursor is assigned a cursor description. The cursor description is the query expression that defines the cursor table.
The query expression is specified directly in the DECLARE CURSOR statement for static cursors and local cursors (in routines). In the case of dynamic cursors, it is created when the program is executed (see section “Dynamic cursor descriptions”).
The following characteristics of the cursor can be specified in the definition:
Positioning
There are two kinds of cursors: scrollable cursors and sequential cursors.
A scrollable cursor can be positioned freely on any row in the cursor table. It is defined by specifying the keyword SCROLL.
A cursor defined with NO SCROLL can only be positioned on the next row in the cursor table.
Lifetime
If a cursor is to remain open after the end of a transaction, this can be specified using the WITH HOLD clause. The only prerequisite is that the cursor must be open prior to completion of the transaction. The WITH HOLD clause is not permitted for local cursors (in routines).
A cursor defined with WITHOUT HOLD is closed implicitly once the transaction has completed. WITHOUT HOLD is the default value.
Sorting
An ORDER BY clause can be specified in the cursor description indicating that the rows in the cursor table are to be sorted.
Number of hits
A FETCH FIRST max ROWS ONLY clause for limiting the number of hits supplied can only be specified in the cursor description.
Updatability
A cursor is updatable if the query expression used to define the cursor is updatable (see section “Updatability of query expressions”), and neither SCROLL nor ORDER BY nor the FOR READ ONLY clause was specified in the cursor declaration.
An updatable cursor references exactly one base table. Individual rows in this table can be deleted or updated using the cursor position to indicate the appropriate row. The FOR UPDATE clause in the cursor description can be used for updatable cursors to specify the columns whose values can be updated.
If a cursor is not updatable, it can only be used to read rows from the relevant cursor table. A cursor cannot be updated in the case of FETCH FIRST max ROWS ONLY, either.