CREATE OR REPLACE PROCEDURE "FCT"."PROC_FCT_DSB_SERIES"(IN ACCOUNTING_DATE DATE) ------------------------------------------------------------------------------- LANGUAGE SQL SPECIFIC PROC_FCT_DSB_SERIES ------------------------------------------------------------------------------ -- 功能描述：描述该存储过程的用处 处理流程 -- 输入：当前程序调用日期8位数（如20140101） -- 源表： -- 目标表： -- 修改日志 -- 类型 作者 日期 备注 -- 创建 ------------------------------------------------------------------------------ BEGIN /*声明异常处理使用变量*/ DECLARE SQLCODE, SMY_SQLCODE INT DEFAULT 0; --SQLCODE DECLARE 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表动态SQL DECLARE DELETE_SQL2 VARCHAR(200); --删除SMY表动态SQL /*1.定义针对SQL异常情况的句柄(EXIT方式). 2.将出现SQL异常时在存储过程中的位置(SMY_STEPNUM),位置描述(SMY_STEPDESC)，SQLCODE(SMY_SQLCODE)记入表SMY_LOG中作调试用. 3.调用RESIGNAL重新引发异常,跳出存储过程执行体,对引发SQL异常之前存储过程体中所完成的操作进行回滚.*/ ---------------------------异常处理---------------------------BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND SET at_end=1; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN 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 SQLWARNING BEGIN 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@