-- 按天创建分区表，并通过存储过程定期删除指定天数前的分区

/****/

/ 创建按天分区表 *//****/

-- 订单订单资产快照表

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_selfis '订单订单资产快照表';

-- Add comments to the columns

comment on column DTSDATA.tyebasset_beforecash_ds_self.id_yebassetselfis '记录编号(唯一)';comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_tradeaccois '交易账号';comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_fundcodeis '基金代码'; comment on column DTSDATA.tyebasset_beforecash_ds_self.d_carrydayis '待分配收益日期';comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday15endassetis 'cday截止15点资产'; comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday24endassetis 'cday截止24点资产'; comment on column DTSDATA.tyebasset_beforecash_ds_self.en_frozenbalais '冻结份额';comment on column DTSDATA.tyebasset_beforecash_ds_self.en_accumincomeis '累计收益'; comment on column DTSDATA.tyebasset_beforecash_ds_self.remarkis '备注'; comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_BYis '录入人员';comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_DATEis '创建日期';comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_BYis '更新人员';comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_DATEis '更新日期';

-- 创建主键

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 --错误消息) ispartitions_size int;delete_size int;v_partition_name user_tab_partitions.partition_name%TYPE;cursor c_partitions isselect partition_name from user_tab_partitionswhere table_name = 'TYEBASSET_BEFORECASH_DS_SELF'and partition_position > 1order by partition_position asc;

begin

-- 获取除默认分区外的分区数量select count(1) into partitions_sizefrom user_tab_partitionswhere 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 thenr_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