You can use two keywords to specify sensitivity when declaring a cursor:
insensitive
semi_sensitive
If you declare a cursor insensitive, the cursor shows only the result set as it is when the cursor is opened; data changes in the underlying tables are not visible. If you declare it semi_sensitive, some changes in the base tables made since opening the cursor may appear in the result set. Data changes may or may not be visible to a semi-sensitive cursor.
The default value is semi_sensitive.
There are also two keywords to specify scrollability:
scroll
no scroll
If you designate a cursor as scrollable (in other words, declare it using scroll), you can fetch the result rows either sequentially or non-sequentially, and you can scan the result set repeatedly. If the option no scroll appears in the cursor declaration, the cursor is nonscrollable; the result set is displayed in a forward-only direction, one row at a time.
If you specify neither attribute, the default value is no scroll.
Think of a cursor as a “handle” on the result set of a select statement. The cursor can be fetched either sequentially or non-sequentially, depending on the cursor’s scrollability.
A nonscrollable cursor can be fetched only in a forward direction; you cannot go back to a row that is already fetched. A scrollable cursor can be fetched in either direction, backward or forward.
A scrollable cursor allows you to set the position of the cursor anywhere in the cursor result set as long as the cursor is open, by specifying the option first, last, absolute, next, prior, or relative in a fetch statement.
To fetch the last row in a result set, enter:
fetch last [from] <cursor_name>
Or, to select a specific row in the result set, in this example the 500th row, enter:
All scrollable cursors are read-only. Any cursor that can be updated is nonscrollable.
