Every explicit cursor and cursor variable has four attributes, each of which returns useful information about the execution of a data manipulation statement.
Keyword and Parameter Descriptions
An explicit cursor previously declared within the current scope.
A PL/SQL cursor variable (or parameter) previously declared within the current scope.
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.
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The data type 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.
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
.
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.
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 were 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, 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.