A cursor is used in an application program
to select a set of rows and then process that returned data one row
at a time. When a SELECT statement in an embedded SQL application
returns multiple rows of data, you need a mechanism that makes this
returned data or result set available to your application program,
one row after another.
A cursor is like a name that is associated with a query. A cursor
is created using the DECLARE CURSOR statement which defines the name
of the cursor and specifies its associated query. Three additional
SQL statements operate on cursors.
- OPEN
- Performs the query which builds the result set and prepares the
cursor for retrieval of the first row.
- FETCH
- Retrieves one row of the result set and assigns the values of
that row to target variables. Fetches are usually executed repeatedly
until all rows of the result set have been retrieved.
- CLOSE
- Terminates the cursor and releases any resources that it was using.
If needed again, the cursor can be reopened.
You can declare cursors as held or not held, returnable or not
returnable, and scrollable or not scrollable.
- holdability
- A held cursor does not close after a commit operation. A cursor
that is not held closes after a commit operation.
- returnability
- A returnable cursor returns the result set back to the caller
of the procedure or client application.
- scrollability
- A non-scrollable cursor moves sequentially forward through a result
set and each row can only be accessed once. A scrollable cursor can
be moved randomly through the result set using the FETCH statement.
Note that scrollable cursors are supported only in CLI, JDBC, and
SQLJ applications.
DB2 10.5 for Linux, UNIX, and Windows