oracle存储过程切割日志表

姚梨祎

CREATE OR REPLACE PROCEDURE proc_split_table
Authid Current_User
IS
-- sms
current_table_name1   VARCHAR2 (50);
create_table_str1     VARCHAR2 (200);
padding_table_str1    VARCHAR2 (200);
del_tabledata_str1    VARCHAR2 (200);
tmpvar                NUMBER;
BEGIN
tmpvar := 0;
current_table_name1 :=  'WHATYUSER_LOG4J_' || TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM');
create_table_str1 := 'CREATE TABLE ' || current_table_name1 || ' AS SELECT * FROM WHATYUSER_LOG4J WHERE 1=2';
padding_table_str1 := 'INSERT INTO ' || current_table_name1 || ' SELECT * FROM WHATYUSER_LOG4J WHERE OPERATE_TIME BETWEEN TRUNC ( ADD_MONTHS (SYSDATE, -1), ''MONTH'') AND TRUNC ( ADD_MONTHS (SYSDATE, 0), ''MONTH'')';
del_tabledata_str1 :=    'DELETE FROM WHATYUSER_LOG4J WHERE OPERATE_TIME BETWEEN TRUNC ( ADD_MONTHS (SYSDATE, -1), ''MONTH'') AND TRUNC ( ADD_MONTHS (SYSDATE, 0), ''MONTH'')';
DBMS_OUTPUT.put_line ( create_table_str1 );
DBMS_OUTPUT.put_line ( padding_table_str1 );
DBMS_OUTPUT.put_line ( del_tabledata_str1 );
EXECUTE IMMEDIATE create_table_str1;
EXECUTE IMMEDIATE padding_table_str1;
EXECUTE IMMEDIATE del_tabledata_str1;
EXECUTE IMMEDIATE 'CREATE INDEX idx1_'||current_table_name1||' ON '||current_table_name1||' ( USERID )';
EXECUTE IMMEDIATE 'CREATE INDEX idx2_'||current_table_name1||' ON '||current_table_name1||' ( BEHAVIOR )';
EXECUTE IMMEDIATE 'CREATE INDEX idx3_'||current_table_name1||' ON '||current_table_name1||' ( STATUS )';
EXECUTE IMMEDIATE 'CREATE INDEX idx4_'||current_table_name1||' ON '||current_table_name1||' ( LOGTYPE )';
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ( '['||SQLCODE||']'||SQLERRM);
RAISE;
END proc_split_table;
/

　　
　　将表WHATYUSER_LOG4J按照月份分割为WHATYUSER_LOG4J_201010这样的表