label:BEGIN
/*日结存储过程*/
DECLARE var_cur_date varchar(15); /*当前日期*/
declare var_zt_date varchar(15); /*昨天日期*/
declare var_wl_code varchar(30); /*物料编码*/
declare flo_kc_num float(9,3) default 0; /*前期库存量*/
declare flo_rk_num float(9,3) default 0; /*当天入库数*/
declare flo_ck_num float(9,3) default 0; /*当天出库数*/
declare flo_dt_num float(9,3) default 0; /*当天库存*/
DECLARE done INT DEFAULT 0;
declare char_yyzt char(1);
DECLARE cur_cq_kc CURSOR FOR select cg_wl.wl_code,kc_num from cg_wl left outer join (select * from cg_wl_kc where convert(kc_rq,date)=(select convert(cur_date,date) from xt_rqb)) cg_wl_kc on cg_wl.wl_code=cg_wl_kc.wl_code; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /**
计算出当前日期和昨天日期
**/
select cur_date,zt_date,rqb_yyzt into var_cur_date,var_zt_date,char_yyzt from xt_rqb; if char_yyzt='2' then
set param_message='已经日结中,日始后才能日结';
LEAVE label;
end if; OPEN cur_cq_kc;
REPEAT
set flo_rk_num=0;
SET flo_ck_num=0;
SET flo_kc_num=0;
FETCH cur_cq_kc INTO var_wl_code,flo_kc_num;
IF NOT done THEN if flo_kc_num is null THEN
set flo_kc_num=0;
end if; /**计算出当天入库数量**/
select sum(rkmx_num) into flo_rk_num from cg_hg_view where convert(rk_time,date)=convert(var_cur_date,date) and wl_code=var_wl_code group by wl_code;
if flo_rk_num is null then
set flo_rk_num=0;
end if;
/**计算出当天出库数量**/
select sum(ck_num) into flo_ck_num from cg_ck,cg_ckmx,cg_llmx where cg_ck.ck_code=cg_ckmx.ck_code and cg_ckmx.llmx_code = cg_llmx.llmx_code and convert(ck_date,date)=convert(var_cur_date,date) and wl_jl_code=var_wl_code group by wl_jl_code;
if flo_ck_num is null then
set flo_ck_num=0;
end if; /**得到当前库存量,当前库存=前期库存+当天入库数-当天领用数**/
set flo_dt_num = flo_kc_num+flo_rk_num-flo_ck_num; insert into cg_wl_kc values(var_wl_code,var_cur_date,flo_dt_num);
end if;
UNTIL done END REPEAT;
update xt_rqb set rqb_yyzt='2';
close cur_cq_kc;
END
以上是存储过程的整个代码,现在我用DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 这个控制游标的滚动,当执行 /**计算出当天入库数量**/
select sum(rkmx_num) into flo_rk_num from cg_hg_view where convert(rk_time,date)=convert(var_cur_date,date) and wl_code=var_wl_code group by wl_code; 查不到数据时,好像游标的循环就跳出循环,后面的插入语句都执行不了,请问怎么解决这个问题.
/*日结存储过程*/
DECLARE var_cur_date varchar(15); /*当前日期*/
declare var_zt_date varchar(15); /*昨天日期*/
declare var_wl_code varchar(30); /*物料编码*/
declare flo_kc_num float(9,3) default 0; /*前期库存量*/
declare flo_rk_num float(9,3) default 0; /*当天入库数*/
declare flo_ck_num float(9,3) default 0; /*当天出库数*/
declare flo_dt_num float(9,3) default 0; /*当天库存*/
DECLARE done INT DEFAULT 0;
declare char_yyzt char(1);
DECLARE cur_cq_kc CURSOR FOR select cg_wl.wl_code,kc_num from cg_wl left outer join (select * from cg_wl_kc where convert(kc_rq,date)=(select convert(cur_date,date) from xt_rqb)) cg_wl_kc on cg_wl.wl_code=cg_wl_kc.wl_code; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /**
计算出当前日期和昨天日期
**/
select cur_date,zt_date,rqb_yyzt into var_cur_date,var_zt_date,char_yyzt from xt_rqb; if char_yyzt='2' then
set param_message='已经日结中,日始后才能日结';
LEAVE label;
end if; OPEN cur_cq_kc;
REPEAT
set flo_rk_num=0;
SET flo_ck_num=0;
SET flo_kc_num=0;
FETCH cur_cq_kc INTO var_wl_code,flo_kc_num;
IF NOT done THEN if flo_kc_num is null THEN
set flo_kc_num=0;
end if; /**计算出当天入库数量**/
select sum(rkmx_num) into flo_rk_num from cg_hg_view where convert(rk_time,date)=convert(var_cur_date,date) and wl_code=var_wl_code group by wl_code;
if flo_rk_num is null then
set flo_rk_num=0;
end if;
/**计算出当天出库数量**/
select sum(ck_num) into flo_ck_num from cg_ck,cg_ckmx,cg_llmx where cg_ck.ck_code=cg_ckmx.ck_code and cg_ckmx.llmx_code = cg_llmx.llmx_code and convert(ck_date,date)=convert(var_cur_date,date) and wl_jl_code=var_wl_code group by wl_jl_code;
if flo_ck_num is null then
set flo_ck_num=0;
end if; /**得到当前库存量,当前库存=前期库存+当天入库数-当天领用数**/
set flo_dt_num = flo_kc_num+flo_rk_num-flo_ck_num; insert into cg_wl_kc values(var_wl_code,var_cur_date,flo_dt_num);
end if;
UNTIL done END REPEAT;
update xt_rqb set rqb_yyzt='2';
close cur_cq_kc;
END
以上是存储过程的整个代码,现在我用DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 这个控制游标的滚动,当执行 /**计算出当天入库数量**/
select sum(rkmx_num) into flo_rk_num from cg_hg_view where convert(rk_time,date)=convert(var_cur_date,date) and wl_code=var_wl_code group by wl_code; 查不到数据时,好像游标的循环就跳出循环,后面的插入语句都执行不了,请问怎么解决这个问题.
至于你说,执行查询
select sum(rkmx_num) into flo_rk_num from cg_hg_view where convert(rk_time,date)=convert(var_cur_date,date) and wl_code=var_wl_code group by wl_code;之后.好像退出了循环.我想应该不会吧.当使用fetch去执行游标操作后.并将指针指向下一行.当没有数据时,才会set done=1,而你的查询.不会对游标位置进行移动.所以应该不会set done=1吧?