增量数据下发整合成全量表的程序分享

浙江地区数据下发均为增量下发，只有数据发生改变的时候才会下发，从接口层转化大绩效层的时候，有时关联其他表会取不到数据，出于解决此问题，我们现场整理了一个批量生成MERGE语句的方法和调用的存储过程。

首先我们来学习一下MERGE INTO语句是使用方法：

DB2中的Merge语句可以将一个表中的数据合并到另一个表中，在合并的同时可以根据条件进行插入、删除、更新等操作。这个语句在我们PAS系统中多次转换主键相同的数据时特别省时间。

语法如下：

MERGE INTO table_name alias1

USING (table|view|sub_query) alias2

ON (join condition)

WHEN MATCHED THEN

     UPDATE table_name SET col1 = col_val1,col2 = col2_val

WHEN NOT MATCHED THEN

     INSERT (column_list) VALUES (column_values)

关键字、参数的解答

into子句 

在into子句中指定所要修改或者插入数据的目标表

using子句 

在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。

on子句 

在on子句中指定执行插入或者修改的满足条件。

when matched | not matched

用该子句通知数据库如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。

merge_update子句

merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。

限制：当修改一个视图时，不能指定一个default值

merge_insert 子句 

merge_insert子句执行当不符合on子句条件时，往目标表中插入数据。如果插入子句执行，则目标表上插入触发器将被触发。

限制：当修改一个视图时，不能指定一个default值

好，看完以上的基本语法后，我们来点干货吧。

JKSJ_CKZH的数据每天增量下发，我们要把它存在JXDX_CKZH_QL全量表中：执行一下命令即可。

 

merge into JKSJ_CKZH_QL as ausing (select b.ZHDH,b.ZZH,b.ZHHM,b.BZ,b.CPH,b.KMH,b.HSM,b.PZH,b.FHDH,b.JGDH,b.KHNM,b.KHH,b.KHRQ,b.QXRQ,b.DQRQ,b.XHRQ,b.ZHZT,b.ZHSX,b.QX,b.LLDH,b.NLL,b.KHJE,b.ZHYE,b.ZHBS,b.HYDH,b.CZYH         from JKSJ_CKZH b) bon a.ZHDH=b.ZHDHand a.ZZH=b.ZZH when matched then       update set (a.ZHDH,a.ZZH,a.ZHHM,a.BZ,a.CPH,a.KMH,a.HSM,a.PZH,a.FHDH,a.JGDH,a.KHNM,a.KHH,a.KHRQ,a.QXRQ,a.DQRQ,a.XHRQ,a.ZHZT,a.ZHSX,a.QX,a.LLDH,a.NLL,a.KHJE,a.ZHYE,a.ZHBS,a.HYDH,a.CZYH)                =(b.ZHDH,b.ZZH,b.ZHHM,b.BZ,b.CPH,b.KMH,b.HSM,b.PZH,b.FHDH,b.JGDH,b.KHNM,b.KHH,b.KHRQ,b.QXRQ,b.DQRQ,b.XHRQ,b.ZHZT,b.ZHSX,b.QX,b.LLDH,b.NLL,b.KHJE,b.ZHYE,b.ZHBS,b.HYDH,b.CZYH) when not matched then      insert (a.ZHDH,a.ZZH,a.ZHHM,a.BZ,a.CPH,a.KMH,a.HSM,a.PZH,a.FHDH,a.JGDH,a.KHNM,a.KHH,a.KHRQ,a.QXRQ,a.DQRQ,a.XHRQ,a.ZHZT,a.ZHSX,a.QX,a.LLDH,a.NLL,a.KHJE,a.ZHYE,a.ZHBS,a.HYDH,a.CZYH)      values (b.ZHDH,b.ZZH,b.ZHHM,b.BZ,b.CPH,b.KMH,b.HSM,b.PZH,b.FHDH,b.JGDH,b.KHNM,b.KHH,b.KHRQ,b.QXRQ,b.DQRQ,b.XHRQ,b.ZHZT,b.ZHSX,b.QX,b.LLDH,b.NLL,b.KHJE,b.ZHYE,b.ZHBS,b.HYDH,b.CZYH)

 

这么长的一段是不是看傻眼了，但现在你不需要写这一段了。因为在浙江江山的项目我们已经把脚本写好。以后增量下发的数据也可以用到。下面这个方法直接在TOAD青蛙里面执行就可以了。

 

1.首先你要建一个跟增量表JKSJ_CKZH结构一样的全量表JKSJ_CKZH_QL；

2查看与调用：单独查看可用命令查看可执行如下：

  SELECT MERGE_ADD_DATA('JKSJ_CKZH_QL', 'JKSJ_CKZH')FROM sysibm.sysdummy1;

  或者在存储过程中调用 ：

    declare c_sql varchar(11000);

    set c_sql = MERGE_ADD_DATA(t_name, t_t_name);    

    prepare s1 from c_sql;

    execute s1;

3.不单单是存款表可以这样使用，所有的只要结构相同和有主键的表都可以这样使用，如果在一个存储过程循环调用上面那一段的话，即可完善所有表的全量表，另外个人认为，此程序效率比SP_PASTRAN_CKZH要高一些，因为里面的逻辑都是一样的，产品部可以根据项目如果是增量下发数据的，可以按此优化。

提高效率，应该从细节做起。另外附件，我也放入这个方法，每一行都加了注释，里面才是精华哦，希望有兴趣的Coder可以一起交流。

CREATE FUNCTION PAS.F_MERGE_ADD_DATA  (  t_name   varchar(50), --全量表表名   t_t_name varchar(50)  --增量数据表名 )RETURNS VARCHAR(10000)LANGUAGE SQL/*------------------------------------------------------------------------ 公用函数：生成MERGER语句 功能描述：输入全量表t_name和增量表t_t_name,返回增加增量数据到全量表的MERGE语句 作者:LIUHZ 日期:2016-11-27 项目名称: 核心功能db2版 版本: v1.0 开发商：广州天维信息技术股份有限公司 调用范例: SELECT F_MERGE_ADD_DATA('JKSJ_CKZH_QL', 'JKSJ_CKZH') FROM sysibm.sysdummy1;------------------------------------------------------------------------*/BEGIN    declare c_sql varchar(11000);     --返回的sql    declare r_col varchar(3000);      --全量表的列名 r表示result    declare r_col_b varchar(3000);    --增量表的列名 r表示result    declare r_mainkey varchar(3000);  --全量表和增量表的主键 r表示result    declare s_col varchar(200);       --取列名的sql    declare s_mainkey varchar(300);   --取主键的sql    --用于记录主键的个数，    declare v_start int;      --记录substr()取数的开始位置    declare v_end   int;      --记录sbustr()取数的结束位置    --声明sql语句 s1和游标 c1    declare s1 statement;       --存放编译的sql语句    declare c1 cursor for s1;   --游标    --行转列，获取增量表的列名。    set s_col = 'SELECT REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME A, ''b.''||name||'','',''''))),''<A>'',''''),''</A>'','''') '||     'from (select name from SYSIBM.SYSCOLUMNS where TBNAME='''||t_name||''' order by COLNO asc )' ;    --预编译 s1    prepare s1 from s_col;--将结果集放到 r_col_b (增量表的列名)    open c1;    fetch c1 into r_col_b;    close c1;    --去掉最右逗号    set r_col_b = left( r_col_b , length(r_col_b)-1);    --行转列    set s_col = 'SELECT REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME A, ''a.''||name||'','',''''))),''<A>'',''''),''</A>'','''') from (select name from SYSIBM.SYSCOLUMNS where TBNAME= '''||t_name||''' order by COLNO asc )' ;    --将结果集放到 r_col (全量表的列名)    prepare s1 from s_col;    open c1;    fetch c1 into r_col;    close c1;    --去掉最右逗号    set r_col = left( r_col , length(r_col)-1);    --组合merge语句    set c_sql = 'merge into '|| t_name || ' as a using (select '||r_col_b||' from '||t_t_name||' b) b on ';    --SELECT REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME A, name||','))),'<A>',''),'</A>','') AS SELECTS     -- from (select name from SYSIBM.SYSCOLUMNS where TBNAME= 'JKSJ_CKZH' and KEYSEQ is not null order by keyseq asc)    --获取主键，但PAS系统一般不声明primaryKey，只将我们的认为的主键声明为not null。    set s_mainkey = 'SELECT REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME A, name||'','',''''))),''<A>'',''''),''</A>'','''') from (select name from SYSIBM.SYSCOLUMNS where TBNAME='''||t_name||'''  and NULLS=''N'' order by NULLS asc)' ;    --将结果集放到 r_mainkey (全量表和增量表关联的主键)    prepare s1 from s_mainkey;    open c1;    fetch c1 into r_mainkey;    close c1;   --去掉最右逗号    set r_mainkey = left( r_mainkey , length(r_mainkey)-1);    --组合 on 后面的条件(其实就是主键关联) 到 c_sql    set v_start = 1;    set v_end =  locate(',',r_mainkey);    while v_end > 0        do            set c_sql = c_sql || ' a.'||substr(r_mainkey,v_start,v_end-v_start) || '=b.'||substr(r_mainkey,v_start,v_end-v_start)||' and ';            set v_start = v_end+1;            set v_end = locate(',',r_mainkey,v_start);    end while;    --组合主键中最后一个主键    set c_sql = c_sql || ' a.'||substr(r_mainkey,v_start) || '=b.'||substr(r_mainkey,v_start);    --组合merge语句的更新语句和插入语句    set c_sql = c_sql || ' when matched then update set ('||r_col||')=('||r_col_b||') when not matched then insert ('||r_col||' ) values ('||r_col_b||')';call  SP_PASSYS_XTRZ ('0',v_proc_name,c_sql);--返回MERGE语句RETURN c_sql;--RETURN s_mainkey;END