oracle定时存储过程抽取数据

CREATE OR REPLACE PROCEDURE P_CSG_FASTREPAIR_MONTH_DAY IS
  TEMPPK          VARCHAR2(32) := SYS_GUID(); -- 审计日志表主键
  V_PROVINCE_CODE VARCHAR2(4) := '';

  V_DATE DATE;
  CURSOR C_MONTH_DAY IS --声明显式游标
    SELECT FDATE
      FROM (SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 AS FDATE
              FROM DUAL
            CONNECT BY LEVEL <= 31) T
     WHERE TO_CHAR(FDATE, 'MM') = TO_CHAR(SYSDATE, 'MM');
  C_ROW C_MONTH_DAY%ROWTYPE;

BEGIN
  --（1）汇总开始时，插入日志
  /** P_SPROC_COLLECT_LOG_NEW(TEMPPK,
                      '月维度日明细',
                      'P_CSG_FASTREPAIR_MONTH_DAY',
                      'SP_CSG_FASTREPAIR_STATISTICS',
                      0,
                      NULL,
                      0); */

  --获取省编码 如0300
  V_PROVINCE_CODE := F_DM_GET_CONFIG_VALUE('ct.sproc.electurnover.provinceCode.headquarters');

  --循环本月插入每天数据
  FOR C_ROW IN C_MONTH_DAY LOOP
    V_DATE := C_ROW.FDATE;
    MERGE INTO SP_CSG_FASTREPAIR_STATISTICS SCFS
    USING (SELECT 'P' || TT0.PROVINCE_CODE || 'B' || TT0.BUREAU_CODE || 'MD' ||
                  TO_CHAR(V_DATE, 'yyyymmdd') AS ID,
                  TT0.PROVINCE_CODE AS PROVINCE_CODE,
                  TT0.BUREAU_CODE AS BUREAU_CODE,
                  TT1.DAY_TOTAL AS DAY_TOTAL
             FROM (SELECT A.PROVINCE_CODE, A.BUREAU_CODE
                     FROM SPROC_CSG_COM_TJDWWD A
                    WHERE A.PROVINCE_CODE = SUBSTR(V_PROVINCE_CODE, 0, 2)
                      AND (A.BUREAU_CODE != V_PROVINCE_CODE OR
                          A.BUREAU_CODE IN ('0800', '0900'))) TT0,
                  (SELECT T1.PROVINCE_CODE,
                          T1.BUREAU_CODE,
                          COUNT(*) DAY_TOTAL --当天总数
                     FROM SP_PD_FAULT T1,
                          (SELECT T3.PROVINCE_CODE,
                                  T3.BUREAU_CODE,
                                  T3.FAULT_ID,
                                  T5.FAULT_OCCUR_TIME,
                                  T3.FAULT_REPORT_FROM
                             FROM SP_PD_FAULT_REPORT T3,
                                  (SELECT T4.FAULT_ID,
                                          MIN(T4.FAULT_OCCUR_TIME) FAULT_OCCUR_TIME
                                     FROM SP_PD_FAULT_REPORT T4
                                    GROUP BY T4.FAULT_ID) T5
                            WHERE (T3.IS_MERGE = '1' OR T3.IS_MERGE IS NULL)
                              AND T3.FAULT_ID = T5.FAULT_ID) T2
                    WHERE T1.ID = T2.FAULT_ID
                      AND T2.PROVINCE_CODE IS NOT NULL
                      AND T2.BUREAU_CODE IS NOT NULL
                      AND T2.PROVINCE_CODE = SUBSTR(V_PROVINCE_CODE, 0, 2)
                      AND T1.PROVINCE_CODE IS NOT NULL
                      AND T1.BUREAU_CODE IS NOT NULL
                      AND TRUNC(T2.FAULT_OCCUR_TIME) = TRUNC(V_DATE)
                    GROUP BY T1.PROVINCE_CODE, T1.BUREAU_CODE) TT1
            WHERE TT0.BUREAU_CODE = TT1.BUREAU_CODE(+)) TEMP
    ON (SCFS.ID = TEMP.ID)
    WHEN MATCHED THEN
      UPDATE
         SET SCFS.UPDATE_TIME = SYSDATE, SCFS.DAY_TOTAL = TEMP.DAY_TOTAL
    WHEN NOT MATCHED THEN
      INSERT
        (SCFS.ID,
         SCFS.PROVINCE_CODE,
         SCFS.BUREAU_CODE,
         SCFS.UPDATE_TIME,
         SCFS.LOADING_TIME,
         SCFS.STATISTICS_TYPE,
         SCFS.STATISTICS_DAY,
         SCFS.STATISTICS_MONTH,
         SCFS.STATISTICS_YEAR,
         SCFS.DAY_TOTAL)
      VALUES
        (TEMP.ID,
         TEMP.PROVINCE_CODE,
         TEMP.BUREAU_CODE,
         SYSDATE,
         SYSDATE,
         21,
         TO_DATE(TO_CHAR(V_DATE, 'yyyy/mm/dd'), 'yyyy/mm/dd'),
         TO_CHAR(V_DATE, 'mm'),
         TO_CHAR(V_DATE, 'yyyy'),
         TEMP.DAY_TOTAL);
  END LOOP;

  --设置本月趋势图的历史日峰值和日均值
  MERGE INTO SP_CSG_FASTREPAIR_STATISTICS SCFS1
  USING (
    WITH T2 AS
     (SELECT T3.PROVINCE_CODE,
             T3.BUREAU_CODE,
             T3.FAULT_ID,
             T5.FAULT_OCCUR_TIME,
             T3.FAULT_REPORT_FROM
        FROM SP_PD_FAULT_REPORT T3,
             (SELECT T4.FAULT_ID, MIN(T4.FAULT_OCCUR_TIME) FAULT_OCCUR_TIME
                FROM SP_PD_FAULT_REPORT T4
               GROUP BY T4.FAULT_ID) T5
       WHERE (T