- 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;
来源: http://www.phpxs.com/code/1008596/