写了一段代码:就是有错,请各位忙帮看一下吧!谢谢!说是第19行和第30行出错,好像是嵌套的游标有问题,望指教!
declare
axxdm varchar2(5);
dxxdm varchar2(5);
xxcount number;
i number;
xxsl number;
newxxdm varchar2(5);
xsxh varchar2(10);
cursor xxdm_cur is select xx.unit_id from xxa_jbxx xx where length(xx.unit_id)=4;
begin
open xxdm_cur;
loop
fetch xxdm_cur into newxxdm;
exit when xxdm_cur%notfound;
select count(*) into xxsl from xsa_jbxx x where x.xxdm=newxxdm ;
declare cursor xsxh_cur is select xs.xh from xsa_jbxx xs where xs.xxdm=newxxdm;
open xsxh_cur;
loop
fetch xsxh_cur into xsxh;
exit when xsxh_cur%notfound;
select length(aa.xh) into xxcount from xsa_jbxx aa where aa.xh=xsxh;
if xxcount=1 then
update xsa_jbxx x set x.xh=x.xxdm||'00'||x.xh where x.xh=i and x.xxdm=newxxdm;
elsif xxcount=2 then
update xsa_jbxx x set x.xh=x.xxdm||'0'||x.xh where x.xh=i and x.xxdm=newxxdm;
elsif xxcount=3 then
update xsa_jbxx x set x.xh=x.xxdm||x.xh where x.xh=i and x.xxdm=newxxdm;
end if;
end loop;
close xsxh_cur;
end loop;
close xxdm_cur;
commit;
end;
declare
axxdm varchar2(5);
dxxdm varchar2(5);
xxcount number;
i number;
xxsl number;
newxxdm varchar2(5);
xsxh varchar2(10);
cursor xxdm_cur is select xx.unit_id from xxa_jbxx xx where length(xx.unit_id)=4;
begin
open xxdm_cur;
loop
fetch xxdm_cur into newxxdm;
exit when xxdm_cur%notfound;
select count(*) into xxsl from xsa_jbxx x where x.xxdm=newxxdm ;
declare cursor xsxh_cur is select xs.xh from xsa_jbxx xs where xs.xxdm=newxxdm;
open xsxh_cur;
loop
fetch xsxh_cur into xsxh;
exit when xsxh_cur%notfound;
select length(aa.xh) into xxcount from xsa_jbxx aa where aa.xh=xsxh;
if xxcount=1 then
update xsa_jbxx x set x.xh=x.xxdm||'00'||x.xh where x.xh=i and x.xxdm=newxxdm;
elsif xxcount=2 then
update xsa_jbxx x set x.xh=x.xxdm||'0'||x.xh where x.xh=i and x.xxdm=newxxdm;
elsif xxcount=3 then
update xsa_jbxx x set x.xh=x.xxdm||x.xh where x.xh=i and x.xxdm=newxxdm;
end if;
end loop;
close xsxh_cur;
end loop;
close xxdm_cur;
commit;
end;
declare
axxdm varchar2(5);
dxxdm varchar2(5);
xxcount number;
i number;
xxsl number;
newxxdm varchar2(5);
xsxh varchar2(10);
cursor xxdm_cur is select xx.unit_id from xxa_jbxx xx where length(xx.unit_id)=4;
begin
open xxdm_cur;
loop
fetch xxdm_cur into newxxdm;
exit when xxdm_cur%notfound;
select count(*) into xxsl from xsa_jbxx x where x.xxdm=newxxdm ;
--declare cursor xsxh_cur is select xs.xh from xsa_jbxx xs where xs.xxdm=newxxdm;
-- open xsxh_cur;
for xsxh_cur in (select xs.xh from xsa_jbxx xs where xs.xxdm=newxxdm)
loop
fetch xsxh_cur into xsxh;
exit when xsxh_cur%notfound;
select length(aa.xh) into xxcount from xsa_jbxx aa where aa.xh=xsxh;
if xxcount=1 then
update xsa_jbxx x set x.xh=x.xxdm||'00'||x.xh where x.xh=i and x.xxdm=newxxdm;
elsif xxcount=2 then
update xsa_jbxx x set x.xh=x.xxdm||'0'||x.xh where x.xh=i and x.xxdm=newxxdm;
elsif xxcount=3 then
update xsa_jbxx x set x.xh=x.xxdm||x.xh where x.xh=i and x.xxdm=newxxdm;
end if;
end loop;
--close xsxh_cur;
end loop;
close xxdm_cur;
commit;
end;
检查xxa_jbxx和xsa_jbxx这两个表里面有什么数据?你想要的结果是什么?
是不是这两个表的数据库不满足循环的结果而错误?
很有可能的,对未知的一个NULL判断,用nvl函数做一下。