在存储过程中为表添加月分区与日分区

实例

添加分区存储过程

CREATE OR REPLACE PROCEDURE PRO_ADD_TAB_PARTITIONS(V_RECE_CODE OUT VARCHAR2,
                                                   V_RECE_MSG  OUT VARCHAR2) IS

  V_ROW_COUNT NUMBER := 0;
  V_SQL       VARCHAR2(2000);
  V_DT        VARCHAR2(20);
  V_DT2       VARCHAR2(20);
  V_P_DT      VARCHAR2(20);
  V_MAX_PART  DATE;
  V_NEXT_MDAY DATE;
  TYPE TYPE_TBL IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  V_TBL_NAME TYPE_TBL;

BEGIN
  V_ROW_COUNT := V_ROW_COUNT + SQL%ROWCOUNT;

  --创建月分区
  --将需要增加分区的月表加在此处
  SELECT TABLE_NAME BULK COLLECT
    INTO V_TBL_NAME
    FROM USER_TABLES
   WHERE TABLE_NAME IN ('T_PRO_UPDATE_LOG');

  FOR I IN V_TBL_NAME.FIRST .. V_TBL_NAME.LAST LOOP
    --取出分区表中最大分区值
    SELECT TO_DATE(MAX(SUBSTR(PARTITION_NAME, 2)), 'yyyymm')
      INTO V_MAX_PART
      FROM USER_TAB_PARTITIONS
     WHERE TABLE_NAME = V_TBL_NAME(I);
    --当前时间下月末最后一天
    SELECT ADD_MONTHS(TRUNC(LAST_DAY(SYSDATE)), 1)
      INTO V_NEXT_MDAY
      FROM DUAL;

    --循环执行
    WHILE V_MAX_PART < V_NEXT_MDAY LOOP
      V_MAX_PART := ADD_MONTHS(V_MAX_PART, 1);
      V_P_DT     := TO_CHAR(V_MAX_PART, 'yyyymm');
      V_DT       := TO_CHAR(ADD_MONTHS(V_MAX_PART, 1), 'yyyymmdd');
      V_SQL      := 'alter table ' || V_TBL_NAME(I) || ' add PARTITION p' ||
                    V_P_DT || ' VALUES LESS THAN (TO_DATE(' || CHR(39) || V_DT ||
                    CHR(39) || ',' || CHR(39) || 'yyyymmdd' || CHR(39) || '))';
      EXECUTE IMMEDIATE V_SQL;
    END LOOP;
  END LOOP;

  --创建日分区
  --在日分区加的
  SELECT TABLE_NAME BULK COLLECT
    INTO V_TBL_NAME
    FROM USER_TABLES
   WHERE TABLE_NAME IN ('REL_TRANSACTION_TO_AEP_MSG','REL_TRANSACTION_TO_CMPP_MSG','REL_TRANSACTION_TO_CMPP_REPORT','LOG_AEP_DELIVER','LOG_AEP_REPORT','LOG_AEP_SUBMIT','LOG_AEP_SUBMIT_RESPONSE','LOG_CMPP_DELIVER','LOG_CMPP_DELIVER_RESPONSE','LOG_CMPP_REPORT','LOG_CMPP_REPORT_RESPONSE','LOG_CMPP_SUBMIT','LOG_CMPP_SUBMIT_RESPONSE','LOG_SMS_TRANSACTION');

  FOR I IN V_TBL_NAME.FIRST .. V_TBL_NAME.LAST LOOP
    --取出分区表中最大分区值
    SELECT TO_DATE(MAX(SUBSTR(PARTITION_NAME, 2)), 'yyyymmdd')
      INTO V_MAX_PART --获取最大分区值
      FROM USER_TAB_PARTITIONS
     WHERE TABLE_NAME = V_TBL_NAME(I);
    --当前时间下月末最后一天
    SELECT ADD_MONTHS(TRUNC(LAST_DAY(SYSDATE)), 1)
      INTO V_NEXT_MDAY
      FROM DUAL;

    --循环执行
    WHILE V_MAX_PART < V_NEXT_MDAY LOOP
      V_MAX_PART := V_MAX_PART + 1;
      V_DT       := TO_CHAR(V_MAX_PART, 'yyyymmdd');
      --new add 20160923 begin by hj
      V_DT2 := TO_CHAR(V_MAX_PART + 1, 'yyyymmdd');
      --end

      V_SQL := 'alter table ' || V_TBL_NAME(I) || ' add PARTITION p' || V_DT ||
               ' VALUES LESS THAN (TO_DATE(' || CHR(39) || V_DT2 || CHR(39) || ',' ||
               CHR(39) || 'yyyymmdd' || CHR(39) || '))';
      EXECUTE IMMEDIATE V_SQL;
    END LOOP;
  END LOOP;

  V_RECE_CODE := 'FINISH';
  V_RECE_MSG  := 'SUCCESS';
  --插入日志
  PRO_UPDATE_LOG(to_char(sysdate, 'yyyymmdd'),
                 '自动增加分区', --目标表名称
                 '',
                 'PRO_ADD_TAB_PARTITIONS', --目标存储过程名称
                 V_RECE_CODE,
                 V_RECE_MSG,
                 V_ROW_COUNT);
  --异常抛出
EXCEPTION
  WHEN OTHERS THEN
    V_RECE_CODE := 'FAIL';
    V_RECE_MSG  := SUBSTR(SQLERRM, 1, 100);
    --异常处理
    PRO_UPDATE_LOG(to_char(sysdate, 'yyyymmdd'),
                   '自动增加分区', --目标表名称
                   '',
                   'PRO_ADD_TAB_PARTITIONS', --目标存储过程名称
                   V_RECE_CODE,
                   V_RECE_MSG,
                   V_ROW_COUNT);
end PRO_ADD_TAB_PARTITIONS;

写日志存储过程

CREATE OR REPLACE PROCEDURE PRO_UPDATE_LOG(V_TIME       VARCHAR2,
                                           v_func_desc  VARCHAR2,
                                           V_TABLE_NAME VARCHAR2,
                                           V_PRO_NAME   VARCHAR2,
                                           V_RECE_CODE  VARCHAR2,
                                           V_RECE_MSG   VARCHAR2,
                                           V_ROW_COUNT  NUMBER) IS

BEGIN
  INSERT INTO T_PRO_UPDATE_LOG
    (ID,
     TIME,
     func_desc,
     TABLE_NAME,
     PRO_NAME,
     RECE_CODE,
     RECE_MSG,
     CREATE_TIME,
     ROW_COUNT)
  VALUES
    (SEQ_T_PRO_UPDATE_LOG.NEXTVAL,
     V_TIME,
     v_func_desc,
     V_TABLE_NAME,
     V_PRO_NAME,
     V_RECE_CODE,
     V_RECE_MSG,
     SYSDATE,
     V_ROW_COUNT);
  COMMIT;
END;