实例
添加分区存储过程
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;