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@
