按月添加分区的存储过程

create or replace procedure guan_add_partition
/* 
  /*为一个用户下所有分区表自动增加分区.分区的列为date类型,分区名类似:p200706. 
  /*create by David 
  */
 as
  v_table_name     varchar2(50);
  v_partition_name varchar2(50);
  v_month          char(6);
  v_add_month_1    char(6);
  v_sql_string     varchar2(2000);
  v_add_month      varchar2(20);

  cursor cur_part is
    select distinct u.table_name, max(p.partition_name) max_part_name
      from user_tables u, user_tab_partitions p
     where u.table_name = p.table_name
       and u.partitioned = 'YES'
     group by u.table_name;

Begin
  select to_char(sysdate, 'yyyymm') into v_month from dual;
  select to_char(add_months(sysdate, 1), 'yyyymm')
    into v_add_month_1
    from dual;
  select to_char(add_months(trunc(sysdate, 'mm'), 2), 'yyyy-mm-dd')
    into v_add_month
    from dual;
  open cur_part;

  loop
    fetch cur_part
      into v_table_name, v_partition_name;
    exit when cur_part%notfound;
    if to_number(substr(v_partition_name, 2)) <=
       to_number(substr(v_month, 1)) then
      v_sql_string := 'alter table ' || ''',''yyyy-mm-dd'') ) ';
      execute immediate v_sql_string;
    else
      null;
    end if;
  end loop;
  close cur_part;

end;