在过程中 ,需要在循环里 定义游标 , 然后在循环里 判断游标里是否有记录,有记录执行一条语句A,无记录执行另外一条语句B。
但是当游标里有记录后 在循环里 始终执行有记录的 语句A, 请教如何清空游标里的数据。
create or replace procedure proc_CHECK_CCB_RENT is
str_location varchar2(100);
str_buildarea number;
str_Mortgage number;
str_Attachment number;
begin
for release in (select cqzbh,cqrxm,cqrzjhm,fwbh from SH_CONTRACT@LINK_CCB_RENT t) loop
Declare
cursor u_cur is
select location,build_area,Isnot_Mortgage,Isnot_Attachment from V_ALL_REGINFO where r_name = release.cqrxm
and zjhm=release.cqrzjhm;
begin
open u_cur;
if u_cur%notfound then /* 使用 u_cur%Rowcount 始终为0 */
update SH_CONTRACT@LINK_CCB_RENT a set a.HYZT='失败' where a.FWBH=release.fwbh;
else
fetch u_cur into str_location,str_buildarea,str_Mortgage,str_Attachment;
update SH_CONTRACT@LINK_CCB_RENT a set a.HYZT='成功',a.FWMJ=str_buildarea,a.FWZL=str_location,a.SFCZCF=to_char(str_Attachment), a.SFDY=to_char(str_Mortgage)
where a.FWBH=release.fwbh;
end if;
close u_cur;
end;
end loop;
commit;
end proc_CHECK_CCB_RENT;
但是当游标里有记录后 在循环里 始终执行有记录的 语句A, 请教如何清空游标里的数据。
create or replace procedure proc_CHECK_CCB_RENT is
str_location varchar2(100);
str_buildarea number;
str_Mortgage number;
str_Attachment number;
begin
for release in (select cqzbh,cqrxm,cqrzjhm,fwbh from SH_CONTRACT@LINK_CCB_RENT t) loop
Declare
cursor u_cur is
select location,build_area,Isnot_Mortgage,Isnot_Attachment from V_ALL_REGINFO where r_name = release.cqrxm
and zjhm=release.cqrzjhm;
begin
open u_cur;
if u_cur%notfound then /* 使用 u_cur%Rowcount 始终为0 */
update SH_CONTRACT@LINK_CCB_RENT a set a.HYZT='失败' where a.FWBH=release.fwbh;
else
fetch u_cur into str_location,str_buildarea,str_Mortgage,str_Attachment;
update SH_CONTRACT@LINK_CCB_RENT a set a.HYZT='成功',a.FWMJ=str_buildarea,a.FWZL=str_location,a.SFCZCF=to_char(str_Attachment), a.SFDY=to_char(str_Mortgage)
where a.FWBH=release.fwbh;
end if;
close u_cur;
end;
end loop;
commit;
end proc_CHECK_CCB_RENT;
否则会死循环,一直update