FETCH statement interaction between row and rowset positioning
When you declare a cursor with the WITH ROWSET POSITIONING clause, you can intermix row-positioned FETCH statements with rowset-positioned FETCH statements.
The following table shows the interaction between row and rowset
positioning for a scrollable cursor. Assume that you declare the scrollable
cursor on a table with 15 rows.
| Keywords in FETCH statement | Cursor position when FETCH is executed |
|---|---|
| FIRST | On row 1 |
| FIRST ROWSET | On a rowset of size 1, consisting of row 1 |
| FIRST ROWSET FOR 5 ROWS | On a rowset of size 5, consisting of rows 1, 2, 3, 4, and 5 |
| CURRENT ROWSET | On a rowset of size 5, consisting of rows 1, 2, 3, 4, and 5 |
| CURRENT | On row 1 |
| NEXT (default) | On row 2 |
| NEXT ROWSET | On a rowset of size 1, consisting of row 3 |
| NEXT ROWSET FOR 3 ROWS | On a rowset of size 3, consisting of rows 4, 5, and 6 |
| NEXT ROWSET | On a rowset of size 3, consisting of rows 7, 8, and 9 |
| LAST | On row 15 |
| LAST ROWSET FOR 2 ROWS | On a rowset of size 2, consisting of rows 14 and 15 |
| PRIOR ROWSET | On a rowset of size 2, consisting of rows 12 and 13 |
| ABSOLUTE 2 | On row 2 |
| ROWSET STARTING AT ABSOLUTE 2 FOR 3 ROWS | On a rowset of size 3, consisting of rows 2, 3, and 4 |
| RELATIVE 2 | On row 4 |
| ROWSET STARTING AT ABSOLUTE 2 FOR 4 ROWS | On a rowset of size 4, consisting of rows 2, 3, 4, and 5 |
| RELATIVE -1 | On row 1 |
| ROWSET STARTING AT ABSOLUTE 3 FOR 2 ROWS | On a rowset of size 2, consisting of rows 3 and 4 |
| ROWSET STARTING AT RELATIVE 4 | On a rowset of size 2, consisting of rows 7 and 8 |
| PRIOR | On row 6 |
| ROWSET STARTING AT ABSOLUTE 13 FOR 5 ROWS | On a rowset of size 3, consisting of rows 13, 14, and 15 |
| FIRST ROWSET | On a rowset of size 5, consisting of rows 1, 2, 3, 4, and 5 |