You can manipulate cursors using the OPEN,
FETCH, and CLOSE statements. When you need to get information about the current
status of your cursor, or the result of the last fetch from a cursor, you will
access cursor attributes.
While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.
Attribute
|
Explanation
|
%ISOPEN
|
- Returns TRUE if the cursor is open, FALSE if the cursor
is closed.
|
%FOUND
|
- Returns INVALID_CURSOR if cursor is declared, but not
open; or if cursor has been closed.
-
Returns NULL if cursor is open, but fetch has not been executed.
-
Returns TRUE if a successful fetch has been executed.
-
Returns FALSE if no row was returned.
|
%NOTFOUND
|
- Returns INVALID_CURSOR if cursor is declared, but not
open; or if cursor has been closed.
-
Return NULL if cursor is open, but fetch has not been executed.
-
Returns FALSE if a successful fetch has been executed.
-
Returns TRUE if no row was returned.
|
%ROWCOUNT
|
- Returns INVALID_CURSOR if cursor is declared, but not
open; or if cursor has been closed.
-
Returns the number of rows fetched.
-
The ROWCOUNT attribute doesn't give the real row count until you have
iterated through the entire cursor. In other words, you shouldn't rely on
this attribute to tell you how many rows are in a cursor after it is opened.
|
Example:
DECLARE
CURSOR emps
IS
SELECT *
FROM employees
WHERE ROWNUM < 6
ORDER BY 1;
emp employees%ROWTYPE;
row NUMBER := 1;
BEGIN
OPEN emps;
FETCH emps INTO emp;
LOOP
IF emps%FOUND
THEN
DBMS_OUTPUT.put_line (
'Looping over record ' || row || ' of ' || emps%ROWCOUNT);
FETCH emps INTO emp;
row := row + 1;
ELSIF emps%NOTFOUND
THEN
EXIT;
END IF;
END LOOP;
IF emps%ISOPEN
THEN
CLOSE emps;
END IF;
END;
/
