db2 to 8a 迁移

1、注释符"--" 后面需要加空格"-- "
2、每一个语句结束后需要加英文分号
    例：
    select * from t1--测试注释
    改为
    select * from t1;-- 测试注释
3、sysibm.sysdummy1 改为 dual
4、sysibm.dual 改为 dual
    例：
    select 1+1 from sysibm.sysdummy1
    select 1+1 from sysibm.dual
    改为
    select 1+1 from dual;
5、存储过程头部带的属性列表内容去掉
6、存储过程前后添加语句结束标识DELIMITER ;;
7、删除存储过程GO关键字
8、ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED  删掉。
    CREATE PROCEDURE MAS.PROC_BPM_BPM_DP014
    (IN IN_WORKDATE VARCHAR(8)) 
    SPECIFIC PROC_BPM_BPM_DP014
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    INHERIT SPECIAL REGISTERS
    BEGIN
    ......
    END
    GO
    改为
    DELIMITER ;;
    CREATE PROCEDURE MAS.PROC_BPM_BPM_DP014
    (IN IN_WORKDATE VARCHAR(8)) 
    BEGIN
    ......
    END
    DELIMITER ;
9、关键字sqlstate，改为`sqlstate`
10、关键字div，改为`div`
11、关键字sort，改为`sort`
    例：
    select sqlstate.a1,div.a1,sort.a1 from t1 sqlstate,t2 div,t3 sort
    改为
    select `sqlstate`.a1,`div`.a1,`sort`.a1 from t1 `sqlstate`,t2 `div`,t3 `sort`;
12、session临时表，需要统一将 "SESSION." 删掉
13、临时表定义需要将DECLARE GLOBAL TEMPORARY TABLE xxx修改为CREATE TEMPORARY TABLE xxx
    例：
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_F_DP_TD_ACC(...)
    insert into SESSION.TMP_F_DP_TD_ACC values (...)
    改为
    create TEMPORARY TABLE TMP_F_DP_TD_ACC(...)
    insert into TMP_F_DP_TD_ACC values (...)
14、REPLACE(SUBSTR(CHAR(CURRENT TIMESTAMP),1,10),'-','') 改为 date_format(now(),'%Y%m%d')
15、REPLACE(SUBSTR(TO_CHAR(CURRENT TIMESTAMP - 1 DAYS,'yyyy-MM-dd hh24:mi:ss'),1,10),'-    ','') 改为 date_format(date_sub(now(),interval 1 day),'%Y%m%d')
16、REPLACE(SUBSTR(TO_CHAR(CURRENT TIMESTAMP,'yyyy-MM-dd hh24:mi:ss'),1,10),'-','') 改为     date_format(now(),'%Y%m%d')
    REPLACE(SUBSTR(CHAR(CURRENT_TIMESTAMP - 1 days),1,10),'-','') 改为     date_format(date_sub(now(),interval 1 day),'%Y%m%d')
17、current timestamp 改为 current_timestamp
18、CURRENT DATE 改为 CURRENT_DATE
19、fetch first 1 rows only 改为 limit 1
20、timestampdiff(2,char(current timestamp - MAX(MACHINE_TM))) 改为     timestampdiff(second,MAX(MACHINE_TM),current_timestamp)
21、一个字符串在另一个字符串中首次出现的位置函数。POSSTR改为instr
22、double函数迁移为cast函数。double('12.345')改为cast('12.345' as decimal(18,2))
23、dec函数迁移为cast函数。dec(a.ACCINT_QUAR*36000,24,6)改为cast(a.ACCINT_QUAR*36000 as     decimal(24,6))
24、translate(v_corp_tmp,'','012')调整入参顺序改为translate(v_corp_tmp,'012','')
25、where条件in后不能直接接字段，需要用子查询的形式
    例：
    select * from BPM_CM_DICTIONARY where '00023' in ITEM_TYPE_ID;
    改为
    select * from BPM_CM_DICTIONARY where '00023' in (select ITEM_TYPE_ID from     BPM_CM_DICTIONARY);
26、insert into语句的values后不能直接接子查询，需要把子查询展开，用insert into select形式例：
    insert into t2 values (3,(select b from t1 where a=1));
    改为
    insert into t2(a,b) select 3,b from t1 where a=1;
27、rownumber() over() 改为 row_number() over( order by 1)
28、函数里如果引用表，需要表为复制表
29、VALUES函数和int函数直接去掉
    例：
    SET DAYS=(VALUES((int(SUBSTR(LN_DUE_DT_IN,5,2))-1)*31+int(SUBSTR(LN_DUE_DT_IN,7,2))));                
    改为
    SET DAYS=((SUBSTR(LN_DUE_DT_IN,5,2)-1)*31+SUBSTR(LN_DUE_DT_IN,7,2));
30、char('abc')||'字符串' 直接用 'abc'||'字符串'
31、update t1 set c1,c2 = (select c1,c2) 改为 update t1,(查询表) set c1=  ,c2 = 
32、set 变量 = (select ...) 改为 select .. into 变量 ...
33、游标方式执行SQL改为call executeSQL 去执行SQL，只有纯粹游标使用才迁移成游标
34、return 跳出，改为leave label;
35、with as嵌套递归 改为 with as connect by prior xxx start with xxx
    例：
    （1）WITH TMP(INSTN_NO,UPPER_INSTN_NO,BEGIN_DATE,END_DATE) AS(SELECT
        O.INSTN_NO,O.UPPER_INSTN_NO,o.BEGIN_DATE,o.END_DATE FROM
        BPM_CM_INSTN AS O 
        UNION ALL
        SELECT
        TMPPLUS.INSTN_NO,TMPPLUS.UPPER_INSTN_NO,TMPPLUS.BEGIN_DATE,TMPPLUS.END_DATE
        FROM BPM_CM_INSTN AS TMPPLUS,TMP WHERE TMP.INSTN_NO = TMPPLUS.UPPER_INSTN_NO)
        改为
        with TMP as (
            select 
                INSTN_NO,
                UPPER_INSTN_NO,
                BEGIN_DATE,
                END_DATE
            from
                BPM_CM_INSTN
            connect by prior INSTN_NO = UPPER_INSTN_NO
        )
    （2）with tmp(INSTN_NO,UPPER_INSTN_NO ) as (select INSTN_NO,UPPER_INSTN_NO from
        BPM_CM_INSTN where INSTN_NO='$dept_num$'
        union all
        select tmpplus.INSTN_NO,tmpplus.UPPER_INSTN_NO from BPM_CM_INSTN as tmpplus,tmp
        where tmp.INSTN_NO = tmpplus.UPPER_INSTN_NO
        改为
        with TMP as (
            select
            INSTN_NO,
            UPPER_INSTN_NO
            from
            BPM_CM_INSTN
            connect by prior INSTN_NO = UPPER_INSTN_NO start with INSTN_NO='$dept_num$'
        )
    （3）with ORG(INSTN_NO,UPPER_INSTN_NO,BEGIN_DATE,END_DATE) as (select
        INSTN_NO,UPPER_INSTN_NO,BEGIN_DATE,END_DATE FROM BPM_CM_INSTN where BPM_CM_INSTN.
        INSTN_NO = #orgId#
        union all
        select ORGTMP.INSTN_NO,ORGTMP.UPPER_INSTN_NO,ORGTMP.BEGIN_DATE,ORGTMP.END_DATE
        FROM BPM_CM_INSTN AS ORGTMP,ORG WHERE ORG.INSTN_NO = ORGTMP.UPPER_INSTN_NO AND
        to_char(CURRENT TIMESTAMP,'yyyymmdd') BETWEEN
        ORGTMP.BEGIN_DATE AND
        ORGTMP.END_DATE )
        改为
        with ORG as (
            select
            INSTN_NO,UPPER_INSTN_NO,BEGIN_DATE,END_DATE
            from (
                select
                INSTN_NO,UPPER_INSTN_NO,BEGIN_DATE,END_DATE
                from
                BPM_CM_INSTN
                where to_char(current_timestamp,'yyyymmdd') between BEGIN_DATE and END_DATE
            ) as m
            connect by prior m.INSTN_NO = m.UPPER_INSTN_NO start with m.INSTN_NO = #orgId#
        )
36、投影列中函数中包含SELECT的，需要修改为join，范例如下：
    （1）MAS.PROC_BPM_ITEM_CR_SHOP_NUM_CUM 存储迁移以下SQL
    INSERT INTO TMP_BPM_CM_CR_SHOP
        SELECT 
         COALESCE(ins.INSTN_NAME,COALESCE((SELECT INSTN_NAME FROM BPM_CM_INSTN WHERE INSTN_NO = c.INSTN_NO and v_sDate BETWEEN BEGIN_DATE AND END_DATE),'')) CUM_ORG_NAME, 
      COALESCE(SH.CR_MGBR,'') OPAC_INSTN_NO,
      COALESCE((SELECT INSTN_NAME FROM BPM_CM_INSTN WHERE INSTN_NO = SH.CR_MGBR and v_sDate BETWEEN BEGIN_DATE AND END_DATE),'') OPAC_INSTN_NAME
    FROM
    (SELECT * FROM BPM_INF_CR_SHOP_PRO_HIS where CORPORATION =v_corp  AND v_sDate BETWEEN BEGIN_DATE AND END_DATE) SH 
    LEFT JOIN tmp_BPM_CM_SHOP_DIV BL
    ON SH.CR_SHOP_NO=BL.CR_SHOP_NO 
    LEFT JOIN BPM_CM_INSTN INS
    ON BL.CUM_ORG_NO = INS.INSTN_NO 
    LEFT JOIN    (SELECT * FROM BPM_CM_EMP WHERE EMP_PROPERTY = 'VIRTUAL' )c  
    ON SH.CR_MGBR = c.INSTN_NO;     
    修改为          
    INSERT INTO TMP_BPM_CM_CR_SHOP
        SELECT 
      COALESCE(ins.INSTN_NAME,COALESCE(hh.INSTN_NAME,'')) CUM_ORG_NAME, 
      COALESCE(SH.CR_MGBR,'') OPAC_INSTN_NO,
      COALESCE(zz.INSTN_NAME,'') OPAC_INSTN_NAME
    FROM
    (SELECT * FROM BPM_INF_CR_SHOP_PRO_HIS where CORPORATION =v_corp  AND v_sDate BETWEEN BEGIN_DATE AND END_DATE) SH 
    LEFT JOIN tmp_BPM_CM_SHOP_DIV BL
    ON SH.CR_SHOP_NO=BL.CR_SHOP_NO 
    LEFT JOIN BPM_CM_INSTN INS
    ON BL.CUM_ORG_NO = INS.INSTN_NO 
    LEFT JOIN    (SELECT * FROM BPM_CM_EMP WHERE EMP_PROPERTY = 'VIRTUAL' )c  
    ON SH.CR_MGBR = c.INSTN_NO 
     LEFT JOIN (SELECT INSTN_NO,INSTN_NAME FROM BPM_CM_INSTN WHERE v_sDate BETWEEN BEGIN_DATE AND END_DATE)hh
     ON hh.INSTN_NO = c.INSTN_NO
     LEFT JOIN (SELECT INSTN_NO,INSTN_NAME FROM BPM_CM_INSTN WHERE v_sDate BETWEEN BEGIN_DATE AND END_DATE)zz
     ON zz.INSTN_NO = SH.CR_MGBR;
    
    
    （2）"MAS"."PROC_BPM_CM_ACCT_DIV_DP"   迁移
   INSERT INTO tmp_BPM_INF_CI_ECCMRAMR 
   SELECT
       T1.CORPORATION,  
            T1.DAY_ID,       
            T1.ACCT_NO,      
            T1.ACCT_TYPE,    
            T1.EXT_ACCT_NO,  
            T1.CARD_NO,      
            T1.CUST_TYP,     
            /*
            CASE WHEN EXISTS (SELECT 1 FROM BPM_INF_CR_ACCT_REAL T2 WHERE T2.CR_CRD_TDACCT_NO = T1.ACCT_NO AND T2.CR_CRD_ACCT_TYP = 'TD') 
                 -- THEN (SELECT T3.CUM_NO FROM BPM_CM_EMP T3 WHERE SUBSTR(T1.ACCT_NO,1,9) = T3.INSTN_NO AND T3.EMP_PROPERTY = 'VIRTUAL' AND v_sDate BETWEEN T3.BEGIN_DATE AND T3.END_DATE)
                 THEN '' -- 若为卡下定期,客户经理赋为空
                 ELSE T1.CUM_NO END,  */
                 
            case when t1.cum_no is not null and t1.cum_no!='' then
                    T1.CUM_NO
                    else
                        -- (SELECT T3.CUM_NO FROM BPM_CM_EMP T3 WHERE SUBSTR(T1.ACCT_NO,1,9) = T3.INSTN_NO AND T3.EMP_PROPERTY = 'VIRTUAL' AND v_sDate BETWEEN T3.BEGIN_DATE AND T3.END_DATE)
           (SELECT T3.CUM_NO FROM tmp_BPM_CM_EMP_VIR T3 WHERE T3.INSTN_NO = SUBSTR(T1.ACCT_NO,1,9) )
                    end,  
            T1.CUM_NAME,     
            T1.CUST_NO,      
            T1.CUST_NAME,    
            T1.EC_CRT_OPR,   
            T1.EC_RELEND_DT, 
            T1.EC_EFFECT_STS,
            T1.SOURCE_ID    
   FROM BPM_INF_CI_ECCMRAMR T1
   WHERE T1.DAY_ID <= v_sDate AND T1.EC_RELEND_DT > v_sDate AND T1.CORPORATION = v_corp;-- 
    修改为
   INSERT INTO tmp_BPM_INF_CI_ECCMRAMR 
   SELECT
       T1.CORPORATION,  
            T1.DAY_ID,       
            T1.ACCT_NO,      
            T1.ACCT_TYPE,    
            T1.EXT_ACCT_NO,  
            T1.CARD_NO,      
            T1.CUST_TYP,     
            /*
            CASE WHEN EXISTS (SELECT 1 FROM BPM_INF_CR_ACCT_REAL T2 WHERE T2.CR_CRD_TDACCT_NO = T1.ACCT_NO AND T2.CR_CRD_ACCT_TYP = 'TD') 
                 -- THEN (SELECT T3.CUM_NO FROM BPM_CM_EMP T3 WHERE SUBSTR(T1.ACCT_NO,1,9) = T3.INSTN_NO AND T3.EMP_PROPERTY = 'VIRTUAL' AND v_sDate BETWEEN T3.BEGIN_DATE AND T3.END_DATE)
                 THEN '' -- 若为卡下定期,客户经理赋为空
                 ELSE T1.CUM_NO END,  */
                 
            case when t1.cum_no is not null and t1.cum_no!='' then
                    T1.CUM_NO
                    else
                        -- (SELECT T3.CUM_NO FROM BPM_CM_EMP T3 WHERE SUBSTR(T1.ACCT_NO,1,9) = T3.INSTN_NO AND T3.EMP_PROPERTY = 'VIRTUAL' AND v_sDate BETWEEN T3.BEGIN_DATE AND T3.END_DATE)
           T3.CUM_NO
                    end,  
            T1.CUM_NAME,     
            T1.CUST_NO,      
            T1.CUST_NAME,    
            T1.EC_CRT_OPR,   
            T1.EC_RELEND_DT, 
            T1.EC_EFFECT_STS,
            T1.SOURCE_ID    
   FROM BPM_INF_CI_ECCMRAMR T1
    LEFT JOIN tmp_BPM_CM_EMP_VIR T3
    ON T3.INSTN_NO = SUBSTR(T1.ACCT_NO,1,9)
   WHERE T1.DAY_ID <= v_sDate AND T1.EC_RELEND_DT > v_sDate AND T1.CORPORATION = v_corp;--

    （3）-- UPDATE修改
   update BPM_INF_CR_YXK t 
   set (t.EMP_NO,t.EMP_NAME)=
   (select a.EMP_NO,a.EMP_NAME from BPM_CM_EMP a where a.DEP_NO=t.EMP_DEPT and a.EMP_PROPERTY='VIRTUAL' and v_sDate between a.BEGIN_DATE and a.end_date) 
   where t.DAY_ID=v_sDate and t.EMP_NO is null;
    修改为
   update BPM_INF_CR_YXK t LEFT JOIN 
    BPM_CM_EMP a
    ON a.DEP_NO=t.EMP_DEPT and a.EMP_PROPERTY='VIRTUAL' and v_sDate between a.BEGIN_DATE and a.end_date
   set t.EMP_NO=a.EMP_NO,
    t.EMP_NAME=a.EMP_NAME
   where t.DAY_ID=v_sDate and t.EMP_NO is null;