oracle 日期sp,oracle自动创建时间分区SP

该分区以时间分区

create or replace package PKG_AutoPartitions is

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

*/

create or replace package body PKG_AutoPartitions is

  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; --要合并的分区的个数

v_BeginPartitionDate timestamp; ----分区的开始时间、

v_EndPartitionDate timestamp; ----分区的结束时间

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

    END IF;

  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(天) 方便扩展

   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

     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;

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

end loop;

END IF;

END P_AutoPartition;

end PKG_AutoPartitions;

/