In order to demonstrate some of these, the following table is created…
create table cur_attr_exmpl (
a number,
b varchar2(20)
);
… and filled with the following values:
insert into cur_attr_exmpl values ( 1, 'one' );
insert into cur_attr_exmpl values ( 2, 'two' );
insert into cur_attr_exmpl values ( 10, 'ten' );
insert into cur_attr_exmpl values ( 20, 'twenty' );
insert into cur_attr_exmpl values ( 42, 'fourty-two' );
insert into cur_attr_exmpl values ( 101, 'one hundred and one');
insert into cur_attr_exmpl values (1000, 'thousand' );
%is_open
With %isopen, it is possible to test whether a cursor was opened:
declare
cursor cur is
select b
from cur_attr_exmpl;
b_ cur_attr_exmpl.b%type;
procedure is_cursor_open(txt in varchar2) as begin
dbms_output.put(txt);
if cur%isopen then
dbms_output.put_line('cursor is open');
else
dbms_output.put_line('cursor is not open');
end if;
end is_cursor_open;
begin
is_cursor_open('First test: ');
open cur;
is_cursor_open('Second test: ');
fetch cur into b_;
is_cursor_open('Third test: ');
close cur;
is_cursor_open('Fourth test: ');
end;
/
This prints:
First test: cursor is not open
Second test: cursor is open
Third test: cursor is open
Fourth test: cursor is not open
%found
%found returns true if the last fetch operation on the cursor fetched a row.
If %found is used on a cursor that is not open (%open is false), an ORA-01001: invalid cursor is raised. %notfound returns null is returned:
declare
cursor cur is
select b
from cur_attr_exmpl
where a between 40 and 140;
b_ cur_attr_exmpl.b%type;
begin
begin
if cur%found then
dbms_output.put_line('Cursor found');
end if;
exception
when invalid_cursor then
dbms_output.put_line('%found only possible if cursor is open');
when others then
raise;
end;
open cur;
if cur%found then
dbms_output.put_line('Cursor opened and %found true');
elsif cur%found is null then
dbms_output.put_line('Cursor opened and %found null');
else
dbms_output.put_line('Cursor opened and %found false');
end if;
loop
fetch cur into b_;
if cur%found then
dbms_output.put_line('fetched: ' || b_);
else
exit;
end if;
end loop;
close cur;
end;
/
This prints:
%found only possible if cursor is open
Cursor opened and %found null
fetched: fourty-two
fetched: one hundred and one
%notfound
%notfound returns true if the last fetch operation on the cursor did not fetch a row.
If %notfound is used on a cursor that is not open (%open being false), an ORA-01001: invalid cursor is raised.
declare
cursor cur is
select b
from cur_attr_exmpl
where a between 40 and 140;
b_ cur_attr_exmpl.b%type;
begin
begin
if cur%notfound then
dbms_output.put_line('Cursor notfound');
end if;
exception when others then
if sqlcode = -1001 then -- ORA-01001: invalid cursor
dbms_output.put_line('%notfound only possible if cursor is open');
else
raise;
end if;
end;
open cur;
if cur%notfound then
dbms_output.put_line('Cursor opened and %notfound true');
elsif cur%notfound is null then
dbms_output.put_line('Cursor opened and %notfound null');
else
dbms_output.put_line('Cursor opened and %notfound false');
end if;
loop
fetch cur into b_;
if cur%notfound then
exit;
else
dbms_output.put_line('fetched: ' || b_);
end if;
end loop;
close cur;
end;
/
The above prints:
%notfound only possible if cursor is open
Cursor opened and %notfound null
fetched: fourty-two
fetched: one hundred and one
%rowcount
%rowcount returns the number of rows that have been fetched so far. So, it increases with each fetch:
declare
cursor cur is
select b
from cur_attr_exmpl
where a between 9 and 90;
b_ cur_attr_exmpl.b%type;
begin
open cur;
loop
fetch cur into b_;
if cur%found then
dbms_output.put_line(cur%rowcount || ': fetched: ' || b_);
else
exit;
end if;
end loop;
dbms_output.put_line('Totally ' || cur%rowcount || ' rows fetched');
close cur;
end;
/
%bulk_rowcount similar to %rowcount, but is used in bulk collects.
declare
cursor cur is
select b
from cur_attr_exmpl
where a between 9 and 90;
type b_t is table of cur_attr_exmpl.b%type;
b_ b_t;
begin
open cur;
fetch cur bulk collect into b_;
for i in 1 .. b_.count loop
dbms_output.put_line(' ' || b_(i));
end loop;
dbms_output.put_line('');
dbms_output.put_line('Totally ' || cur%rowcount || ' rows fetched');
close cur;
end;
/