动态使用存储过程给分区表建立分区

CREATE OR REPLACE Procedure add_partition(v_table_name         in varchar2,
                                          v_tablespace_name_in in varchar2,
                                          v_begin_date         in varchar2,
                                          v_end_date           in varchar2) is
 
  -- Local variables here
  i integer;

  ---分区表信息
  cursor cur_utp(v_table_name in user_tab_partitions.table_name%TYPE) is
    select *
      from (select utp.table_name,
                   utp.tablespace_name,
                   utp.partition_name,
                   utp.high_value,
                   utp.high_value_length,
                   utp.partition_position
              from user_tab_partitions utp
             where utp.table_name = UPPER(v_table_name)
             order by utp.partition_position desc) utp
     where rownum = 1;

  v_high_value         varchar2(255); --less than value信息
  v_partition_max_date timestamp; ----less than value信息的 timestamp表示形式
  v_sqlexec            VARCHAR2(2000); --DDL语句变量
  v_count              number := 0;
  v_interver           number := 1; --步长间隔 单位(天)
  v_part_name_header   varchar2(20) := 'p';
  v_part_name          varchar2(2000); --分区名
  v_partition_num      number := 0;
  v_tablespace_name    varchar2(200);
  v_end_date_inner     timestamp;
  
begin
  v_interver         := 1 ;
  v_part_name_header := upper(v_part_name_header);

  v_end_date_inner := trunc(to_timestamp(v_end_date,
                                         'syyyy-mm-dd hh24:mi:ss.ff'));
  --取值
  for utp in cur_utp(v_table_name) loop
    v_high_value         := substr(utp.high_value,
                                   11,
                                   10);
    v_partition_max_date := to_timestamp(v_high_value,
                                         'syyyy-mm-dd hh24:mi:ss.ff');
    if (to_timestamp(v_begin_date, 'syyyy-mm-dd hh24:mi:ss.ff') >
       v_partition_max_date) then
      v_partition_max_date := to_timestamp(v_begin_date,
                                           'syyyy-mm-dd hh24:mi:ss.ff');
    end if;
    v_part_name := utp.partition_name;
  
    --如果没有给默认值
    if (v_tablespace_name_in is null) then
      -- v_tablespace_name
      v_tablespace_name := utp.tablespace_name;
    else
      v_tablespace_name := v_tablespace_name_in;
    end if;
  end loop;

  if (v_part_name_header =
     substr(v_part_name, 1, length(v_part_name_header))) then
    v_partition_num := to_number(substr(v_part_name,
                                        length(v_part_name_header) + 1,
                                        length(v_part_name)));
    v_partition_num := to_number(to_char(to_date(v_partition_num,'yyyymmdd')+1,'yyyymmdd'));
  else
    dbms_output.put_line('not expect part_name header user default:' ||
                         v_part_name);
    v_partition_num := 0;
  end if;

  i                    := 0;
  v_partition_max_date := v_partition_max_date + v_interver;
 
 while v_partition_max_date <= v_end_date_inner loop
    
    v_SqlExec := 'ALTER TABLE ' || v_table_name || ' ADD PARTITION ' ||
                 v_part_name_header || (to_number(to_char(to_date(v_partition_num,'yyyymmdd')+i,'yyyymmdd'))) ||
                 ' values less than(TIMESTAMP''' ||
                 to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff') ||
                 ''') TABLESPACE ' || v_tablespace_name;
    dbms_output.put_line('创建 表分区' || i || '=' || v_SqlExec);
    --alter table IP_CONNRATE_LIMITEX_LOG add partition part_0002 values less than(TIMESTAMP'2010-08-31 00:00:00.000000') tablespace NASP_IP_LOG_TABLESPACE;
    DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
    v_partition_max_date := v_partition_max_date + v_interver;
    i                    := i + 1;
  
  end loop;
  
  v_count := v_count + i;
  dbms_output.put_line('add partition count:' || v_count);

end add_partition;