关于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存储过程总结

存储过程是一个能够封装SQL语句和业务逻辑的数据库应用对象。应用程序和数据库的大量交互会产生网络堵塞,而将应用逻辑的一部分保存在数据库中会使这一情况得到相当程度的改善。另外,存储过程提供一个集中的位置...
  • liujinwei2005
  • liujinwei2005
  • 2012年12月14日 21:14
  • 5124

DB2数据库使用存储过程详解

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。...
  • fanyun_01
  • fanyun_01
  • 2016年11月30日 09:16
  • 8145

DB2存储过程示例

下面是对于创建DB2存储过程的一个示例,正在入手这项学习的朋友可以参考。 在db2命令行中进行操作:  1.开始-运行,输入CMD,执行db2cw命令进入db2命令行模式  2.再输入db2后,...
  • pianzif
  • pianzif
  • 2014年06月24日 10:02
  • 1212

db2 存储过程详解

学习 DB2 9.5 SQL Procedural Language,包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。 您将: 学习 SQL PL 的基本要素。理解如何声明...
  • pianzif
  • pianzif
  • 2014年06月20日 13:11
  • 7631

DB2 创建或修改存储过程

  在DB2命令行创建或更新存储过程时,需要使用特殊结束符,如:@1.登陆数据库:    $ db2 -td@     db2 => connect to dbname user db2inst1 u...
  • jionghan3855
  • jionghan3855
  • 2008年07月21日 09:17
  • 7154

db2查询函数和存储过程

--查找某个存储过程   select * from syscat.procedures where procschema='PAS' and procname='sp_insert_xtrz'  -...
  • harrison2010
  • harrison2010
  • 2010年11月26日 16:45
  • 1886

DB2存储过程中的异常处理

存储过程异常的处理: DECLARE handler-type HANDLER FOR condition handler-action   异常处理器类型(handler-type)有以下几...
  • wag_pig
  • wag_pig
  • 2017年09月14日 10:47
  • 471

DB2数据库之命令行执行存储过程(以@符结束)

DB2数据中会用到存储过程,有的时候不能用客户端,需要在命令行中执行;下面是以@为结束符的存储过程。   方法1: [db2inst1@DB ~]$ db2 connect to jf user db...
  • super712
  • super712
  • 2013年12月09日 11:21
  • 2483

DB2 存储过程 调试

打开WSAD6里面的数据就可以调试了,建立数据库连接 MSN:zhoujianguo_leo@hotmail.com
  • lucifer821031
  • lucifer821031
  • 2007年12月11日 12:44
  • 1560

DB2 数据库中得到存储过程创建语句的三种方法

介绍获取 DB2 v8 数据库中所有存储过程创建语句的三种方法。ITPUB个人空间Q/E oi}.eD%zO    如果 DB2 v8 的用户需要提取数据库所有存储过程的定义,即用 CREA...
  • junli0310
  • junli0310
  • 2008年06月03日 11:33
  • 1301
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:关于DB2存储过程的一点经验
举报原因:
原因补充:

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