Cursor (databases) - Scrollable Cursors

Scrollable Cursors

Programmers may declare cursors as scrollable or not scrollable. The scrollability indicates the direction in which a cursor can move.

With a non-scrollable (or forward-only) cursor, you can FETCH each row at most once, and the cursor automatically moves to the next row. After you fetch the last row, if you fetch again, you will put the cursor after the last row and get the following code: SQLSTATE 02000 (SQLCODE +100).

A program may position a scrollable cursor anywhere in the result set using the FETCH SQL statement. The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL, although different language bindings like JDBC may apply a different default.

DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT ... FROM ...

The target position for a scrollable cursor can be specified relatively (from the current cursor position) or absolutely (from the beginning of the result set).

FETCH FROM cursor_name FETCH ABSOLUTE n FROM cursor_name FETCH RELATIVE n FROM cursor_name

Scrollable cursors can potentially access the same row in the result set multiple times. Thus, data modifications (insert, update, delete operations) from other transactions could have an impact on the result set. A cursor can be SENSITIVE or INSENSITIVE to such data modifications. A sensitive cursor picks up data modifications impacting the result set of the cursor, and an insensitive cursor does not. Additionally, a cursor may be ASENSITIVE, in which case the DBMS tries to apply sensitivity as much as possible.