按天分區并通過存儲過程刪除歷史分區

-- 按天創建分區表，并通過存儲過程定期刪除指定天數前的分區

/****/
/ 創建按天分區表 */
/****/

-- 訂單訂單資產快照表
drop table DTSDATA.tyebasset_beforecash_ds_self;
create table DTSDATA.tyebasset_beforecash_ds_self
(
id_yebassetself VARCHAR2(32) default sys_guid() not null,
vc_tradeacco VARCHAR2(17) not null,
vc_fundcode VARCHAR2(6) not null,
d_carryday DATE not null,
en_cday15endasset NUMBER(16,2),
en_cday24endasset NUMBER(16,2),
en_frozenbala NUMBER(16,2),
en_accumincome NUMBER(16,2),
remark VARCHAR2(200) ,
created_by VARCHAR2(100) not null,
created_date DATE not null,
updated_by VARCHAR2(100) not null,
updated_date DATE not null
)
partition by range (d_carryday)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(
partition asset_self_p1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
);

-- Add comments to the table
comment on table DTSDATA.tyebasset_beforecash_ds_self
is '訂單訂單資產快照表';

-- Add comments to the columns
comment on column DTSDATA.tyebasset_beforecash_ds_self.id_yebassetself
is '記錄編號(唯一)';
comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_tradeacco
is '交易賬號';
comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_fundcode
is '基金代碼';
comment on column DTSDATA.tyebasset_beforecash_ds_self.d_carryday
is '待分配收益日期';
comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday15endasset
is 'cday截止15點資產';
comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday24endasset
is 'cday截止24點資產';
comment on column DTSDATA.tyebasset_beforecash_ds_self.en_frozenbala
is '凍結份額';
comment on column DTSDATA.tyebasset_beforecash_ds_self.en_accumincome
is '累計收益';
comment on column DTSDATA.tyebasset_beforecash_ds_self.remark
is '備注';
comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_BY
is '錄入人員';
comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_DATE
is '創建日期';
comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_BY
is '更新人員';
comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_DATE
is '更新日期';

-- 創建主鍵
alter table DTSDATA.tyebasset_beforecash_ds_self add constraint PK_tyebasset_ds_self primary key (id_yebassetself) using index initrans 16 ;

-- 創建索引
create unique index DTSDATA.IDX_tyebasset_ds_self on DTSDATA.tyebasset_beforecash_ds_self (vc_tradeacco,vc_fundcode,d_carryday) local ;

-- 創建同義詞、授權
create or replace public synonym tyebasset_beforecash_ds_self for dtsdata.tyebasset_beforecash_ds_self ;
grant select, insert, update, delete on DTSDATA.tyebasset_beforecash_ds_self to FDWKTL, DTSOPR, R_DTSDATA_DML;
grant select on DTSDATA.tyebasset_beforecash_ds_self to R_DTSDATA_QRY,R_DTSDATA_DEV_QRY;

/****/
/ 定時刪除分區表歷史分區 package /
/****/

create or replace package puf_dts_xxx is
procedure del_self_partition(
days in int, --保留天數
r_error_code out int, --錯誤代碼
r_error_message out varchar2 --錯誤消息
);
end puf_dts_xxx;
/

/****/
/* 定時刪除分區表歷史分區 package body /
/****/

create or replace package body puf_dts_xxx is
procedure del_self_partition(days in int, --保留天數
r_error_code out int, --錯誤代碼
r_error_message out varchar2 --錯誤消息
) is
partitions_size int;
delete_size int;
v_partition_name user_tab_partitions.partition_name%TYPE;
cursor c_partitions is
select partition_name from user_tab_partitions
where table_name = 'TYEBASSET_BEFORECASH_DS_SELF'
and partition_position > 1
order by partition_position asc;

begin
-- 獲取除默認分區外的分區數量
select count(1) into partitions_size
from user_tab_partitions
where table_name = 'TYEBASSET_BEFORECASH_DS_SELF';

delete_size := partitions_size - 1 - days;     -- 待刪除分區數量

open c_partitions;
loop
  fetch c_partitions
    into v_partition_name;
  EXIT WHEN delete_size <= 0;
  begin
    --刪除分區后需要更新全局索引，否則后續插入數據會報ORA-01502異常
    execute immediate 'alter table  dtsdata.tyebasset_beforecash_ds_self drop partition ' || v_partition_name || ' update global indexes ';
    delete_size := delete_size - 1;
  end;
end loop;
close c_partitions;

Exception
When others then
r_error_code := SQLCODE;
r_error_message := SQLERRM;
puf_dts_error_log.log_error(r_error_code,
r_error_message,
'puf_dts_xxx.del_self_partition',
'刪除' || days || '天前分區失敗',
user);

end del_self_partition;

end puf_dts_xxx;
/

-- 指定分區查詢
-- select * from TYEBASSET_BEFORECASH_DS_SELF partition(SYS_P131) ;

-- java代碼 通過preparestatement調用
-- call puf_dts_clear_expproc.exportpredeal_proc(?,?,?,?)

--kettle定時任務 TyebassetBeforecashDsSelfMain