Oracle 光标嵌套使用出现的问题,高手请近!!
存储过程如下:
create or replace procedure aaaa
as
p_fund_begin number;
cursor cur_aff is
select A.cause_code,A.unit_code from aff_adjust A where A.cause_code='12';
rs1 cur_aff%rowtype;
cursor cur_period(p_no varchar2) is
select period_no from epd_period
where code=p_no;
rs2 cur_period%rowtype;
p_cause_no varchar2(10);
begin
OPEN cur_aff;
fetch cur_aff into rs1;
while cur_aff%found loop
insert into bb values('a');--测试
p_cause_no :=rs1.cause_code;
--查询该客户的核算期范围,并依次生成财务帐;
OPEN cur_period(p_cause_no);
fetch cur_period into rs2;
while cur_period%found loop
..处理过程
fetch cur_period into rs2;
end loop;
CLOSE cur_period;
fetch cur_aff into rs1;
end loop;
CLOSE cur_aff;
end aaaa;
/
---
两个光标查询结果都存在多条纪录,
但嵌套使用时,第一个光标却只执行了一次就结束了
(bb表中有一条纪录)
如果改成下面的形式就可以正常循环:
create or replace procedure aaaa
as
p_fund_begin number;
cursor cur_aff is
select A.cause_code,A.unit_code from aff_adjust A where A.cause_code='12';
rs1 cur_aff%rowtype;
cursor cur_period(p_no varchar2) is
select period_no from epd_period
where code=p_no;
rs2 cur_period%rowtype;
p_cause_no varchar2(10);
begin
OPEN cur_aff;
fetch cur_aff into rs1;
while cur_aff%found loop
insert into bb values('a');--测试
fetch cur_aff into rs1;
end loop;
CLOSE cur_aff;
end aaaa;
/
这是什么原因?怎么解决??
谢谢!