关于有关联的查询的游标问题

[ShaB]

create or replace procedure getbm as
v_sheng varchar2(10):='';
cursor c_sheng is select * from sheng;
cursor c_shi; //请注意这个游标
begin
flag:=1;
c_sheng.open;
loop
exit when c_sheng%notfound;
--我想在这里插入 select * from shi where shi.sheng=sheng.id
--并将结果保存在c_shi这个游标里，应该怎么写？
--嵌套的游标
fetch c_sheng into v_sheng;
end loop;
c_sheng.close;
end getbm;

[gaobh]

中间一段可以通过存储过程实现

[TwinkleCrystals]

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;

[beckhambobo]

两种解决方案，不知楼主是否要哪种:
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;
/

[ShaB]

能否解释一下啊？

[TwinkleCrystals]

不知您哪里看不懂呢？
提出问题来 我回答你

[ShaB]

为什么需要动态的游标呢？

[TwinkleCrystals]

因为c_shi这个游标的sql语句中包含了变量
所以使用动态游标，也就是带参数的游标。