This example uses a simple query to illustrate how different cursors respond to a row in the result set being deleted.
Consider the following sequence of events:
An application opens a cursor on the following query against the sample database.
SELECT EmployeeID, Surname FROM Employees ORDER BY EmployeeID;
EmployeeID | Surname |
---|---|
102 | Whitney |
105 | Cobb |
160 | Breault |
... | ... |
The application fetches the first row through the cursor (102).
The application fetches the next row through the cursor (105).
A separate transaction deletes employee 102 (Whitney) and commits the change.
The results of cursor actions in this situation depend on the cursor sensitivity:
Action | Result |
---|---|
Fetch previous row | Returns the original copy of the row (102). |
Fetch the first row (absolute fetch) | Returns the original copy of the row (102). |
Fetch the second row (absolute fetch) | Returns the unchanged row (105). |
Action | Result |
---|---|
Fetch previous row | Returns Row Not Found. There is no previous row. |
Fetch the first row (absolute fetch) | Returns row 105. |
Fetch the second row (absolute fetch) | Returns row 160. |
Action | Result |
---|---|
Fetch previous row | Returns No current row of cursor. There is a hole in the cursor where the first row used to be. |
Fetch the first row (absolute fetch) | Returns No current row of cursor. There is a hole in the cursor where the first row used to be. |
Fetch the second row (absolute fetch) | Returns row 105. |
The benefit of asensitive cursors is that for many applications, sensitivity is unimportant. In particular, if you are using a forward-only, read-only cursor, no underlying changes are seen. Also, if you are running at a high isolation level, underlying changes are disallowed.