A cursor can either be open or closed.
An open cursor can point to a row from the result table returned by its associated SELECT. This row is said to be the current row.
While the relational model does not support the concept of ordered rows, the mechanism of processing a result set one row at a time, as executed by non-relational programming languages, requires a redefinition for this situation only.
- Before a row
The cursor is positioned before the first row if the cursor was opened but no rows were fetched.
- On a row
The cursor is on a row following a fetch of the row.
When a cursor is pointing at a row, that row is referred to as the current row of the cursor.
- After the last row in a result set
When there are no further rows in the result set to fetch, the cursor points immediately after the last row in the retrieved set.
Leave the result set cursors open to return the result sets to the caller or client. If the stored procedure closes the result set cursors, the result sets are deleted and not returned. The result sets are returned in the order they were opened.