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

最新推荐文章于 2024-06-12 11:41:02 发布

bensantan

最新推荐文章于 2024-06-12 11:41:02 发布

阅读量577

点赞数

分类专栏： J2EE--数据存储

本文链接：https://blog.csdn.net/bensantan/article/details/40355791

版权

J2EE--数据存储专栏收录该内容

99 篇文章 0 订阅

订阅专栏

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);