存储过程，游标，好奇怪的问题，希望大家帮帮忙！

[fred_du_1]

我现在有个存储过程，主要是把明细表数据汇总插入到汇总表。
1.我定义了个视图，把明细表的数据做汇总，这个视图的sql很复杂的。
2.我在存储过程中定义了游标，从视图中取出所有数据。
3.然后遍历游标，插入到目标表中。

现在有个很奇怪的问题：
1.我把视图中的所有字段都取出来放在游标中，存储过程运行的时候有很多数据插不进去，或者报“分布式协议错误”。
2.如果我只从视图中取出第一个字段，然后遍历，插入都没问题。

关键代码如下：
DECLARE cursor1 CURSOR for select ibankid,c1,s1,c2,s2 from inidraw;--申明游标
open cursor1;
set l_sqlerror=0;
fetch from cursor1 into v_bankid;
label:--遍历数据开始
while l_sqlerror=0 do
Insert into INDIVIDUALDRAWMONTH(IYEAR, IMONTH, IBANKID, VCHACCOUNTNAME, CHIDTYPE, VCHIDNUMBER, IACCOUNTNUM, ICASHDRAWALLNUM, NCASHDRAWALLSUM,ICASHDRAWCOUNTERNUM, NCASHDRAWCOUNTERSUM, ICASHDRAWATMNUM, NCASHDRAWATMSUM, VCHBANKSHORTNAME, DTINSERTDATE, CHINSERTBY)
values(‘2009‘，’11‘,v_bankid,'1','1','1',1,1,1,1,1,1,1,'1',current timestamp,'1');
fetch from cursor1 into v_bankid;

end while label;
我只要把红色部分加上，就会出现插入不完全的情况，或者报“分布式协议错误”的问题。

请大家帮我分析分析。谢谢了

[王帆]

当你加上红色部门时，游标的当前值就有5列：ibankid,c1,s1,c2,s2，但是你打开游标/游标循环时fetch却只有一列：fetch cursor1 into v_bankid;应该是这里的问题。
如果确实需要处理那几列，就在多定义几个变量，在fetch时加上它们，如fetch cursor1 into v_bankid,v_c1,v_s1,v_c2,v_s3;

[fred_du_1]

谢谢了。
不过加上还是一样的，只插入了部分数据，好奇怪，

[王帆]

老大，你不是说存储过程有问题吗？怎么把视图定义贴出来了，晕

[fred_du_1]

晕。。。
存储过程没有问题啊，我把视图给大伙看看是不是类型的问题

[fred_du_1]

现在可以肯定的是存储过程语法没问题，因为我换个视图，然后遍历可以完全插入数据，不知道是不是db2的游标对视图的复杂程度有限制

[liyan93834183]

你的语法结构好像有点问题吧
DECLARE cursor1 CURSOR for select ibankid,c1,s1,c2,s2 from inidraw;--申明游标
open cursor1;
set l_sqlerror=0;
fetch from cursor1 into v_bankid;
label:--遍历数据开始
while l_sqlerror=0 do
Insert into INDIVIDUALDRAWMONTH(IYEAR, IMONTH, IBANKID, VCHACCOUNTNAME, CHIDTYPE, VCHIDNUMBER, IACCOUNTNUM, ICASHDRAWALLNUM, NCASHDRAWALLSUM,ICASHDRAWCOUNTERNUM, NCASHDRAWCOUNTERSUM, ICASHDRAWATMNUM, NCASHDRAWATMSUM, VCHBANKSHORTNAME, DTINSERTDATE, CHINSERTBY)
values(‘2009‘，’11‘,v_bankid,'1','1','1',1,1,1,1,1,1,1,'1',current timestamp,'1');
fetch from cursor1 into v_bankid;
这句话是干什么用的啊

[fred_du_1]

你红色标记的这一句是把游标的值写到指定变量啊

[fred_du_1]

没有人知道怎末弄吗？

[王帆]

[Quote=引用 4 楼 fred_du_1 的回复:]
你红色标记的这一句是把游标的值写到指定变量啊
[/Quote]

能说明白点儿吗？最好能把新脚本、出错信息、和部分数据贴出来。

[fred_du_1]

select
ibankid,vchaccountname,chidtype,vchidnumber,vchtradeorganname,c1,s1,c2,s2,c3,s3,dtinsertdate,chinsertby,c
from
(select
r1x.ibankid,r1x.vchaccountname,r1x.chidtype,r1x.vchidnumber,r1x.vchtradeorganname,COALESCE(r1x.c1,0) as c1,COALESCE(r1x.s1,0.00) as s1,COALESCE(r1x.c2,0) as c2,COALESCE(r1x.s2,0.00) as s2,COALESCE(r1x.c3,0) as c3,COALESCE(r1x.s3,0.00) as s3,r1x.dtinsertdate,r1x.chinsertby,r2x.c

from
(
select
rx.ibankid,rx.vchaccountname,rx.chidtype,rx.vchidnumber,rx.vchtradeorganname,
sum(rx.c1) as c1,sum(rx.s1) as s1,sum(rx.c2) as c2,sum(rx.s2) as s2,sum(rx.c3) as c3,sum(rx.s3) as s3,rx.dtinsertdate,rx.chinsertby
from
(
select
rs1.ibankid,
i.vchaccountname,
i.chidtype,
i.vchidnumber,
i.vchTradeorganName,
case i.chCTType
when 0 then
count(i.nTradeSum) end as c1,
case i.chCTType
when 0 then
sum(i.nTradeSum) end as s1,
case i.chTradeManner
when '0001' then
count(i.nTradeSum)
end as c2,
case i.chTradeManner
when '0001' then
sum(i.nTradeSum)
end as s2,
case i.chTradeManner
when '0002' then
count(i.nTradeSum)
end as c3,
case i.chTradeManner
when '0002' then
sum(i.nTradeSum)
end as s3,
rs1.dtinsertdate,
rs1.chinsertby
from
(
select
r.ibankid,d.ireportid,d.dtinsertdate,d.chinsertby
from
reportswitch as r,
datafileinfo as d
where
--r.ireportid:个人大额现金表
--r.tistatus=1:表示已经上报
r.ireportyear=CSFCGGETYEAR(CURRENT TIMESTAMP,1) and r.ireportmonth=CSFCGGETMONTH(CURRENT TIMESTAMP,1) and r.tistatus=11 and r.ireportid=322 and r.ireportswitchid=d.ireportswitchid
) as rs1,
IndividualAccount as i
where
rs1.ireportid=i.ireportid
and i.chrptype=1 --表示支取方向
--and i.chCTType=0 -- 表示现金
--and i.chrptype=1
group by
rs1.ibankid,
i.vchaccountname,
i.chidtype,
i.vchidnumber,
i.vchTradeorganName,
i.chTradeManner,
rs1.dtinsertdate,
i.chCTType,
rs1.chinsertby
) as rx where
rx.c1 is not null
and rx.s1 is not null
group
by rx.ibankid,rx.vchaccountname,rx.chidtype,rx.vchidnumber,rx.vchtradeorganname,rx.dtinsertdate,rx.chinsertby
) as r1x left join
( select
rs1.ibankid,
i.vchaccountname,
count(distinct i.vchaccountnum) as c
from
(
select
r.ibankid,d.ireportid,d.dtinsertdate,d.chinsertby
from
reportswitch as r,
datafileinfo as d
where
--r.ireportid:个人大额现金表
--r.tistatus=1:表示已经上报
r.ireportyear=CSFCGGETYEAR(CURRENT TIMESTAMP,1) and r.ireportmonth=CSFCGGETMONTH(CURRENT TIMESTAMP,1) and r.tistatus=11 and r.ireportid=322 and r.ireportswitchid=d.ireportswitchid
) as rs1,
IndividualAccount as i
where
rs1.ireportid=i.ireportid
group by
rs1.ibankid,
i.vchaccountname) as r2x on r1x.ibankid=r2x.ibankid and r1x.vchaccountname=r2x.vchaccountname
) as rx

视图如上！视图可以正确执行，没有报错，执行条数是17321条

[fred_du_1]

希望大家帮帮忙，很诡异的问题哦，

[fred_du_1]

妈的，把视图换个写法，好像就可以了。好贱，估计是db2的cursor对sql有要求