1、无数据查询,对游标无效
create or replace procedure noData is
v_val varchar2(100);
begin
select val1 into v_val from test where rownum = 1;
dbms_output.put_line(v_val);
exception
when no_data_found then
dbms_output.put_line('no data');
end noData;
2、嵌套的游标
create or replace procedure nestedCustor is
cursor CUR_master is
SELECT val1 from test;
cursor cur_new is
select val2 from new;
v_test varchar2(100);
v_new varchar2(100);
begin
open CUR_master;
FETCH CUR_master
into v_test;
while CUR_master%found loop
open cur_new;
fetch cur_new
into v_new;
while cur_new%found loop
dbms_output.put_line(v_new);
fetch cur_new
into v_new;
end loop;
close cur_new;
dbms_output.put_line(v_test);
FETCH CUR_master
into v_test;
end loop;
CLOSE CUR_master;
exception
when others then
dbms_output.put_line('error');
dbms_output.put_line(substr(SQLERRM, 1, 255));
if (cur_new%isopen) then
close cur_new;
end if;
if (CUR_master%isopen) then
close CUR_master;
end if;
end nestedCustor;