Each cursor has four attributes: %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. These attributes are used to test the cursor status. This topic describes these attributes and their examples.
%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, TRUE
of the BOOLEAN data type is returned. Otherwise, FALSE
is returned.
%ISOPEN
is used in the following example:
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
operation is performed on the cursor.
cursor_name%FOUND
cursor_name
is the name of the cursor. If a row is retrieved from the result set of the cursor
after a FETCH
operation is performed, TRUE
of the BOOLEAN data type is returned.
After the last row of the result set is fetched in a FETCH
operation, %FOUND
returns FALSE
due to the next FETCH
operation. FALSE
is also returned after the first FETCH
operation if the result set has no rows.
If %FOUND
is referenced on a cursor before the cursor is opened or after the cursor is closed,
an INVALID_CURSOR exception occurs.
If the cursor is open and %FOUND
is referenced before the first FETCH
operation, %FOUND returns null.
%FOUND
is used in the following example:
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 stored procedure is invoked, the following output appears:
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. If a row is retrieved from the result set of the cursor
after a FETCH
operation, FALSE
of the BOOLEAN data type is returned.
After the last row of the result set is fetched in a FETCH
operation, %NOTFOUND
returns TRUE
due to the next FETCH
operation. TRUE
is also returned after the first FETCH
operation if the result set has no rows.
If %NOTFOUND
is referenced on a cursor before the cursor is opened or after the cursor is closed,
an INVALID_CURSOR exception occurs.
If the cursor is open and %NOTFOUND
is referenced before the first FETCH
operation, %NOTFOUND returns null.
%NOTFOUND
is used in the following example:
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;
This stored procedure generates the same output as the preceding example when the stored procedure is 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 that indicates the number of rows that have been fetched
from the specified cursor in a FETCH
operation by now.
cursor_name%ROWCOUNT
cursor_name
is the name of the cursor. For this cursor, %ROWCOUNT
returns the number of rows that have been retrieved by now. After the last row is
retrieved, %ROWCOUNT
is still specified as the total number of rows that are returned before the cursor
is closed. In this case, if %ROWCOUNT
is referenced, an INVALID_CURSOR exception occurs.
If %ROWCOUNT
is referenced on a cursor before the cursor is opened or after the cursor is closed,
an INVALID_CURSOR exception occurs.
If the cursor has been opened and %ROWCOUNT
is referenced before the first FETCH
operation, %ROWCOUNT returns 0. %ROWCOUNT
also returns 0 after the first FETCH
operation when the result set has no rows.
%ROWCOUNT
is used in the following example:
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 stored procedure generates the total number of rows that are retrieved at the end of the employee list, as shown in the following example:
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
**********************
14 rows were retrieved
Summary of cursor states and attributes
The following table summarizes the possible cursor states and the values that are 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 and Before the first FETCH | True | Null | Null | 0 |
After the first successful FETCH | True | True | False | 1 |
After the nth successful FETCH (last row) | True | True | False | n |
After the (n+1)th FETCH (after the last row) | True | False | True | n |
After CLOSE | False | INVALID_CURSOR exception | INVALID_CURSOR exception | INVALID_CURSOR exception |