Programmers may declare cursors as scrollable or not scrollable.The scrollability indicates the direction in which a cursor can move.
A cursor can be SENSITIVE or INSENSITIVE to such data modifications.
A sensitive cursor picks up data modifications affecting the result set of the cursor, and an insensitive cursor does not.
With a non-scrollable (or forward-only) cursor, you can 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 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 affect the result set.
Cursors enable manipulation of whole result sets at once.
In this scenario, a cursor enables the rows in a result set to be processed sequentially.
The XQuery language allows cursors to be created using the subsequence() function. Fetching a row from the cursor may result in a network round trip each time.
This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE.
Additionally, a cursor may be INSENSITIVE, in which case the DBMS tries to apply sensitivity as much as possible.