DB2存储过程游标

[a234440967]

DROP PROCEDURE T_ST_A_ORG_STO;

CREATE PROCEDURE T_ST_A_ORG_STO
BEGIN

DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE T_ORG_STO_CODE VARCHAR (32);--PK
DECLARE T_TRAN_MODE VARCHAR (32);--PK
DECLARE T_MONTH_ID VARCHAR (6);---PK
DECLARE T_CAR_COUNT DECIMAL (14,2);
DECLARE T_CGT_PUR_QTY DECIMAL (14,2);
DECLARE T_CGT_COUNT DECIMAL (14,2);
DECLARE T_MAXCSJS TIMESTAMP;---抽取数据的最大时间
DECLARE T_MINCSJS TIMESTAMP;---抽取数据的最小时间
DECLARE T_COUNT NUMERIC;
DECLARE CCSJ TIMESTAMP;
DECLARE at_end INT DEFAULT 0;
DECLARE V_SQL1 VARCHAR(1000);
DECLARE V_SQLSTR1 VARCHAR(1000);
-- DECLARE c1 CURSOR FOR T_SQL;
DECLARE c1 CURSOR FOR V_SQL1;
-- DECLARE EXIT HANDLER FOR NOT FOUND
------------------------------------------------------------------------
-- 说明：将数据抽取的范围存入变量
SELECT MAX(LD_CGT_ORDER_HIS.UPDATE_TIME) INTO T_MAXCSJS FROM LD_CGT_ORDER_HIS;
SELECT ST_T_TIME.UPDATETIME INTO T_MINCSJS FROM ST_T_TIME WHERE ST_T_TIME.TABLENAME='ST_A_ORG_STO';
------------------------------------------------------------------------
SET SQLCODE = 0;
SET SQLSTATE='3';
--发车次数:仓库的发货次数,从订单表里取数 ---更新ST_T_TIME 的表的时间
SET V_SQLSTR1='' ||'SELECT '
||' LD_CGT_ORDER_HIS.ORG_STO_CODE '
||' ,LD_CGT_ORDER_HIS.TRAN_MODE '
||' ,to_char(LD_CGT_ORDER_HIS.OUT_DATE, ''YYYYMM'') '
||' ,COUNT(DISTINCT LD_CGT_ORDER_HIS.BATCH_ID) '
||' ,sum(LD_CGT_ORDER_HIS.CGT_PUR_QTY1) '
||' ,count(LD_CGT_ORDER_HIS.ORDER_ID) '
||' FROM LD_CGT_ORDER_HIS '
||' GROUP BY LD_CGT_ORDER_HIS.ORG_STO_CODE '
||' ,LD_CGT_ORDER_HIS.TRAN_MODE '
||' ,LD_CGT_ORDER_HIS.OUT_DATE '
|| '';


--WHERE LD_CGT_ORDER_HIS.UPDATE_TIME<=T_MAXCSJS
-- AND LD_CGT_ORDER_HIS.UPDATE_TIME>T_MINCSJS


PREPARE V_SQL1 FROM V_SQLSTR1;

SET at_end = 1;
OPEN c1;
LOOP
FETCH c1 INTO T_ORG_STO_CODE,T_TRAN_MODE,T_MONTH_ID,T_CAR_COUNT,T_CGT_PUR_QTY,T_CGT_COUNT;
INSERT INTO DB2ADMIN.D_TEST (ID, NAME) VALUES ('4444444443333', '4');

END LOOP;
close c1;
commit;

END;

[mysdzlt2007]

loop中少个判断leave loop吧

[WWWWA]

2个示例：
OPEN c1;
SET at_end = 0;
SET numrec = 0;
fetch_loop: 1
LOOP
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
LEAVE fetch_loop; 2
END IF;
END LOOP fetch_loop; 3
CLOSE c1;

while 例子：
OPEN c1;
SET at_end = 0;
SET numrec = 0;
WHILE at_end = 0 DO
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
SET at_end = 1;
END IF;
END WHILE;
CLOSE c1;

[wwwwb]

begin

declare sqlcode integer default 0;

declare app_code varchar(10);

declare cursor1 cursor for select app_code from kf_app_class ;

open cursor1;

cursorLoop:

loop

fecth cursor1 into app_code ;

if sqlcode=100 then leave cursorLoop;

end if;

end loop;

end;

参考一下动态游标：
CREATE PROCEDURE data_wtptest( IN in_taskid_timestamp varchar(30),
OUT o_err_no int, OUT o_err_msg varchar(1024))
LANGUAGE SQL P1: BEGIN ATOMIC
声明开始
临时变量出错变量
DECLARE SQLCODE integer default 0;
DECLARE SQLStmt varchar(1024) default '';
DECLARE r_code integer default 0;
DECLARE state varchar(1024) default 'AAA';

记录程序当前所作工作
DECLARE at_end int DEFAULT 0;
DECLARE t_destnetid int default 0;
DECLARE t_recvid varchar(30) default '';
DECLARE SP_Name varchar(50) default 'data_integrate';
声明DB2存储过程中放游标的值

声明动态游标存储变量
DECLARE stmt1 STATEMENT;
DECLARE c1 CURSOR FOR stmt1;
声明出错处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION begin set r_code=SQLCODE;
set o_err_no=1;
set o_err_msg='处理['||state||']出错 ,'||'错误代码SQLCODE:['||CHAR(r_code) || '].';
insert into fcc_sp_log(object,name,value) values(SP_Name,in_taskid_timestamp,o_err_msg);
end;
DECLARE continue HANDLER for not found begin set at_end = 1;
set state='找到0行记录或已经到记录结尾.';

end;

声明结束

SET state='[add]单独测试中中,统计条数';
SET SQLStmt='SELECT count(*) FROM wtp_pre_download
where task_timestamp = ?';
PREPARE stmt1 FROM SQLStmt;
OPEN c1 USING in_taskid_timestamp;
FETCH c1 INTO t_destnetid;
CLOSE c1;
SET state='[add]查具体信息';
SET at_end = 0;
SET SQLStmt='SELECT recv_userid FROM wtp_pre_download
where task_timestamp = ?';
PREPARE c1 FROM SQLStmt;
OPEN c1 USING in_taskid_timestamp;
SET state='[add]************';
FETCH c1 INTO t_recvid;
SET state='[add]============';
insert into fcc_sp_log(object,name,value)
values(SP_Name,in_taskid_timestamp,t_recvid);
END p1

[a234440967]

[Quote=引用 4 楼 的回复:]
begin

declare sqlcode integer default 0;

declare app_code varchar(10);

declare cursor1 cursor for select app_code from kf_app_class ;

open cursor1;

cursorLoop:

loop

fecth c……
[/Quote]第一个我看懂了，但我还是不明白我的是什么错。

[wwwwb]

如果你没有用
DECLARE EXIT HANDLER FOR ...的话，必须用
leave cursorLoop
跳出循环，再仔细看看示例

[a234440967]

[Quote=引用 6 楼 的回复:]
如果你没有用
DECLARE EXIT HANDLER FOR ...的话，必须用
leave cursorLoop
跳出循环，再仔细看看示例
[/Quote]
谢谢，我明白了.原来是这个问题，纠结

[a234440967]

各位大哥，为什么运行死循环