oracle分区交换存储过程,oracle 存储过程创建表分区

oracle 存储过程创建表分区 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_PartPreFl

oracle 存储过程创建表分区

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;