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;