oracle学习笔记(二)分享之存储过程嵌套游标例子

create or replace procedure aa1 is
v_aa varchar2(20);
v_bb varchar2(20);
va_aa varchar2(20);
va_bb varchar2(20);
va_number_aa number;
message varchar2(60);
CURSOR aas is
SELECT DISTINCT aa.aa, aa.bb from aa;
CURSOR aaas is
SELECT DISTINCT aaa.aa, aaa.bb from aaa where aaa.aa = v_aa;
begin
OPEN aas;
FETCH aas INTO v_aa, v_bb;
WHILE aas%FOUND LOOP
OPEN aaas;
FETCH aaas INTO va_aa, va_bb;
WHILE aaas%FOUND LOOP
begin
va_number_aa :=to_number(va_bb);
exception
when others then
message:='ddddddddddddd';
end;

commit;
FETCH aaas INTO va_aa, va_bb;
END LOOP;
CLOSE aaas;
FETCH aas INTO v_aa, v_bb;
END LOOP;
CLOSE aas;
end aa1;

---------------------------------------------------
declare
v_id0000 char(2);
cursor c1 is select id00,name00 from a;
cursor c2 is select * from b where id0000=v_id0000;
begin
for i in c1 loop
v_id00:=i.id0000;
for j in c2 loop
null;
end loop;
end loop;
end;