Oracle Plsql Cursor Attributes
Oracle / PLSQL: 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. |
Below is an example of how you might use the %NOTFOUND attribute.
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;