Cursor Attributes
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 Attributes".
The implicit cursor
SQL
has additional attributes, %BULK_ROWCOUNT
and %BULK_EXCEPTIONS
. For more information, see "SQL Cursor".Syntax
Text description of the illustration cursor_attribute.gif
Keyword and Parameter Description
cursor_name
This identifies an explicit cursor previously declared within the current scope.
cursor_variable_name
This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.
%FOUND Attribute
This is 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
yields NULL
. Thereafter, it yields TRUE
if the last fetch returned a row, or FALSE
if the last fetch failed to return a row.host_cursor_variable_name
This identifies 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
This is a cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open,
cursor_name%ISOPEN
yields TRUE
; otherwise, it yields FALSE
.%NOTFOUND Attribute
This is 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
yields NULL
. Thereafter, it yields FALSE
if the last fetch returned a row, or TRUE
if the last fetch failed to return a row.%ROWCOUNT Attribute
This is 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
yields 0. Thereafter, it yields the number of rows fetched so far. The
number is incremented if the latest fetch returned a row.Usage Notes
The cursor attributes apply to every cursor or cursor variable. So, 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
. So, if FETCH
never executes successfully, the loop is never exited. That is because the EXIT
WHEN
statement executes only if its WHEN
condition is true. 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.
Examples
The PL/SQL block below uses
%FOUND
to select an action. The IF
statement either inserts a row or exits the loop unconditionally.-- available online in file 'examp12' DECLARE CURSOR num1_cur IS SELECT num FROM num1_tab ORDER BY sequence; CURSOR num2_cur IS SELECT num FROM num2_tab ORDER BY sequence; num1 num1_tab.num%TYPE; num2 num2_tab.num%TYPE; pair_num NUMBER := 0; BEGIN OPEN num1_cur; OPEN num2_cur; LOOP -- loop through the two tables and get pairs of numbers FETCH num1_cur INTO num1; FETCH num2_cur INTO num2; IF (num1_cur%FOUND) AND (num2_cur%FOUND) THEN pair_num := pair_num + 1; INSERT INTO sum_tab VALUES (pair_num, num1 + num2); ELSE EXIT; END IF; END LOOP; CLOSE num1_cur; CLOSE num2_cur; END;
The next example uses the same block. However, instead of using
%FOUND
in an IF
statement, it uses %NOTFOUND
in an EXIT
WHEN
statement.-- available online in file 'examp13' DECLARE CURSOR num1_cur IS SELECT num FROM num1_tab ORDER BY sequence; CURSOR num2_cur IS SELECT num FROM num2_tab ORDER BY sequence; num1 num1_tab.num%TYPE; num2 num2_tab.num%TYPE; pair_num NUMBER := 0; BEGIN OPEN num1_cur; OPEN num2_cur; LOOP -- loop through the two tables and get -- pairs of numbers FETCH num1_cur INTO num1; FETCH num2_cur INTO num2; EXIT WHEN (num1_cur%NOTFOUND) OR (num2_cur%NOTFOUND); pair_num := pair_num + 1; INSERT INTO sum_tab VALUES (pair_num, num1 + num2); END LOOP; CLOSE num1_cur; CLOSE num2_cur; END;
In the following example, you use
%ISOPEN
to make a decision:IF NOT (emp_cur%ISOPEN) THEN OPEN emp_cur; END IF; FETCH emp_cur INTO emp_rec;
The following PL/SQL block uses
%ROWCOUNT
to fetch the names and salaries of the five highest-paid employees:-- available online in file 'examp14' DECLARE CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; -- start with highest-paid employee my_ename CHAR(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND); INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c1; END;
In the final example, you use
%ROWCOUNT
to raise an exception if an unexpectedly high number of rows is deleted:DELETE FROM accts WHERE status = 'BAD DEBT'; IF SQL%ROWCOUNT > 10 THEN RAISE out_of_bounds; END IF;
No comments:
Post a Comment