oracle自动创建时间分区SP

该分区以时间分区

<p>create or replace package PKG_AutoPartitions is</p><p> v_TableSpace VARCHAR2(30):= 'XXXUSER'; --命名空间 上线要修改成XXXUSER  procedure P_RepairAutoPartition(p_EndPartitionDate in varchar2) ;procedure P_RepairAutoMergePartition(p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2) ;procedure P_AutoMergePartition(p_TableName in varchar2,p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2);                       procedure P_AutoPartition(p_TableName in varchar2,p_TableSpace in varchar2,p_PrePartitionName in varchar2, p_EndPartitionDate in varchar2, p_InterverType in varchar2, p_Interver in number); end PKG_AutoPartitions;/  /*      名称：PKG_AutoPartitions      描述：XXX 系统 oracle添加分区表分区的存储过程     P_AutoPartition 是最根本的存储过程      本存储过程支持按照年、天、月进行分区，      使用方法：     维修 P_AutoPartition('T_PHONEDEAL','XXXUSER','T_PHONEDEAL',p_EndPartitionDate,'M',1);       */</p><p>create or replace package body PKG_AutoPartitions is</p><p>  procedure P_RepairAutoPartition(p_EndPartitionDate in varchar2) AS   BEGIN   P_AutoPartition('T_PHONEDEAL',PKG_AutoPartitions.v_TableSpace,'T_PHONEDEAL',p_EndPartitionDate,'M',1);  END P_RepairAutoPartition;    procedure P_RepairAutoMergePartition(p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2) AS   BEGIN   P_AutoMergePartition('T_PHONEDEAL',p_BeginPartitionDate,p_EndPartitionDate,p_NewPartitionName);  END P_RepairAutoMergePartition;    procedure P_AutoMergePartition(p_TableName in varchar2,p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2) AS  v_SqlExec            VARCHAR2(2000); --DDL语句变量  v_HighValue         varchar2(255); --less than value信息   v_HighValuePartitionDate timestamp; ----less than value信息的 timestamp表示形式   v_PartitionNames         varchar2(1500); --要合并的分区名字，逗号分隔   v_PartitionCount        number := 0; --要合并的分区的个数</p><p>         v_BeginPartitionDate timestamp; ----分区的开始时间、      v_EndPartitionDate timestamp; ----分区的结束时间      </p><p>            v_CurrenntPartitionNameBox VARCHAR2(30);  --用于存放当前分区            v_PartitionNameTemp01 VARCHAR2(30):= 'PartitionNameTemp_01';            v_PartitionNameTemp02 VARCHAR2(30):= 'PartitionNameTemp_02';               ---分区表信息  cursor cur_utp(v_TableName in user_tab_partitions.table_name%TYPE) is    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(p_TableName)             order by utp.partition_position asc;     BEGIN      v_BeginPartitionDate :=to_timestamp(p_BeginPartitionDate,  'syyyy-mm-dd hh24:mi:ss.ff');     v_EndPartitionDate :=to_timestamp(p_EndPartitionDate,  'syyyy-mm-dd hh24:mi:ss.ff');      --读取满足要求的分区   for utp in cur_utp(p_TableName) loop    v_HighValue         := substr(utp.high_value,                                   11,                                   10);                             v_HighValuePartitionDate := to_timestamp(v_HighValue,                                         'syyyy-mm-dd hh24:mi:ss.ff');                                                                           IF ( v_HighValuePartitionDate>= v_BeginPartitionDate and v_HighValuePartitionDate<= v_EndPartitionDate) then      if(v_PartitionCount=1) then           v_CurrenntPartitionNameBox :=utp.partition_name;      end if;            if(v_PartitionCount>=2) then          if(mod(to_number(v_PartitionCount),2)=1) then              v_PartitionNames :=v_CurrenntPartitionNameBox||','||utp.partition_name;             v_CurrenntPartitionNameBox :=v_PartitionNameTemp01;                          v_SqlExec := 'ALTER TABLE ' || p_TableName || ' MERGE PARTITIONS ' ||                         v_PartitionNames ||                         ' INTO PARTITION ' ||v_CurrenntPartitionNameBox;                                                  dbms_output.put_line('合并 表分区' || v_PartitionNames ||'到 ' || v_CurrenntPartitionNameBox||'='||v_SqlExec);                                     DBMS_Utility.Exec_DDL_Statement(v_SqlExec);                      else             v_PartitionNames :=v_CurrenntPartitionNameBox||','||utp.partition_name;             v_CurrenntPartitionNameBox :=v_PartitionNameTemp02;                          v_SqlExec := 'ALTER TABLE ' || p_TableName || ' MERGE PARTITIONS ' ||                         v_PartitionNames ||                         ' INTO PARTITION ' ||v_CurrenntPartitionNameBox;                                                  dbms_output.put_line('合并 表分区' || v_PartitionNames ||'到 ' || v_CurrenntPartitionNameBox||'='||v_SqlExec);                                     DBMS_Utility.Exec_DDL_Statement(v_SqlExec);                  end if;            end if;      v_PartitionCount:=v_PartitionCount+1;      END IF;              end loop;      IF (v_PartitionCount=0) THEN        dbms_output.put_line('没有找到要合并的分区');    ELSE     v_SqlExec := 'ALTER TABLE ' || p_TableName || ' RENAME PARTITION ' ||                     v_CurrenntPartitionNameBox ||                     ' TO  ' ||p_NewPartitionName;                            dbms_output.put_line('修改 表分区' || v_CurrenntPartitionNameBox ||'到 ' || p_NewPartitionName||'='||v_SqlExec);                DBMS_Utility.Exec_DDL_Statement(v_SqlExec);</p><p>    END IF; </p><p>  END P_AutoMergePartition;       procedure P_AutoPartition(p_TableName in varchar2,p_TableSpace in varchar2,p_PrePartitionName in varchar2, p_EndPartitionDate in varchar2, p_InterverType in varchar2, p_Interver in number) AS      v_Interver           number := 1; --步长间隔 单位(月)   v_InterverType       VARCHAR2(1) := 'M'; --Y(年)/D(天)M(月)/D(天) 方便扩展</p><p>   v_SqlExec            VARCHAR2(2000); --DDL语句变量      v_TableSpace VARCHAR2(30):= PKG_AutoPartitions.v_TableSpace; --命名空间 上线要修改成XXXUSER   v_PrePartitionName           VARCHAR2(30); --DDL语句变量   v_EndPartitionDate     timestamp;   v_PartitionName           VARCHAR2(50); --DDL语句变量      v_HighValue         varchar2(255); --less than value信息   v_HighValuePartitionMaxDate timestamp; ----less than value信息的 timestamp表示形式     ---分区表信息  cursor cur_utp(v_TableName 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(p_TableName)             order by utp.partition_position desc) utp     where rownum = 1;   BEGIN</p><p>     IF (UPPER(p_InterverType)='Y' or UPPER(p_InterverType)='M' or UPPER(p_InterverType)='D') THEN          v_InterverType :=p_InterverType;      END IF;         IF (p_Interver IS NULL) THEN          v_Interver :=p_Interver;      END IF;        v_EndPartitionDate := to_timestamp(p_EndPartitionDate, 'syyyy-mm-dd hh24:mi:ss.ff');        --获取最大分区时间    for utp in cur_utp(p_TableName) loop    v_HighValue         := substr(utp.high_value,                                   11,                                   10);                             v_HighValuePartitionMaxDate := to_timestamp(v_HighValue,                                         'syyyy-mm-dd hh24:mi:ss.ff');                                                                                    --取前缀    if (p_PrePartitionName is null) then      v_PrePartitionName := SUBSTRB(utp.partition_name,1,INSTR(utp.partition_name,'_', 1,1)-1) ;    else      v_PrePartitionName := p_PrePartitionName;    end if;    v_PrePartitionName := UPPER(v_PrePartitionName);      --取表空间    if (p_TableSpace is null) then      v_TableSpace :=utp.tablespace_name;    else      v_TableSpace := p_TableSpace;    end if;    v_TableSpace := UPPER(v_TableSpace);                           end loop;      </p><p>    IF (v_InterverType='Y') THEN            v_EndPartitionDate:=  To_Date(to_char(v_EndPartitionDate, 'yyyy'), 'yyyy');--获取本年                 ELSIF (v_InterverType='M') THEN          v_EndPartitionDate:=trunc(add_months(last_day(v_EndPartitionDate), -1) + 1);--获取本月第一天       ELSIF (v_InterverType='D') THEN           v_EndPartitionDate :=  to_timestamp(p_EndPartitionDate, 'syyyy-mm-dd hh24:mi:ss.ff')  ;    END IF;                              IF ( v_HighValuePartitionMaxDate>= v_EndPartitionDate) then       dbms_output.put_line('没有分区可以添加');    ELSE        while v_HighValuePartitionMaxDate < v_EndPartitionDate loop         IF (v_InterverType='Y') THEN                v_HighValuePartitionMaxDate := add_months(v_HighValuePartitionMaxDate, 12*v_Interver);                 v_HighValuePartitionMaxDate :=To_Date(to_char(v_HighValuePartitionMaxDate, 'yyyy')||'0101', 'yyyymmdd');               v_PartitionName :=v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyy');        ELSIF (v_InterverType='M') THEN              v_HighValuePartitionMaxDate := add_months(v_HighValuePartitionMaxDate, v_Interver);                v_PartitionName :=v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyymm');        ELSIF (v_InterverType='D') THEN                v_HighValuePartitionMaxDate := v_HighValuePartitionMaxDate+ v_Interver;               v_PartitionName :=v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyymmdd');        END IF;                                v_SqlExec := 'ALTER TABLE ' || p_TableName || ' ADD PARTITION ' ||                     v_PartitionName ||                     ' values less than(TIMESTAMP''' ||                     to_char(v_HighValuePartitionMaxDate, 'syyyy-mm-dd hh24:mi:ss.ff') ||                     ''') TABLESPACE ' || v_TableSpace;        dbms_output.put_line('创建 表分区' || v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyymmdd') || '=' || v_SqlExec);                DBMS_Utility.Exec_DDL_Statement(v_SqlExec);</p><p>          end loop;        END IF;    END P_AutoPartition;</p><p>  end PKG_AutoPartitions;/</p>