关于DB2存储过程的一点经验

原创 2015年07月07日 10:26:14

最近遇到一个Birt报表项目比较复杂。领导的要求是希望得到算上双休日和假期的每天的平均余额。可是数据库中只有工作日的数据,假日是没有数据的。好在假日也没有资金变动,只要用前一天的数据补上假日的数据就好了。
之前我的做法都是把数据读取到Java端,再做处理。这次想要多研究一下sql的存储过程,决定利用存储过程直接把原表中假日的数据用前一天的数据补齐。期间遇到了不少问题,所以把经验记录下来。
存储过程的代码如下:

DROP SPECIFIC PROCEDURE "DB2ADMIN"."SQL150702173356000";

SET SCHEMA DB2ADMIN;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";

CREATE PROCEDURE "DB2ADMIN"."UPDATE_EVERYDAY_BAL" ( ) 
  SPECIFIC "DB2ADMIN"."SQL150702173356000"
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  begin
DECLARE @rec_id varchar(42);
DECLARE @cmp_code varchar(9);
DECLARE @line_id varchar(9);
DECLARE @cur_code varchar(6);
DECLARE @rec_date date;
DECLARE @close_bal float;
DECLARE @lcy_close_bal float ;
DEclare @total_num int;

P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE session.TEMPDATE like cmbsg.linedata_distinct_date WITH REPLACE;
DECLARE GLOBAL TEMPORARY TABLE session.TEMPBAL like cmbsg.everyday_bal WITH REPLACE;
insert into session.tempdate (select * from cmbsg.linedata_distinct_date);
insert into session.tempbal (select * from cmbsg.everyday_bal);

P2: BEGIN
DECLARE My_Cursor CURSOR
FOR (select "REC_ID", "CMP_CODE", "LINE_ID", "CUR_CODE", "REC_DATE",
    "CLOSE_BAL", "CLOSE_BAL_LCY"     
  from session.tempbal);
OPEN My_Cursor; 

select count(*) into @total_num from session.tempbal;

FETCH My_Cursor into @rec_id,@cmp_code,@line_id,@cur_code,@rec_date,@close_bal,@lcy_close_bal;

while @total_num>0
do
        if (select count(*) from session.TEMPDATE where rec_date = (@rec_date+1 day)) = 0 
           then insert into cmbsg.everyday_bal ("REC_ID", "CMP_CODE", "LINE_ID", "CUR_CODE", "REC_DATE","CLOSE_BAL", "CLOSE_BAL_LCY") values('fake-'||left(@rec_id,37),@cmp_code,@line_id,@cur_code,@rec_date+1 day,@close_bal,@lcy_close_bal);
        end if;
    set @total_num = @total_num-1;
    FETCH My_Cursor into @rec_id,@cmp_code,@line_id,@cur_code,@rec_date,@close_bal,@lcy_close_bal;
end while;
CLOSE My_Cursor;
END P2;
END P1;
end;

这之中,遇到过的问题有:
1> while loop 中对游标(cursor)引用的table进行了insert操作。DB2 的cursor是很sensitive的,我对table的改变直接影响了cursor处理的结果,特别是我在一开始就在@total_num写死了循环的次数。我的解决办法是建立了一个和原表完全一样的临时表,让cursor引用临时表。这里需要注意

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPBAL LIKE cmbsg.everyday_bal WITH REPLACE;
insert into session.tempbal (select * from cmbsg.everyday_bal);

第一句的LIKE或者也可以写成

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPBAL AS (select * from cmbsg.everyday_bal)
DEFINITION ONLY
WITH REPLACE;

这和第一句是一样的。这个临时表里面并没有数据,只是一个结构。DEFINITION ONLY 这一句就是这个意思,这句是不得不写的。所以还必须跟一句INSERT 来加入数据。

2> 因为必须要先 declare临时表,再declare游标。一开始我写成这样:

DECLARE GLOBAL TEMPORARY TABLE session.TEMPDATE like cmbsg.linedata_distinct_date WITH REPLACE;
DECLARE GLOBAL TEMPORARY TABLE session.TEMPBAL like cmbsg.everyday_bal WITH REPLACE;
insert into session.tempdate (select * from cmbsg.linedata_distinct_date);
insert into session.tempbal (select * from cmbsg.everyday_bal);

DECLARE My_Cursor CURSOR
FOR (select "REC_ID", "CMP_CODE", "LINE_ID", "CUR_CODE", "REC_DATE",
    "CLOSE_BAL", "CLOSE_BAL_LCY"     
  from session.tempbal);
OPEN My_Cursor; 

这是不能编译通过的,会有 SQLSTATE=42601。因为DB2对变量的声明有严格的顺序。不可以在声明临时表之后声明游标。解决办法是用P1和P2把变量的声明分开。

3> 注意循环体中
if (select count(*) from session.TEMPDATE where rec_date = (@rec_date+1 day)) = 0
这句。原本的写法是
if rec_date+1 day not in (select * from session.tempdate)
按照原本的这个写法,每次循环需要0.2s的时间,跑完整个存储过程需要好几个小时。改成上面的写法之后,跑完整个存储过程就只需要几秒的时间了。

这个存储过程还有不少可以改进的地方。目前我可以想到的地方是可以放弃cursor引用的临时表,就让cursor指向目标table。同时把循环条件改成


DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE My_cursor CURSOR FOR SELECT ID FROM T;
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET V_END = 1;
FETCH My_cursor into something;
While V_END = 0 do
...

如果还有什么可以改进的地方的话,欢迎大家多多指教。

db2存储过程

  • 2013年11月19日 09:42
  • 23KB
  • 下载

DB2存储过程精简教程

  • 2014年07月25日 14:34
  • 972KB
  • 下载

DB2 存储过程几个例子

CREATE PROCEDURE proc_with_variables (IN p_empno VARCHAR(6)) LANGUAGE SQL SPECIFIC pro...
  • kalision
  • kalision
  • 2012年10月26日 13:00
  • 12530

IBM DB2 SQL存储过程

  • 2007年06月26日 11:22
  • 4.98MB
  • 下载

DB2存储过程-基础教程

  • 2013年04月23日 17:23
  • 216KB
  • 下载

DB2 如何获得存储过程对应的包中的执行计划

编写一个 shell 脚本,可以直接根据 存储过程名字来查看执行计划. vi ck_proc_expln.sh ### ck_proc_expln.sh # 将第1个 输入参数 的值,赋...

DB2存储过程

  • 2007年11月28日 09:45
  • 2.91MB
  • 下载

DB2存储过程基础培训

  • 2014年02月21日 15:41
  • 518KB
  • 下载

db2 存储过程迁移方法

存储过程迁移,解决量大,父子存储过程,存储过程过大问题

db2数据库存储过程.ppt

  • 2014年02月28日 14:05
  • 5.08MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:关于DB2存储过程的一点经验
举报原因:
原因补充:

(最多只允许输入30个字)