Oracle PL/SQL - Checking the status of implicit cursors


%ISOPEN showed when the cursor was opened;

%ROWCOUNT showed the number of currently fetched rows; and

%NOTFOUND showed when there were no more rows to fetch.

If you use the %FOUND, %NOTFOUND, and %ROWCOUNT cursor variables before the cursor is opened or after the cursor is closed, they will raise an exception.

Values of %FOUND, %NOTFOUND, and %ROWCOUNT are changed after every fetch.

If there are no more rows to fetch, %ROWCOUNT keeps the number of successfully fetched records until the cursor is closed.

Cursor variables are wonderful tools for knowing exactly how many records were processed and whether any were processed at all.


SQL> drop table emp;

Table dropped.--   w w w  . ja va  2s  .c o m

Elapsed: 00:00:00.02
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL> begin
  2       update emp set sal=sal*1;
  4       DBMS_OUTPUT.put_line('Processed:'||sql%rowcount);
  6       if sql%FOUND then
  7           DBMS_OUTPUT.put_line('Found=true');
  8       else
  9           DBMS_OUTPUT.put_line('Found=false');
 10       end if;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Related Topic