Cursor attributes (PL/SQL)
Each cursor has a set of attributes that enables an application program to test the state of the cursor.
These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT.
- %ISOPEN
- This attribute is used to determine whether a cursor is in the open state. When a cursor is passed as a parameter to a function or procedure, it is useful to know (before attempting to open the cursor) whether the cursor is already open.
- %FOUND
- This attribute is used to determine whether a cursor contains
rows after the execution of a FETCH statement. If FETCH statement
execution was successful, the %FOUND attribute has a value of true.
If FETCH statement execution was not successful, the %FOUND attribute
has a value of false. The result is unknown when:
- The value of cursor-variable-name is null
- The underlying cursor of cursor-variable-name is not open
- The %FOUND attribute is evaluated before the first FETCH statement was executed against the underlying cursor
- FETCH statement execution returns an error
The %FOUND attribute provides an efficient alternative to using a condition handler that checks for the error that is returned when no more rows remain to be fetched.
- %NOTFOUND
- This attribute is the logical opposite of the %FOUND attribute.
- %ROWCOUNT
- This attribute is used to determine the number of rows that have been fetched since a cursor was opened.
Table 1 summarizes the attribute values that are associated with certain cursor events.
Cursor attribute | %ISOPEN | %FOUND | %NOTFOUND | %ROWCOUNT |
---|---|---|---|---|
Before OPEN | False | Undefined | Undefined | Cursor not openexception |
After OPEN and before 1st FETCH | True | Undefined | Undefined | 0 |
After 1st successful FETCH | True | True | False | 1 |
After nth successful FETCH (last row) | True | True | False | n |
After n+1st FETCH (after last row) | True | False | True | n |
After CLOSE | False | Undefined | Undefined | Cursor not openexception |