%NOTFOUND v17
The %NOTFOUND
attribute is the logical opposite of %FOUND
.
<cursor_name>%NOTFOUND
cursor_name
is the name of the cursor for which a BOOLEAN
data type of FALSE
is returned if a row is retrieved from the result set of the cursor after a FETCH
.
After the last row of the result set is fetched, the next FETCH
results in %NOTFOUND
returning TRUE
. TRUE
is also returned after the first FETCH
if the result set has no rows to begin with.
Referencing %NOTFOUND
on a cursor before it's opened or after it's closed results in an INVALID_CURSOR
exception.
%NOTFOUND
returns null
if it's referenced when the cursor is open but before the first FETCH
.
This example uses %NOTFOUND
:
CREATE OR REPLACE PROCEDURE cursor_example IS v_emp_rec emp%ROWTYPE; CURSOR emp_cur_1 IS SELECT * FROM emp; BEGIN OPEN emp_cur_1; DBMS_OUTPUT.PUT_LINE('EMPNO ENAME'); DBMS_OUTPUT.PUT_LINE('----- -------'); LOOP FETCH emp_cur_1 INTO v_emp_rec; EXIT WHEN emp_cur_1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename); END LOOP; CLOSE emp_cur_1; END;
The following is the output from this example:
EXEC cursor_example;
Output
EMPNO ENAME ----- ------ 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER