新增一维二维表分区

记得绿罗裙106

于 2022-01-13 16:26:48 发布

阅读量218

点赞数

分类专栏： oracel 文章标签： sql 数据库 dba

本文链接：https://blog.csdn.net/Xiaoshiniudao889/article/details/122476872

版权

oracel 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

create or replace procedure SP_IE_CRE_PARTITION(IV_CJRQ in varchar2,
                                                                              IV_BSJGH in varchar2,
                                                                              IV_TYPE in varchar2)
/******************************
@AUTHOR:
@CREATE-DATE:
@DESCRIPTION:创建表分区
@MODIFICATION HISTORY:
*******************************/                                                                             
is
V_CJRQ varchar2(8); --报送日期
V_BSJGH varchar2(30);--报送机构号
V_TYPE    varchar2(2);--1: 添加新的复合分区  2：添加子分区
V_PAT_SQL varchar2(4000);-- 添加复合分区的sql
V_SUB_PAT_SQL varchar2(4000);-- 添加子分区的sql
--定义游标
cursor  cur1 is select distinct a.i_full_table_name as sql_ from m_standard_table  a where a.category_id='94B261EE68D9C154E050007F010067B8' ;--and a.i_full_table_name like '%BJ%';
begin
  V_CJRQ := IV_CJRQ;
  V_BSJGH := IV_BSJGH;
  V_TYPE := IV_TYPE;
if V_TYPE = '1' then
for tmp1 in cur1 loop
            V_PAT_SQL :=  'alter table '||tmp1.sql_||'_ERR add partition P'||V_CJRQ||' values less than ('''||to_char(trunc(to_date(V_CJRQ,'yyyyMMdd')+1),'yyyyMMdd')||''')' ;
--dbms_output.put_line('V_PAT_SQL:'||V_PAT_SQL);
     execute immediate V_PAT_SQL;
      V_PAT_SQL :=  'alter table '||tmp1.sql_||'_RECORD add partition P'||V_CJRQ||' values less than ('''||to_char(trunc(to_date(V_CJRQ,'yyyyMMdd')+1),'yyyyMMdd')||''')' ;
--dbms_output.put_line('V_PAT_SQL:'||V_PAT_SQL);
     execute immediate V_PAT_SQL;
         V_PAT_SQL :=  'alter table '||tmp1.sql_||'_inc add partition P'||V_CJRQ||' values less than ('''||to_char(trunc(to_date(V_CJRQ,'yyyyMMdd')+1),'yyyyMMdd')||''')' ;
--dbms_output.put_line('V_PAT_SQL:'||V_PAT_SQL);
     execute immediate V_PAT_SQL;
              V_PAT_SQL :=  'alter table '||tmp1.sql_||' add partition P'||V_CJRQ||' values less than ('''||to_char(trunc(to_date(V_CJRQ,'yyyyMMdd')+1),'yyyyMMdd')||''')' ;
--dbms_output.put_line('V_PAT_SQL:'||V_PAT_SQL);
     execute immediate V_PAT_SQL;
    V_PAT_SQL :=  'alter table '||tmp1.sql_||'_SPLIT add partition P'||V_CJRQ||' values less than ('''||to_char(trunc(to_date(V_CJRQ,'yyyyMMdd')+1),'yyyyMMdd')||''')
 
(
       subpartition P'||V_CJRQ||'_JG'||V_BSJGH||' values('''||V_BSJGH||''') tablespace EAST4
)' ;
--dbms_output.put_line('V_PAT_SQL:'||V_PAT_SQL);
  execute immediate V_PAT_SQL;
     V_PAT_SQL :=  'alter table '||tmp1.sql_||'_SPT_ERR add partition P'||V_CJRQ||' values less than ('''||to_char(trunc(to_date(V_CJRQ,'yyyyMMdd')+1),'yyyyMMdd')||''')
 
(
     subpartition P'||V_CJRQ||'_JG'||V_BSJGH||' values('''||V_BSJGH||''') tablespace EAST4
)' ;
--dbms_output.put_line('V_PAT_SQL:'||V_PAT_SQL);
     execute immediate V_PAT_SQL;
  end loop;
 
elsif V_TYPE='2' then
for tmp2 in cur1 loop
     V_SUB_PAT_SQL :='alter table '||tmp2.sql_||'_SPLIT modify partition P'||V_CJRQ||' add SUBPARTITION P'||V_CJRQ||'_JG'||V_BSJGH||' values('''||V_BSJGH||''') tablespace EAST4';
   -- dbms_output.put_line('V_SUB_PAT_SQL:'||V_SUB_PAT_SQL);
      execute immediate V_SUB_PAT_SQL;
     V_SUB_PAT_SQL :='alter table '||tmp2.sql_||'_SPT_ERR modify partition P'||V_CJRQ||' add SUBPARTITION P'||V_CJRQ||'_JG'||V_BSJGH||' values('''||V_BSJGH||''') tablespace EAST4';
    --dbms_output.put_line('V_SUB_PAT_SQL:'||V_SUB_PAT_SQL);
      execute immediate V_SUB_PAT_SQL;
  end loop;
 
end if;
 
end SP_IE_CRE_PARTITION;
/