create or replace procedure getbm as
v_sheng varchar2(10):='';
cursor c_sheng is select * from sheng;
cursor c_shi(v_id number) is
select * from shi where sheng=v_id; //动态游标
begin
flag:=1;
c_sheng.open;
loop
exit when c_sheng%notfound;
fetch c_sheng into v_sheng;
for row_shi in c_shi(v_sheng.id) loop --可以在这里可以使用c_shi游标
...
end loop;
end loop;
c_sheng.close;
end getbm;
v_sheng varchar2(10):='';
cursor c_sheng is select * from sheng;
cursor c_shi(v_id number) is
select * from shi where sheng=v_id; //动态游标
begin
flag:=1;
c_sheng.open;
loop
exit when c_sheng%notfound;
fetch c_sheng into v_sheng;
for row_shi in c_shi(v_sheng.id) loop --可以在这里可以使用c_shi游标
...
end loop;
end loop;
c_sheng.close;
end getbm;
create or replace procedure getbm as
v_sheng varchar2(10):='';
cursor c_sheng is select * from sheng;
cursor c_shi(v_id varchar2) is
select * from shi where sheng=v_id;
begin
--flag:=1;
for v_sheng in c_sheng loop
for v_shi in c_shi(v_sheng.id) loop
.....
end loop;
end loop;
end getbm;
/第二种:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create or replace procedure getbm(p_rc out pkg_test.myrctype)
as
cursor c_sheng is select * from sheng;
--cursor c_shi(v_id varchar2) is select * from shi where sheng=v_id;
str varchar2(100);
flag number:=0;
begin
for v_sheng in c_sheng loop
if flag=0 then
str:='select * from shi where sheng='||v_sheng.id;
flag:=flag+1;
else
str:=str||' union select * from shi where sheng='||v_sheng.id;
end if;
end loop;
open p_rc for str;
end;
/
.....
end loop;
end loop;
end getbm;
/
所以使用动态游标,也就是带参数的游标。