Each cursor has a set of attributes associated with it that allows the program to test the state of the cursor. These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. These attributes are described in the following topics.
%ISOPEN
The %ISOPEN attribute is used to test whether a cursor is open.
cursor_name%ISOPEN
cursor_name is the name of the cursor. If the cursor is open, a BOOLEAN data type of TRUE is returned. Otherwise, FALSE is returned.
The following example uses %ISOPEN:
CREATE OR REPLACE PROCEDURE cursor_example
IS
...
CURSOR emp_cur_1 IS SELECT * FROM emp;
...
BEGIN
...
IF emp_cur_1%ISOPEN THEN
NULL;
ELSE
OPEN emp_cur_1;
END IF;
FETCH emp_cur_1 INTO...
...
END;
%FOUND
The %FOUND attribute is used to test whether a row is retrieved from the result set of the specified cursor after a FETCH on the cursor.
cursor_name%FOUND
cursor_name is the name of the cursor for which a BOOLEAN data type of TRUE will be returned if a row is retrieved from the result set of the cursor after a FETCH.
After the last row of the result set has been FETCHed, the next FETCH results in %FOUND returning FALSE. FALSE is also returned after the first FETCH if the result set has no rows to begin with.
Referencing %FOUND on a cursor before it is opened or after it is closed results in an INVALID_CURSOR exception being thrown.
%FOUND returns null if it is referenced when the cursor is open, but before the first FETCH.
The following example uses %FOUND:
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('----- -------');
FETCH emp_cur_1 INTO v_emp_rec;
WHILE emp_cur_1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
FETCH emp_cur_1 INTO v_emp_rec;
END LOOP;
CLOSE emp_cur_1;
END;
When the previous procedure is invoked, the output appears as follows:
EXEC cursor_example;
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
%NOTFOUND
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 will be returned if a row is retrieved from the result set of the cursor after a FETCH.
After the last row of the result set has been 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 is opened or after it is closed results in an INVALID_CURSOR exception being thrown.
%NOTFOUND returns null if it is referenced when the cursor is open, but before the first FETCH.
The following 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;
Similar to the prior example, this procedure produces the same output when invoked:
EXEC cursor_example;
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
%ROWCOUNT
The %ROWCOUNT attribute returns an integer showing the number of rows FETCHed so far from the specified cursor.
cursor_name%ROWCOUNT
cursor_name is the name of the cursor for which %ROWCOUNT returns the number of rows retrieved thus far. After the last row has been retrieved, %ROWCOUNT remains set to the total number of rows returned until the cursor is closed at which point %ROWCOUNT will throw an INVALID_CURSOR exception if referenced.
Referencing %ROWCOUNT on a cursor before it is opened or after it is closed results in an INVALID_CURSOR exception being thrown.
%ROWCOUNT returns 0 if it is referenced when the cursor is open, but before the first FETCH. %ROWCOUNT also returns 0 after the first FETCH when the result set has no rows to begin with.
The following example uses %ROWCOUNT:
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;
DBMS_OUTPUT.PUT_LINE('**********************');
DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
CLOSE emp_cur_1;
END;
This procedure prints the total number of rows retrieved at the end of the employee list as follows:
EXEC cursor_example;
EMPNO ENAME
----- -------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
**********************
14 rows were retrieved
Summary of cursor states and attributes
The following table summarizes the possible cursor states and the values returned by the cursor attributes.
Cursor state | %ISOPEN | %FOUND | %NOTFOUND | %ROWCOUNT |
---|---|---|---|---|
Before OPEN | False | INVALID_CURSOR exception | INVALID_CURSOR exception | INVALID_CURSOR exception |
After OPEN & Before 1st FETCH | True | Null | Null | 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 | INVALID_CURSOR exception | INVALID_CURSOR exception | INVALID_CURSOR exception |