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 存储过程 <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms
- –> <!—ecms -ecms
- $velocityCount
–> <!—ecms -ecms
–>
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 <a style="color:transparent">来@源gao*daima.com搞@代#码网</a>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;
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：oracle 存储过程创建表分区
转载请注明原文链接：oracle 存储过程创建表分区
