一次性每天自动结算所有存储过程，失败的重新调用

CREATE TABLE REPORT_SETTLE_LIST
(
"REPORT_NAME" VARCHAR2(255) NOT NULL,
"PROCEDURE_NAME" VARCHAR2(255) NOT NULL,
"SETTLE_DAYS" NUMBER NOT NULL,
"IS_ENABLED" CHAR(1) DEFAULT 'Y' NOT NULL,
"SETTLE_TYPE" VARCHAR(10) DEFAULT 'DAY') ;

COMMENT ON COLUMN "REPORT_SETTLE_LIST"."PROCEDURE_NAME" IS '存储过程名称';
COMMENT ON COLUMN "REPORT_SETTLE_LIST"."REPORT_NAME" IS '报表名称';
COMMENT ON COLUMN "REPORT_SETTLE_LIST"."SETTLE_DAYS" IS '结算天数';

 

CREATE TABLE "REPORT_SETTLE_LOG"
(
"LOG_ID" NUMBER NOT NULL,
"PROCEDURE_NAME" VARCHAR2(255) NOT NULL,
"SETTLE_DATE" VARCHAR(20) NOT NULL,
"SETTLE_STATUS" VARCHAR2(50) NOT NULL,
"INSERT_TIME" TIMESTAMP(6) DEFAULT SYSDATE NOT NULL,
"RELOAD_TIME" TIMESTAMP(6) DEFAULT NULL,
CLUSTER PRIMARY KEY("LOG_ID"))) ;

CREATE OR REPLACE   PROCEDURE "NC_MLC"."REPORT_SETTLE_ALL" (v_settle_date VARCHAR2) IS
v_rpt_date      VARCHAR2(20);
v_rpt_proc_name VARCHAR2(100);
v_days          INT;
v_real_date     VARCHAR2(20);
v_string        VARCHAR2(2000);
BEGIN
/*1. 先重新之前有结算失败记录的报表，重新结算后将日志状态改成 'RELOAD'*/
FOR ERR_LIST IN (
SELECT PROCEDURE_NAME, SETTLE_DATE
FROM REPORT_SETTLE_LOG
WHERE SETTLE_STATUS = 'Failed'
GROUP BY PROCEDURE_NAME, SETTLE_DATE
) LOOP
v_rpt_proc_name := ERR_LIST.PROCEDURE_NAME;
v_rpt_date := ERR_LIST.SETTLE_DATE;
UPDATE REPORT_SETTLE_LOG
SET RELOAD_TIME = SYSDATE,
SETTLE_STATUS = 'Reload'
WHERE SETTLE_STATUS = 'Failed'
AND PROCEDURE_NAME = v_rpt_proc_name
AND SETTLE_DATE = v_rpt_date;
COMMIT;
v_string := 'BEGIN ' || v_rpt_proc_name || '(''' || v_rpt_date || '''); END;';
EXECUTE IMMEDIATE v_string;
END LOOP;
/*2. 每天定时结算上一个运营日的普通报表*/
FOR REPORT_LIST IN (
SELECT PROCEDURE_NAME, SETTLE_DAYS
FROM REPORT_SETTLE_LIST
WHERE IS_ENABLED = 'Y' AND SETTLE_TYPE='DAY'
) LOOP
v_rpt_proc_name := REPORT_LIST.PROCEDURE_NAME;
v_days := REPORT_LIST.SETTLE_DAYS;
WHILE v_days >= 1 LOOP
v_real_date := TO_CHAR(TO_DATE(v_settle_date, 'YYYY-MM-DD') - v_days + 1, 'YYYY-MM-DD');
v_string := 'BEGIN ' || v_rpt_proc_name || '(''' || v_real_date || '''); END;';
EXECUTE IMMEDIATE v_string;
v_days := v_days - 1;
END LOOP;
END LOOP;
/*3、每月1号结算上月周期的月度报表*/
IF EXTRACT(DAY FROM v_settle_date) = 1 THEN
FOR REPORT_LIST IN (
SELECT PROCEDURE_NAME, SETTLE_DAYS
FROM REPORT_SETTLE_LIST
WHERE IS_ENABLED = 'Y' AND SETTLE_TYPE='MONTH'
) LOOP
v_rpt_proc_name := REPORT_LIST.PROCEDURE_NAME;
v_rpt_date :=TO_CHAR(ADD_MONTHS(TO_DATE(v_settle_date),-1),'YYYY-MM');
v_string := 'BEGIN ' || v_rpt_proc_name || '(''' || v_rpt_date || '''); END;';
--PRINT(v_string);
EXECUTE IMMEDIATE v_string;
END LOOP;
END IF;
END;