Every explicit cursor and cursor variable has four attributes: %FOUND
, %ISOPEN
%NOTFOUND
, and %ROWCOUNT
. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. For more information, see "Using Cursor Expressions".
The implicit cursor SQL
has additional attributes, %BULK_ROWCOUNT
and %BULK_EXCEPTIONS
. For more information, see "SQL Cursor".
cursor attribute ::=
Keyword and Parameter Description
cursor_name
An explicit cursor previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable (or parameter) previously declared within the current scope.
%FOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%FOUND
returns NULL
. Afterward, it returns TRUE
if the last fetch returned a row, or FALSE
if the last fetch failed to return a row.
host_cursor_variable_name
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
%ISOPEN Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN
returns TRUE
; otherwise, it returns FALSE
.
%NOTFOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND
returns NULL
. Thereafter, it returns FALSE
if the last fetch returned a row, or TRUE
if the last fetch failed to return a row.
%ROWCOUNT Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT
is zeroed. Before the first fetch, cursor_name%ROWCOUNT
returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.
The cursor attributes apply to every cursor or cursor variable. For example, you can open multiple cursors, then use %FOUND
or %NOTFOUND
to tell which cursors have rows left to fetch. Likewise, you can use %ROWCOUNT
to tell how many rows have been fetched so far.
If a cursor or cursor variable is not open, referencing it with %FOUND
, %NOTFOUND
, or %ROWCOUNT
raises the predefined exception INVALID_CURSOR
.
When a cursor or cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set one at a time.
If a SELECT
INTO
statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS
and sets %ROWCOUNT
to 1, not the actual number of rows that satisfy the query.
Before the first fetch, %NOTFOUND
evaluates to NULL
. If FETCH
never executes successfully, the EXIT WHEN
condition is never TRUE
and the loop is never exited. To be safe, you might want to use the following EXIT
statement instead:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
You can use the cursor attributes in procedural statements, but not in SQL statements.
For examples, see the following: