刚刚开始使用DB2,编写了一个简单存储过程,其中使用了游标进行提取数据,根据取得的数据二次查询,但是老报24501错误:ERROR [24501] [IBM][DB2/AIX64] SQL0501N The cursor specified in a FETCH or CLOSE statement is not open. SQLSTATE=24501
这个error code的解释是:SQL0501N, 24501 在试图获取数据或关闭一个游标前必须打开一个游标。但是代码中我是将游标打开后才使用的,请帮我看下,代码如下:
SET SCHEMA = "DET";
CREATE PROCEDURE "DET"."GETCHARGE" ( )
SPECIFIC "SQL100302203852000"
LANGUAGE SQL
DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
BEGIN
--临时变量出错变量
DECLARE not_found CONDITION FOR SQLSTATE '02000'; --没有找到满足SQL语句的行
DECLARE SQLCODE integer default 0;
DECLARE r_code integer default 0;
DECLARE state varchar(1024) default 'AAA';--记录程序当前所作工作
DECLARE at_end int DEFAULT 0;
--中间数据变量
DECLARE fee_charge decimal(10,0);
DECLARE device_nbr varchar(12);
--声明提取手机号码游标
DECLARE fee_cur cursor for
select device_number from det.report_fee_201001;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
--声明结束
OPEN fee_cur;
FETCH fee_cur INTO device_nbr;
UPD_LOOP:
LOOP
IF at_end = 1 THEN
LEAVE UPD_LOOP;
ELSE
--计算欠费金额
SELECT sum(bil.charge) INTO fee_charge
FROM det.BIL_DAY_OWE_ACCT_ITEM_290_20100131 bil, det.ofr_prd_inst_main_c_20100131 ofr
WHERE bil.PRD_INST_ID = ofr.USER_NO AND ofr.device_number = device_nbr
GROUP BY bil.PRD_INST_ID;
--更新临时表欠费金额
UPDATE det.report_fee_201001 fee
SET fee.charge = fee_charge
WHERE fee.device_number = device_nbr;
--返回下一条记录
FETCH fee_cur INTO device_nbr;
END IF;
END LOOP UPD_LOOP;
COMMIT;
CLOSE fee_cur;
END;
GRANT EXECUTE ON PROCEDURE "DET"."GETCHARGE"( ) TO USER "SELUSER" WITH GRANT OPTION;
------解决方案--------------------------------------------------------
COMMIT;
CLOSE fee_cur;
--------------------
看看你上面的语句,先COMMIT后CLOSE 游标,所以就出现了上面的问题。