A scrollable cursor is cursor that can be moved in both a forward and a backward direction.
Scrollable cursors can be either row-positioned or rowset-positioned.
About this task
When you open any cursor, the cursor is positioned before the first row of the result table.
You move a scrollable cursor around in the result table by specifying a fetch orientation keyword in a FETCH statement.
A fetch orientation keyword indicates the absolute or relative position of the cursor when the FETCH statement is executed.
The following table lists the fetch orientation keywords that you can specify and their meanings.
These keywords apply to both row-positioned scrollable cursors and rowset-positioned scrollable cursors.
Table 1. Positions for a scrollable cursor
| Keyword in FETCH statement |
Cursor position when FETCH is executed1 |
| BEFORE |
Before the first row |
| FIRST or ABSOLUTE +1 |
On the first row |
| LAST or ABSOLUTE -1 |
On the last row |
| AFTER |
After the last row |
| ABSOLUTE2 |
On an absolute row number, from before the first row forward or from after the last row backward |
| RELATIVE2 |
On the row that is forward or backward a relative number of rows from the current row |
| CURRENT |
On the current row |
| PRIOR or RELATIVE -1 |
On the previous row |
| NEXT |
On the next row (default) |
Notes:
- The cursor position applies to both row position and rowset position, for example, before the first row or before the first rowset.
- For more information about ABSOLUTE and RELATIVE, see the FETCH statement syntax.
|
Example:
To use the cursor that is declared in Types of cursors to
fetch the fifth row of the result table, use a FETCH statement like
this:
EXEC SQL FETCH ABSOLUTE +5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;
To fetch the fifth row from the end of the result table, use this FETCH statement:
EXEC SQL FETCH ABSOLUTE -5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;
- Comparison of scrollable cursors
Whether a scrollable cursor can view the changes that are made to the data by other processes or cursors depends on how the cursor is declared.
It also depends on the type of fetch operation that is executed.
- Scrolling through a table in any direction
Use a scrollable cursor to move through the table in both a forward and a backward direction.
- Determining the number of rows in the result table for a static scrollable cursor
You can determine how many rows are in the result table of an INSENSITIVE or SENSITIVE STATIC scrollable cursor.
- Removing a delete hole or update hole
If you try to fetch data from a delete hole or an update hole, DB2® issues an SQL warning.
If you try to update or to delete a delete hole or delete an update hole, DB2 issues an SQL error.
