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;
