CREATE OR REPLACE PROCEDURE "FCT"."PROC_FCT_DSB_SERIES"(IN ACCOUNTING_DATE DATE)-------------------------------------------------------------------------------LANGUAGE SQLSPECIFIC PROC_FCT_DSB_SERIES-------------------------------------------------------------------------------- 功能描述：描述该存储过程的用处  处理流程-- 输入：当前程序调用日期8位数（如20140101）-- 源表：-- 目标表：-- 修改日志-- 类型    作者    日期      备注-- 创建   ------------------------------------------------------------------------------BEGIN/*声明异常处理使用变量*/DECLARE SQLCODE, SMY_SQLCODE INT DEFAULT 0;            --SQLCODEDECLARE SMY_STEPNUM INT DEFAULT 1;                     --过程内部位置标记DECLARE SMY_STEPDESC VARCHAR(100) DEFAULT '';          --过程内部位置描述DECLARE SMY_DATE DATE;                                 --临时日期变量DECLARE SMY_RCOUNT INT;                                --DML语句作用记录数DECLARE SMY_PROCNM VARCHAR(100);                       --存储过程名称DECLARE AT_END SMALLINT DEFAULT 0;                     --SQL查询结果结束标志/*声明存储过程使用变量*/DECLARE CUR_YEAR SMALLINT;                             --年DECLARE CUR_MONTH SMALLINT;                            --月DECLARE CUR_DAY INTEGER;                               --日DECLARE CLEAR_MONTH_DAY INTEGER;                       --清理数据日DECLARE CLEAR_MONTH_DATE DATE;                         --清理数据日期DECLARE YR_FIRST_DAY DATE;                             --本年初1月1日DECLARE QTR_FIRST_DAY DATE;                            --本季度第1日DECLARE MONTH_FIRST_DAY DATE;                          --本月第1日DECLARE NEXT_YR_FIRST_DAY DATE;                        --下年1月1日DECLARE NEXT_QTR_FIRST_DAY DATE;                       --下季度第1日DECLARE NEXT_MONTH_FIRST_DAY DATE;                     --下月第1日DECLARE MONTH_DAY SMALLINT;                            --本月天数DECLARE YR_DAY SMALLINT;                               --本年天数DECLARE QTR_DAY SMALLINT;                              --本季度天数DECLARE TMP_YEAR                   CHAR(4) ;                    --年(YYYY)DECLARE TMP_MON                    CHAR(2) ;                    --月(MM)DECLARE TMP_YMD                     CHAR(8);                     --年月(YYYYMM)DECLARE TMP_DAY                    CHAR(2) ;                    --日(DD)DECLARE LST_TMP_YEAR           CHAR(4) ;                    --上年(YYYY)DECLARE PRE_LST_TMP_YEAR  CHAR(4) ;                       --前年(YYYY)DECLARE LST_TMP_MON            CHAR(2) ;                    --上月(MM)DECLARE MTH_DAY                   INT ;                        --月数(INTEGER)DECLARE YEAR_DAY                   INT ;                        --年初到当前时间的天数(INTEGER)DECLARE LAST_MTH_DAY             INT ;                        --上月末到年初的天数(INTEGER)DECLARE PD_DAY                        DATE;                                                 --上日DECLARE PTD_DAY                        DATE;                                                 --旬初DECLARE MI_DAY                        DATE;                                                 --月初DECLARE QI_DAY                        DATE;                                                 --月初DECLARE YI_DAY                        DATE;                                                 --月初DECLARE CP_DAY                        DATE;                                                 --月初DECLARE CP_YI_DAY                    DATE;                                                 --月初DECLARE    MTH                                SMALLINT;                                         --月份数(INT)DECLARE MAX_ACG_DT DATE;                               --最大会计日期DECLARE DELETE_SQL1 VARCHAR(200);                      --删除HIS表动态SQLDECLARE DELETE_SQL2 VARCHAR(200);                      --删除SMY表动态SQL/*1.定义针对SQL异常情况的句柄(EXIT方式).  2.将出现SQL异常时在存储过程中的位置(SMY_STEPNUM),位置描述(SMY_STEPDESC)，SQLCODE(SMY_SQLCODE)记入表SMY_LOG中作调试用.  3.调用RESIGNAL重新引发异常,跳出存储过程执行体,对引发SQL异常之前存储过程体中所完成的操作进行回滚.*/---------------------------异常处理---------------------------BEGINDECLARE CONTINUE HANDLER FOR NOT FOUNDSET at_end=1;DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN    SET SMY_SQLCODE = SQLCODE;  ROLLBACK;  INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE, NULL, CURRENT TIMESTAMP);  COMMIT;  RESIGNAL;END;DECLARE CONTINUE HANDLER FOR SQLWARNINGBEGIN  SET SMY_SQLCODE = SQLCODE;  INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE, NULL, CURRENT TIMESTAMP);  COMMIT;END;---------------------------异常处理---------------------------END/*初始化变量*/SET TMP_YEAR = SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),1,4);            --年SET TMP_MON  = SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),6,2);            --月SET TMP_YMD  = TO_CHAR(ACCOUNTING_DATE,'YYYYMMDD');                          --年月日SET TMP_DAY  = SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),9,2);            --日SELECT SUBSTR(TO_CHAR((ACCOUNTING_DATE - 1 YEAR),'YYYY-MM-DD'),1,4), SUBSTR(TO_CHAR((ACCOUNTING_DATE - 1 MONTHS),'YYYY-MM-DD'),6,2),SUBSTR(TO_CHAR((ACCOUNTING_DATE - 2 YEAR),'YYYY-MM-DD'),1,4)INTO LST_TMP_YEAR,LST_TMP_MON ,PRE_LST_TMP_YEAR FROM SYSIBM.SYSDUMMY1;SELECT INT(SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYYMMDD'),5,2)) INTO MTH_DAY FROM SYSIBM.SYSDUMMY1 ;SELECT (DAYS(ACCOUNTING_DATE)-DAYS(TO_DATE(TMP_YEAR||'-01-01','YYYY-MM-DD'))+1)  INTO YEAR_DAY FROM SYSIBM.SYSDUMMY1 ;SELECT (DAYS(TO_DATE(TMP_YEAR||TMP_MON||'01','YYYY-MM-DD'))-DAYS(TO_DATE(TMP_YEAR||'-01-01','YYYY-MM-DD')))  INTO LAST_MTH_DAY FROM SYSIBM.SYSDUMMY1 ;SET        PD_DAY          = ACCOUNTING_DATE - 1 DAY;                                                            --上日SET        MI_DAY         = TO_DATE(SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),1,7)||'-01','YYYY-MM-DD') - 1 DAY;                                    --月初SELECT        CASE WHEN SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),9,2)<=10         THEN MI_DAY                                             WHEN (SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),9,2)>10                                                                   AND SUBSTR(TO_CHAR(ACCOUNTING_DATE,'YYYY-MM-DD'),9,2)<=20) THEN TO_DATE(TMP_YEAR||TMP_MON||'10','YYYY-MM-DD')                                             ELSE TO_DATE(TMP_YEAR||TMP_MON||'20','YYYY-MM-DD') END                                             INTO PTD_DAY        FROM            SYSIBM.DUAL;                --旬初SELECT        CASE WHEN TMP_MON IN ('01','02','03') THEN TO_DATE(TMP_YEAR||'-01-01','YYYY-MM-DD') -1 DAY                                                WHEN TMP_MON IN ('04','05','06') THEN TO_DATE(TMP_YEAR||'-03-31','YYYY-MM-DD')                                                WHEN TMP_MON IN ('07','08','09') THEN TO_DATE(TMP_YEAR||'-06-30','YYYY-MM-DD')                                                ELSE TO_DATE(TMP_YEAR||'-09-30','YYYY-MM-DD') END INTO QI_DAY FROM SYSIBM.DUAL;        --季初SET        YI_DAY         = TO_DATE(LST_TMP_YEAR||'-12-31','YYYY-MM-DD');                --年初SET        CP_DAY         = CASE WHEN TMP_YMD <> TMP_YEAR||'0229' THEN TO_DATE(TMP_YMD -10000,'YYYY-MM-DD') ELSE TO_DATE((TMP_YEAR-1)||'0228','YYYY-MM-DD') END;     --同期SET        CP_YI_DAY     = TO_DATE(PRE_LST_TMP_YEAR ||'-12-31','YYYY-MM-DD');        --同期年初SELECT    INT(SUBSTR(TMP_YMD,5,2))    INTO MTH    FROM SYSIBM.DUAL;--月数(INT)SET SMY_PROCNM = 'PROC_FCT_DSB_SERIES';SET SMY_DATE=ACCOUNTING_DATE;SELECT MAX(LAST_ETL_ACG_DT) INTO MAX_ACG_DT FROM SMY.ORG_CST_SMY;SET DELETE_SQL1='ALTER TABLE HIS.ORG_CST_SMY ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE'; --清空HIS表SET DELETE_SQL2='ALTER TABLE SMY.ORG_CST_SMY ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE'; --清空SMY表/*Delete日志表,条件SMY_PROCNM=当前存储过程名字,SMY_DATE=ACCOUNTING_DATE,并插入新的起始标志*/DELETE FROM SMY.SMY_LOG WHERE SMY_ACT_DT = SMY_DATE AND SMY_PROC_NM = SMY_PROCNM;INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, 'PROCEDURE START.', SMY_SQLCODE, SMY_RCOUNT, CURRENT TIMESTAMP);/*存储过程Begin*//*Step.1 : 数据恢复与备份*/SET SMY_STEPNUM = SMY_STEPNUM + 1 ;SET SMY_STEPDESC = '数据恢复与备份';IF MAX_ACG_DT=ACCOUNTING_DATE THEN   EXECUTE IMMEDIATE DELETE_SQL2;   COMMIT;   INSERT INTO SMY.ORG_CST_SMY SELECT * FROM HIS.ORG_CST_SMY;   COMMIT;   GET DIAGNOSTICS SMY_RCOUNT = ROW_COUNT;   INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE ,SMY_RCOUNT, CURRENT TIMESTAMP);ELSE   EXECUTE IMMEDIATE DELETE_SQL1;   COMMIT;   INSERT INTO HIS.ORG_CST_SMY SELECT * FROM SMY.ORG_CST_SMY;   COMMIT;   GET DIAGNOSTICS SMY_RCOUNT = ROW_COUNT;   INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE ,SMY_RCOUNT, CURRENT TIMESTAMP);END IF;GET DIAGNOSTICS SMY_RCOUNT = ROW_COUNT;INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE ,SMY_RCOUNT, CURRENT TIMESTAMP);COMMIT;/*Step.2 : 删除目标表数据*/SET SMY_STEPNUM = SMY_STEPNUM + 1 ;SET SMY_STEPDESC = '删除目标表数据';DELETE FROM FCT.FCT_DSB_SERIES WHERE DAY_ID = ACCOUNTING_DATE ;GET DIAGNOSTICS SMY_RCOUNT = ROW_COUNT;INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE ,SMY_RCOUNT, CURRENT TIMESTAMP);COMMIT;/*Step.3 : update最后更新日期*/SET SMY_STEPNUM = SMY_STEPNUM + 1 ;SET SMY_STEPDESC = 'update最后更新日期';UPDATE FCT.FCT_DSB_SERIES SET LAST_ETL_ACG_DT = ACCOUNTING_DATE;GET DIAGNOSTICS SMY_RCOUNT = ROW_COUNT;--INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE ,SMY_RCOUNT, CURRENT TIMESTAMP);/*Step.4 : 标记存储过程结束*/SET SMY_STEPNUM = SMY_STEPNUM + 1 ;SET SMY_STEPDESC = '存储过程结束!';GET DIAGNOSTICS SMY_RCOUNT = ROW_COUNT;--INSERT INTO SMY.SMY_LOG VALUES(SMY_PROCNM, SMY_DATE, SMY_STEPNUM, SMY_STEPDESC, SMY_SQLCODE ,SMY_RCOUNT, CURRENT TIMESTAMP);COMMIT;END@