oracle嵌套存储过程,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;