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这样的表