oracle存储过程总结及优化方案

一、一个为指定表增加分区的存储过程：

/**
 * table_name 分割表名.
 * from_date 建立分区的开始时间.
 * to_date 建立分区的结束时间.
 */

create or replace procedure CHOUY_ADD_PARTITION(table_name in Varchar2, from_date IN DATE, to_date in DAte)

AS
   F_DATE DATE;
   T_DATE DATE;
   sql_stmt VARCHAR2 (1000);
   err_msg VARCHAR2 (300);
BEGIN
   F_DATE := trunc(from_date);
   T_DATE := trunc(to_date);
   while F_DATE < T_DATE
   LOOP
      BEGIN
         sql_stmt := 'alter table '
           || table_name
           || ' split partition P'
           || to_char(T_DATE, 'yyyymmdd')
           || ' at (to_date('''
           || to_char(F_DATE + 1, 'yyyymmdd')
           || ''',''yyyymmdd'')) '
           || 'into (partition P'
           || to_char(F_DATE, 'yyyymmdd')
           || ',partition P'
           || to_char(T_DATE, 'yyyymmdd')
           || ') ';
         execute immediate sql_stmt;
         commit;
         F_DATE := F_DATE + 1;
      EXCEPTION
         WHEN OTHERS THEN
          err_msg := ' partition unsuccessfully! Error Information:'
           || SQLERRM;
      END;
   END LOOP;
END;

 

 

技术资料：

Oracle存储过程编写经验和优化措施：  http://lusong163.itpub.net/post/14139/335461

Oracle存储过程学习：                       http://www.iteye.com/topic/288536