oracle 通过拆解创建分区

create or replace procedure sp_create_partition(p_i_table_name in varchar2,p_i_date int varchar2) is

v_par_name varchar2(50);
v_par_name_max varchar2(50);
v_par_exists integer;

v_etl_dt date;
v_etl_dt_1 varchar(10);

begin
v_par_name :=‘p_’||p_i_date;
v_par_name_max:=‘p_99991230’;
v_par_exists:=0;

v_etl_dt_1:=substr(p_i_date,1,4)||’-’||substr(p_i_date,5,2)||’-’||substr(p_i_date,7,2);
v_etl_dt:=to_date(v_etl_dt,‘YYYY-MM-DD’);

select ‘p_’||substr(m.partition_name,3) into v_par_name_max from user_tab_partitions m where substr(m.partition_name,3)>p_i_date and m.table_name=p_i_table_name and rownum=1
order by substr(m.partition_name,3) asc;

select count(1) into v_par_exists from user_tab_partitions where table_name=p_i_table_name and partition_name=v_par_name;

if v_par_exists=0 then
execute immediate ’ alter table ’ || par_i_table_name || split partition ’ || v_par_name_max || ‘at (’’’||to_char(v_etl_dt + 1,‘YYYY-MM-DD’)||’’’))’||
’ into (partition’||v_par_name||’,partition’||v_par_name_max||’) update indexs’;
end sp_create_partition;