SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-175
Cursors
Cursors
A cursor is a named mechanism defined by a SELECT statement and used in a host
language program. An opened cursor can be thought of as scanning the set of records
specified by the SELECT operation. The program processes a cursor like a sequential
file, fetching rows one by one. The row being fetched is at the current position of the
cursor. The program can use the current cursor position to designate a row to delete or
update. A cursor name is an SQL identifier.
Operations for each cursor used must execute in this order:
1. DECLARE defines the cursor.
2. OPEN determines the result table to fill the cursor and, for audited tables or views,
associates the cursor with a TMF transaction. The program must set values of host
variables or parameters in the cursor definition before the OPEN.
3. FETCH fills the cursor on the first fetch and then locks rows according to the
access specified on the SELECT statement associated with the cursor. If a sort is
required, all rows in the result table might be retrieved at this time and placed in a
temporary, sorted table.
4. DELETE or UPDATE WHERE CURRENT deletes or updates the row at the current
position of the cursor.
5. CLOSE (or FREE RESOURCES) releases the result table established on the
OPEN.
A loop can execute multiple sequences of operations 3 and 4. Operation 5 can be
performed any time after operation 2.
If the cursor locks or updates an audited table, the FETCH operation and subsequent
cursor operations must be within a TMF transaction.
A process that uses a cursor must have read authority for tables and protection views
referred to in the SELECT associated with the cursor. If the cursor refers to a
shorthand view, the process must have read authority for tables or protection views
underlying the shorthand view. If the cursor declaration specifies FOR UPDATE, the
process must also have write authority for the referenced table, protection view, and
underlying table of a view. SQL checks authority to use a cursor when you execute an
OPEN statement.
If you use a cursor to locate rows to delete without specifying FOR UPDATE in the
declaration, SQL checks only the read authority when the OPEN executes, although
the delete requires write authority. SQL checks for write authority when the DELETE
executes. If your program is having problems contending for data access with other
users, you can specify the IN EXCLUSIVE MODE clause on the SELECT statement in
the cursor declaration so that SQL does not have to escalate the lock when an
UPDATE or DELETE executes. If, however, your program is reading records
concurrently accessed by a cursor defined with an IN EXCLUSIVE MODE clause, your
program must wait for access.