Post a Comment On: Oracle Help

There are five cursor attributes:
%isopen

%found

%notfound

%rowcount

%bulk_rowcount

------------------------------------------------------------------------------
%isopen
With %isopen it is possible to test whether a cursor was opened:

declare 
      cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
      l_name
begin
      open cur_emp;
      fetch cur_emp  into  l_name;
      if cur_emp%isopen
      then
            log("Cursor is Open")
      end if;
      close cur;
end;

If there are records in EMP table then the message "Cursor is Open" will be logged at the terminal.

------------------------------------------------------------------------------
%found returns true when the last fetch operation on the cursor fetched a row.
%notfound returns true when the last fetch operation on the cursor did not fetch a row.

declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;
begin
       open cur_emp;
       Loop
       if cur_emp%found
       then
              log("Record returned");        
       elsif cur_emp%notfound
              log("No records");
       exit;          
       end if;
       close cur;
end;

If there are 5 records in EMP TABLE then you will get "Record returned" message five times and then
loop will exit logging the message "No records".
%NOTFOUND is mostly used to exit the LOOP for cursor after the last record has been fetched/processed.

---------------------------------------------------
%rowcount returns the number of rows that have been fetched so far

example;
declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
begin
       open cur_emp;
       loop
       if cur%found then     
                    dbms_output.put_line(cur%rowcount);
      else
                    exit;
      end if;
      end loop;
end;

%bulk_rowcount is similar to %rowcount, but is used in bulk collects.