open mycursor;
fetch mycrusor into 变量1,...;
loop
exit when mycrusor%notfound;
if 符合条件 then
fetch mycrusor into 变量1,...;
else
exit;
end if;
end loop;
fetch mycrusor into 变量1,...;
loop
exit when mycrusor%notfound;
if 符合条件 then
fetch mycrusor into 变量1,...;
else
exit;
end if;
end loop;
as
m_jh varchar2(20);
m_ypph number(7,2);
m_yybh varchar2(20);
m_yysd1 number(7,2);
m_yysd2 number(7,2);
m_fnz number(5,2);
m_szt number(5,2);
m_qzz number(5,2);
m_jzz number(5,2);
m_dzz number(5,2);
m_bz varchar2(20);
m_cwmc varchar2(30);
m_yxms varchar2(30);
allcount number(5,2);
a number(5,2);
b1 number(5,2);
b2 number(5,2);
c number(5,2);
d number(5,2);
TI number(5,2);
m_lxhf varchar2(6);
m_zt number(4);
m_csrq date;
m_yylb varchar2(20);
CURSOR c1 Is select YPPH,YYBH from GLGJJYS where BGBH=m_bgbh;
begin
delete from GLGJJ where BGBH=m_bgbh;
open c1;
loop
FETCH c1 into m_ypph,m_yybh;
EXIT WHEN c1%NOTFOUND;
select FNZ,SZT,QZZ,JZZ,DZZ into m_fnz,m_szt,m_qzz,m_jzz,m_dzz from GLGJJYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
if m_fnz=null then
Update GLGJJYS set FNZ=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if m_szt=null then
Update GLGJJYS set SZT=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if m_qzz=null then
Update GLGJJYS set QZZ=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if m_jzz=null then
Update GLGJJYS set JZZ=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if m_dzz=null then
Update GLGJJYS set DZZ=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
select FNZ,SZT,QZZ,JZZ,DZZ,ZT into m_fnz,m_szt,m_qzz,m_jzz,m_dzz,m_zt from GLGJJYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
if m_fnz=0 and m_szt=0 and m_qzz=0 and m_jzz=0 and m_dzz=0 then
if m_zt=-100 then
update GLGJJYS set BZ='未见干酪根' where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
else
FETCH c1 into m_ypph,m_yybh;(我想实现的是如果符合条件m_zt=-100时就执行下面的计算语句否则跳入下一条记录执行,可是在此加入FETCH c1 into m_ypph,m_yybh,过程还是往下执行了,请教这是怎么回事?)
end if;
else
update GLGJJYS set BZ=' ' where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
select BZ,YYSD1,YYSD2,CWMC,YXMS into m_bz,m_yysd1,m_yysd2,m_cwmc,m_yxms from GLGJJYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
m_bz:=ltrim(rtrim(m_bz));
if length(m_bz)=0 or m_bz is null then
TI:=null;
a:=null;
b1:=null;
b2:=null;
c:=null;
d:=null;
m_lxhf:=null;
allcount:=m_fnz+m_szt+m_qzz+m_jzz+m_dzz;
if allcount<>0 then
a:=(m_fnz*100)/allcount;
b1:=(m_szt*100)/allcount;
b2:=(m_qzz*100)/allcount;
c:=(m_jzz*100)/allcount;
d:=(m_dzz*100)/allcount;
TI:=(100*a+80*b1+50*b2-75*c-100*d)/100;
if TI<=0 then
m_lxhf:='III型';
end if;
if TI>0 and TI<40 then
m_lxhf:='II2型';
end if;
if TI>40 and TI<80 then
m_lxhf:='II1型';
end if;
if TI>=80 then
m_lxhf:='I型';
end if;
end if;
end if;
select JH,YYSD1,YYSD2,YYLB,CSRQ,CWMC,YXMS,BZ into m_jh,m_yysd1,m_yysd2,m_yylb,m_csrq,m_cwmc,m_yxms,m_bz from GLGJJYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
Insert into GLGJJ(BGBH,YPPH,YYBH,JH,YYSD1,YYSD2,YYLB,CSRQ,CWMC,YXMS,BZ) values(m_bgbh,m_ypph,m_yybh,m_jh,m_yysd1,m_yysd2,m_yylb,m_csrq,m_cwmc,m_yxms,m_bz);
m_bz:=ltrim(rtrim(m_bz));
if m_bz<>'未见干酪根' or m_bz is null then
Update GLGJJ set FNZ=a,SZT=b1,QZZ=b2,JZZ=c,DZZ=d,LXZS=TI,LXHF=m_lxhf where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
end loop;
close c1;
commit;
end;
修改为:
if m_fnz is null
--------------------
方法一:
<<l_inner>>
loop
....
else
goto l_inner;
end if;方法二:
<<l_inner>>
loop
....
else
exit l_inner;
end if;
....
end loop l_inner;