关于PB游标跳出循环sqlca.sqlcode的问题

[sxwzj]

使用游标跳出循环用 sqlca.sqlcode <> 100 可是到了数据结尾无法跳出，应该如何做？
程序如下：
declare c2 cursor for
select ct_ywbh from wzn_dhjl_m where to_char(dt_dsj,'yyyy/mm') = :st_sj and ct_gysbm = :st_gysbm order by ct_ywbh;
open c2;
do while sqlca.sqlcode<>100 //这里应该用什么条件才能在游标的最后一条数据跳出？
fetch c2 into :st_ywbh;
select sum(am_jk) into :i_jhfk from wzn_dhjl_d where ct_ywbh = :st_ywbh;
i_jhfk_hj = i_jhfk_hj + i_jhfk
loop
close c2;

我试过sqlca.sqlcode<>100， sqlca.sqlcode=0 都不行。

[xiaoyou1982]

今天刚写了个游标循环，不过是双循环，你自己对着学习
declare cor_code cursor for
SELECT MFCODE FROM MANAFRAME WHERE MFSTATUS = 'Y' and mftypecode='1' and mflsfs<>'3' and mfclass='1'
and MFCODE<>:ls_prcmfid;
open cor_code;

fetch cor_code into :ls_gljg;
DO WHILE sqlca.sqlcode=0
if l_row<0 then
exit;
end if

declare cro_code cursor for
SELECT MFFCODE,MFCODE FROM MANAFRAME WHERE MFSTATUS = 'Y' and mflsfs<>'3' and mfareacenter=:ls_gljg;
open cro_code;
fetch cro_code into :ls_dm,:ls_ck;
DO WHILE sqlca.sqlcode=0

select nvl(sum(BTHSSL),0) into :l_sl from goodsbatchday
where BTFBILLNO in (select BIHBILLNO from BINSTRHEAD where BIHJHDATE>=:ld_prcksdate
and BIHJHDATE<=:ld_prcjsdate and BIHSHMKT=:ls_prcmfid
and BIHSUPID=:ls_gys and BIHDJLB='1' and BIHFLAG='Y')
and BTMARKET=:ls_dm and BTMFID=:ls_ck and BTSUPID=:ls_nbgys and BTGDID=:ls_code
and BTDATE=:ld_prcjsdate;


select nvl(sum(abs(jglsl)),0) into :l_jxce from jxcgoodslist
where JGLFBILLNO in (select BIHBILLNO from BINSTRHEAD where BIHJHDATE>=:ld_prcksdate
and BIHJHDATE<=:ld_prcjsdate and BIHSHMKT=:ls_prcmfid and BIHSUPID=:ls_gys
and BIHDJLB='1' and BIHFLAG='Y')
and JGLMARKET=:ls_dm and JGLMFID=:ls_ck and JGLSUPID=:ls_nbgys
and JGLGDID=:ls_code and jgltran='E';


select nvl(sum(abs(jglsl)),0) into :l_jxcg from jxcgoodslist
where JGLFBILLNO in (select BIHBILLNO from BINSTRHEAD
where BIHJHDATE>=:ld_prcksdate BIHJHDATE<=:ld_prcjsdate
and BIHSHMKT=:ls_prcmfidand BIHSUPID=:ls_gys
and BIHDJLB='1' and BIHFLAG='Y')
and JGLMARKET=:ls_dm and JGLMFID=:ls_ck and JGLSUPID=:ls_nbgys
and JGLGDID=:ls_code and jgltran='G'
and jglvc5 in (select cdmno from cardmain where cdmflag='1');//渠道商批发，可能不准确，还得确认

l_sum=l_jxce+l_jxcg;
l_lsum=l_sum+l_sl;
if l_lsum>0 then
l_row=dw_detail.insertrow(0)
dw_detail.Setitem(l_row,'pcerow',l_row)
dw_detail.Setitem(l_row,'pcemfid',ls_gljg)
dw_detail.Setitem(l_row,'pcebmen',ls_ck)
dw_detail.Setitem(l_row,'pcekcsl',l_sl)
dw_detail.Setitem(l_row,'pcexssl',l_sum)
dw_detail.Setitem(l_row,'pcejc','2')
dw_detail.Setitem(l_row,'pcepec',ls_nbgys)
end if;
fetch cro_code into :ls_dm,:ls_ck;
LOOP
close cro_code;

fetch cor_code into :ls_gljg;
LOOP
close cor_code;

[xzxxiong]

declare c_1 cursor for ....;
open c_1;
do while sqlca.sqlcode = 0
fetch c_1 into ....;
if sqlca.sqlcode <> 0 then exit
loop
close c_1;

[I_am_Z]

你在fetch c2 into :st_ywbh;后要进行sqlca的判断，如果没有取到值，就要exit推出循环。

[sxwzj]

如何进行判断呢？目前PB调试跟踪程序在最后一条数据时候，st_ywbh取得的值永远都是最后一个数据，并不是取出空数据，造成死循环。

[I_am_Z]

这个还是建议你在执行游标前查查语句返回的记录数存入变量，以此界定游标是否走到最后一条。

[sxwzj]

[Quote=引用 6 楼 hxy75 的回复:]
declare c2 cursor for
select ct_ywbh from wzn_dhjl_m where to_char(dt_dsj,'yyyy/mm') = :st_sj and ct_gysbm = :st_gysbm order by ct_ywbh;
open c2;
fetch c2 into :st_ywbh;
do while sqlca.sqlcode <>100 //这里应该用什么条件才能在游标的最后一条数据跳出？
select sum(am_jk) into :i_jhfk from wzn_dhjl_d where ct_ywbh = :st_ywbh;
i_jhfk_hj = i_jhfk_hj + i_jhfk
fetch c2 into :st_ywb…
[/Quote]

to 小潜，青锋 ：
如果在LOOP前加fetch c2 into :st_ywbh; 有2个fetch c2 into :st_ywbh;出现会造成中间的代码只计算一半次数，即若有6个数据，只能计算3次就跳出循环。

[sxwzj]

谢谢，好了。第一个fetch c2 into :st_ywbh;没看清楚，是放在循环外的。

[sxwzj]

解决，比较麻烦，不知道有没有好点方法。
select count(*) into :i_c2 from wzn_dhjl_m where ct_dhbh like :st_sj and ct_gysbm = :st_gysbm and ct_bzbh='E' order by ct_ywbh;
i_c2k = 0
declare c2 cursor for
select ct_ywbh from wzn_dhjl_m where ct_dhbh like :st_sj and ct_gysbm = :st_gysbm and ct_bzbh='E' order by ct_ywbh;
open c2;
do while sqlca.sqlcode<>100
i_c2k ++
fetch c2 into :st_ywbh;
select sum(am_jk) into :i_jhfk from wzn_dhjl_d where ct_ywbh = :st_ywbh;//每个业务编号内的所有金额合计
i_jhfk_hj = i_jhfk_hj + i_jhfk
if i_c2 = i_c2k then exit
// messagebox(string(st_gysbm),string(st_ywbh))
loop
close c2;

[小潜s]

fetch后面的select语句会影响SQLCODE，要把FETCH语句写在循环的最好，即LOOP之前，在WHILE之前也写一句FETCH语句

[小潜s]

declare c2 cursor for
select ct_ywbh from wzn_dhjl_m where to_char(dt_dsj,'yyyy/mm') = :st_sj and ct_gysbm = :st_gysbm order by ct_ywbh;
open c2;
fetch c2 into :st_ywbh;
do while sqlca.sqlcode <>100 //这里应该用什么条件才能在游标的最后一条数据跳出？
select sum(am_jk) into :i_jhfk from wzn_dhjl_d where ct_ywbh = :st_ywbh;
i_jhfk_hj = i_jhfk_hj + i_jhfk
fetch c2 into :st_ywbh;
loop
close c2;

[青锋-SS]

改成这样试试:

declare c2 cursor for
select ct_ywbh from wzn_dhjl_m where to_char(dt_dsj,'yyyy/mm') = :st_sj and ct_gysbm = :st_gysbm order by ct_ywbh;
open c2;
fetch c2 into :st_ywbh;
do while sqlca.sqlcode <>100  //这里应该用什么条件才能在游标的最后一条数据跳出？
select sum(am_jk) into :i_jhfk from wzn_dhjl_d where ct_ywbh = :st_ywbh;
i_jhfk_hj = i_jhfk_hj + i_jhfk
fetch c2 into :st_ywbh;
loop
close c2;

[青锋-SS]

晕,没看小潜的,两个一样了.

[小潜s]

另外，循环的判断一般不建议用SQLCODE<>100来判断，最好用SQLCODE=0去判断，否则，如果游标出错的时候，由于SQLCODE是-1，满足SQLCODE<>100的条件，会导致死循环。

[sanyuv]

我想我这个方案 可以实现
open KcmCursor;//课程名游标

/*循环游标取出课程名*/
fetch KcmCursor into :ls_kcm;
do
ls_sql = ls_sql+ " ,SUM(CASE Course.Kcm WHEN '"+ls_kcm+"' THEN Grade.Ach END) as '"+ls_kcm+"' "
fetch KcmCursor into :ls_kcm;
loop while sqlca.sqlcode = 0

close KcmCursor;//关闭游标