自动化分区脚本：按天/月/年操作

declare
  v_part_type int;
  /* 
  20161212  按天分区 type:1
  201612    按月分区 type:2
  2016      按年分区 type:3
  161212    按天分区 type:4
            自动分区 type:5
  */
  v_part_key_value    varchar2(20);
  v_part_prefix       varchar2(50);
  v_part_name         varchar2(252);
  v_col_type          varchar2(64);
  v_part_length       int;
  v_part_key_length   int;
  v_part_key_position int;
  type cur_type is ref cursor;
  my_cur              sys_refcursor;
  val                 varchar2(1000);
  v_year              varchar2(50);
  v_max_part_name     varchar2(256);
  v_maxdate_part_name varchar2(256);
  v_part_tablespace   varchar2(256);
  v_part_sums         int;
begin

  for v_minp_rec in (select a.table_owner,
                            a.table_name,
                            a.partition_name as minpart
                       from dba_tab_partitions a,

                            (select table_owner,
                                    table_name,
                                    decode(max(partition_position) - 1,
                                           0,
                                           1,
                                           max(partition_position) - 1) as part_posi
                               from dba_tab_partitions t
                              where table_owner NOT in ('SYS', 'SYSTEM')
                                and t.table_name = 'F_PASSENGER_STOP'
                              group by table_owner, table_name) b
                      where a.table_owner = b.table_owner
                        and a.table_name = b.table_name
                        and a.partition_position = b.part_posi) loop
    v_part_name := v_minp_rec.minpart;
    --------------------------------------------------获取分区类型---------------------------------------------------   
    select length(v_part_name) - regexp_instr(reverse(v_part_name), '\D')
      into v_part_key_position
      from dual; -----获取分区key开始位置
    v_part_key_value  := substr(v_part_name, v_part_key_position + 2); -----获取分区key
    v_part_key_length := length(v_part_key_value); -----获取分区
    if v_part_key_length = 8 then
      v_part_type := 1;
    elsif v_part_key_length = 6 then
      if regexp_instr(v_part_key_value, '201') = 1 then
        ---------判定是yyyymm
        v_part_type := 2;
      else
        ---------判定是yymmdd
        v_part_type := 4;
      end if;