oracle split cursor,自动SPLIT ORACLE PARTITION

create or replace procedure pro_split_subpart(v_tab varchar2) as

cursor c_subp(v_1 varchar2) is

select *

from user_tab_subpartitions

where table_name = upper(v_1)

and subpartition_name like '%1106%';

v_sql varchar2(4000);

v_p_date varchar2(20);

v_p_prex varchar2(200);

v_p_date2 date;

v_p_date3 date;

v_i number;

v_sql2 varchar2(2000);

v_sql3 varchar2(2000);

v_sql4 varchar2(2000);

begin

for a in c_subp(v_tab) loop

v_sql := 'alter table ' || a.table_name || ' split subpartition ' ||

a.subpartition_name || ' at ';

--分区前缀

v_p_prex := substr(a.subpartition_name,

1,

instr(a.subpartition_name, '_', 1, 2) );

--分区日期

v_P_date := substr(a.subpartition_name,

instr(a.subpartition_name, '_', 1, 2) + 1,

4);

--分区时间

v_p_date2 := to_date(v_p_date, 'yymm');

v_i := add_months(v_p_date2, 1) - v_p_date2;

v_p_date2 := add_months(v_p_date2, -1);

for b in 1 .. v_i-1 loop

--(TO_DATE('2009-01-05','YYYY-MM-DD') )

v_sql2 := 'to_date(''' ||

to_char(v_p_date2 + b, 'yyyymmdd') || ''',''yyyymmdd'')';

v_sql3 := 'subpartition ' ||v_p_prex || to_char(v_p_date2 + b, 'yymmdd');

v_sql4 := v_sql || ' ( '||v_sql2||' ) ' || ' into ('||v_sql3||',subpartition '||a.subpartition_name||' ) ';

--dbms_output.put_line(v_sql4);

begin

execute immediate v_sql4;

exception when others then

null;

end;

end loop;

-- dbms_output.put_line(v_sql);

-- dbms_output.put_line(v_sql2);

-- dbms_output.put_line(v_sql3);

v_sql2:='';

v_sql3:='';

v_sql:='';

end loop;

end;[@more@]