oracle存储过程创建表分区

CREATE OR REPLACE PROCEDURE BIP_MMS_PARTITION_PROC AS

 v_Mms_Task_Tab VARCHAR2(50); --表名
 v_Mms_Content_Tab VARCHAR2(50);
 v_Mms_User_Tab VARCHAR2(50);
 v_TableSpace VARCHAR2(20); --表空间
 v_PartPreFlag VARCHAR2(50); --分区名标识
 v_SqlCursor NUMBER; --游标
 v_SqlExec VARCHAR2(2000); --执行语句
 v_PartPreDate VARCHAR2(20); --分区日期
 v_RangeValue NUMBER;
 v_RangeDate NUMBER;
 v_Rows NUMBER(30) := 0;
 v_Num NUMBER(30) := 0;
 vErrInfo VARCHAR2(200);
 p_DateFrom NUMBER;
 p_PartNum NUMBER;
 p_Range NUMBER;
BEGIN

 v_Mms_Task_Tab := 'BIP_MMS_MT_TASK_LOG_TAB_TEST';
 v_Mms_Content_Tab := 'BIP_MMS_MT_CONTENT_TAB_TEST';
 v_Mms_User_Tab := 'BIP_MMS_MT_USER_LOG_TAB_TEST';
 -- 读取配置参数
 BEGIN
 SELECT TO_NUMBER(VALUE)
 INTO p_DateFrom
 FROM BIP_OTHERS_PROPERTIES_TAB
 WHERE NAME = 'p_DateFrom';
 SELECT TO_NUMBER(VALUE)
 INTO p_PartNum
 FROM BIP_OTHERS_PROPERTIES_TAB
 WHERE NAME = 'p_PartNum';
 SELECT TO_NUMBER(VALUE)
 INTO p_Range
 FROM BIP_OTHERS_PROPERTIES_TAB
 WHERE NAME = 'p_Range';
 EXCEPTION
 WHEN OTHERS THEN
 BEGIN
 p_DateFrom := 0;
 p_PartNum := 1;
 p_Range := 180;
 END;
 END;
 --记录存储过程添加分区
 INSERT INTO BIP_LOG_STAT_EXEC_TAB
 VALUES
 (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
 'BIP_MMS_PARTITION_PROC_ADD',
 'BEGIN');
 COMMIT;
 --ADD PARTITION 
 FOR i IN 1 .. p_PartNum LOOP
 --BIP_MMS_MT_CONTENT_TAB 添加分区
 v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
 dbms_output.put_line(v_PartPreDate);
 v_Num := 0;
 v_TableSpace := 'BIP_MMS_TS_TEST';
 v_PartPreFlag := 'MMS_MT_CONTENT';
 SELECT COUNT(*)
 INTO v_Num
 FROM user_tab_partitions
 WHERE table_name = v_Mms_Content_Tab
 AND SUBSTR(partition_name, 16, 8) = v_PartPreDate;
 IF v_Num < 1 THEN
 v_RangeDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
 v_RangeValue := v_RangeDate || '240000';
 dbms_output.put_line(v_RangeValue);
 v_SqlExec := 'ALTER TABLE ' || v_Mms_Content_Tab || ' ADD PARTITION ' ||
 v_PartPreFlag || '_' || v_PartPreDate ||
 ' VALUES LESS THAN(''' || v_RangeValue ||
 ''') TABLESPACE ' || v_TableSpace;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 END IF;
 --BIP_MMS_MT_TASK_LOG_TAB_TEST 添加分区
 v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
 v_Num := 0;
 v_TableSpace := 'BIP_MMS_TS_TEST';
 v_PartPreFlag := 'MMS_MT_TASK_LOG';
 SELECT COUNT(*)
 INTO v_Num
 FROM user_tab_partitions
 WHERE table_name = v_Mms_Task_Tab
 AND SUBSTR(partition_name, 17, 8) = v_PartPreDate;
 IF v_Num < 1 THEN
 v_RangeDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
 v_RangeValue := v_RangeDate || '240000';
 v_SqlExec := 'ALTER TABLE ' || v_Mms_Task_Tab || ' ADD PARTITION ' ||
 v_PartPreFlag || '_' || v_PartPreDate ||
 ' VALUES LESS THAN(''' || v_RangeValue ||
 ''') TABLESPACE ' || v_TableSpace;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 END IF;
 --BIP_MMS_MT_USER_LOG_TAB_TEST 添加分区
 v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
 v_Num := 0;
 v_TableSpace := 'BIP_MMS_TS_TEST';
 v_PartPreFlag := 'MMS_MT_USER_LOG';
 SELECT COUNT(*)
 INTO v_Num
 FROM user_tab_partitions
 WHERE table_name = v_Mms_User_Tab
 AND SUBSTR(partition_name, 17, 8) = v_PartPreDate;
 IF v_Num < 1 THEN
 v_RangeDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
 v_RangeValue := v_RangeDate || '240000';
 v_SqlExec := 'ALTER TABLE ' || v_Mms_User_Tab || ' ADD PARTITION ' ||
 v_PartPreFlag || '_' || v_PartPreDate ||
 ' VALUES LESS THAN(''' || v_RangeValue ||
 ''') TABLESPACE ' || v_TableSpace;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 END IF;
 END LOOP;
 COMMIT;

 INSERT INTO BIP_LOG_STAT_EXEC_TAB
 VALUES
 (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'), 'BIP_MMS_PARTITION_PROC_ADD', 'END');
 COMMIT;

 --DELETE PARTITION
 INSERT INTO BIP_LOG_STAT_EXEC_TAB
 VALUES
 (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
 'BIP_MMS_PARTITION_PROC_DEL',
 'BEGIN');
 COMMIT;

 BEGIN
 v_PartPreFlag := 'MMS_MT_CONTENT' || '_' ||
 TO_CHAR(SYSDATE - p_Range, 'yyyymmdd');
 dbms_output.put_line(v_PartPreFlag);
 v_SqlExec := 'ALTER TABLE ' || v_Mms_Content_Tab ||
 ' TRUNCATE PARTITION ' || v_PartPreFlag;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 dbms_output.put_line(v_PartPreFlag || ' truncated');
 
 v_SqlExec := 'ALTER TABLE ' || v_Mms_Content_Tab || ' DROP PARTITION ' ||
 v_PartPreFlag;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 dbms_output.put_line(v_PartPreFlag || ' dropped');
 END;

 BEGIN
 v_PartPreFlag := 'MMS_MT_TASK_LOG' || '_' ||
 TO_CHAR(SYSDATE - p_Range, 'yyyymmdd');
 dbms_output.put_line(v_PartPreFlag);
 v_SqlExec := 'ALTER TABLE ' || v_Mms_Task_Tab || ' TRUNCATE PARTITION ' ||
 v_PartPreFlag;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 dbms_output.put_line(v_PartPreFlag || ' truncated');
 
 v_SqlExec := 'ALTER TABLE ' || v_Mms_Task_Tab || ' DROP PARTITION ' ||
 v_PartPreFlag;
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 dbms_output.put_line(v_PartPreFlag || ' dropped');
 END;

 BEGIN
 v_PartPreFlag := 'MMS_MT_USER_LOG' || '_' ||
 TO_CHAR(SYSDATE - p_Range, 'yyyymmdd');
 dbms_output.put_line(v_PartPreFlag);
 v_SqlExec := 'ALTER TABLE ' || v_Mms_User_Tab || ' TRUNCATE PARTITION ' ||
 v_PartPreFlag;
 dbms_output.put_line(v_SqlExec);
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 dbms_output.put_line(v_PartPreFlag || ' truncated');
 
 v_SqlExec := 'ALTER TABLE ' || v_Mms_User_Tab || ' DROP PARTITION ' ||
 v_PartPreFlag;
 v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
 v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
 DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
 dbms_output.put_line(v_PartPreFlag || ' dropped');
 END;

 COMMIT;

 INSERT INTO BIP_LOG_STAT_EXEC_TAB
 VALUES
 (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
 'BIP_MMS_PARTITION_PROC_DEL',
 'END');
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 BEGIN
 ROLLBACK;
 dbms_output.put_line(TO_CHAR(SQLCODE));
 vErrInfo := SUBSTR(SQLERRM, 1, 200);
 dbms_output.put_line(TO_CHAR(vErrInfo));
 INSERT INTO BIP_LOG_STAT_EXEC_TAB
 VALUES
 (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
 'BIP_MMS_PARTITION_PROC_ERROR',
 vErrInfo);
 COMMIT;
 END;
 
end bip_mms_partition_proc;