The FETCH on a scrollable cursor allows specification of a
direction. If there is no more data in the specified fetch
direction this causes the NOT FOUND exception to be raised, as with
the forward-only cursors. In addition to that if the row on which
the cursor is about to position has been deleted and the isolation
level & cursor type allows detecting that, then the exception
SQLSTATE 'HY109' (Row deleted) is raised.
Positioning on a bookmark is done the following way:
A bookmark value should be retrieved using the bookmark() function .
The value returned by that function can be stored, copied and
retrieved. This value can also survive a cursor close and reopen,
even between transactions. How the cursor will behave if a bookmark
from a cursor with different select statement or scroll type is
used for positioning is undefined and should be avoided. On some
occasions it may signal an error, on others it will position on a
wrong or non-existing row. As a general rule bookmark values should
be used only on the cursor from which they are generated.
The cursor should be in opened state. Now a FETCH .. BOOKMARK
bm_value INTO ... can be issued with the bookmark variable.
Bookmarks can serve for persisting the cursor position in an VSP
context. One can imagine a VSP page which on it's first go will
execute a cursor and will show the first so-many rows. Then it can
retrieve the bookmark value of the last displayed row, persist it
somehow (for example as an HTTP session variable), then close the
cursor and exit. On each subsequent hit it will open again the same
cursor, position on the bookmark persisted and return the next,
previous, first or last so-many rows.