Shulou(Shulou.com)06/01 Report--

-- create partition tables on a daily basis, and periodically delete partitions before a specified number of days through stored procedures

/ * /

/ create a daily partition table * /

/ * /

-- order order asset snapshot table

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 (16Pol 2)

En_cday24endasset NUMBER (16Pol 2)

En_frozenbala NUMBER (16Pol 2)

En_accumincome NUMBER (16Pol 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)

(

Partition asset_self_p1 values less than (to_date ('2018-01-01-01-01))

);

-- Add comments to the table

Comment on table DTSDATA.tyebasset_beforecash_ds_self

Is' order Asset Snapshot Table'

-- Add comments to the columns

Comment on column DTSDATA.tyebasset_beforecash_ds_self.id_yebassetself

Is' record number (unique)'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_tradeacco

Is' trading account'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.vc_fundcode

Is' fund code'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.d_carryday

Is' date of income to be distributed'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday15endasset

Is' cday as of 15:00 Asset'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_cday24endasset

Is' cday as of 24:00 Asset'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_frozenbala

Is' Frozen shares'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.en_accumincome

Is' cumulative income'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.remark

Is' remarks'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_BY

Is' input personnel'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.CREATED_DATE

Is' creation date'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_BY

Is' update personnel'

Comment on column DTSDATA.tyebasset_beforecash_ds_self.UPDATED_DATE

Is' Update date'

-- create primary key

Alter table DTSDATA.tyebasset_beforecash_ds_self add constraint PK_tyebasset_ds_self primary key (id_yebassetself) using index initrans 16

-create an index

Create unique index DTSDATA.IDX_tyebasset_ds_self on DTSDATA.tyebasset_beforecash_ds_self (vc_tradeacco,vc_fundcode,d_carryday) local

-- create synonyms, authorization

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

/ * /

/ regularly delete partition table historical partition package /

/ * /

Create or replace package puf_dts_xxx is

Procedure del_self_partition (

Days in int-- number of days to retain

R_error_code out int,-- error code

R_error_message out varchar2-error messa

);

End puf_dts_xxx

/

/ * /

/ * regularly delete the partition table historical partition package body /

/ * /

Create or replace package body puf_dts_xxx is

Procedure del_self_partition (days in int,-- retention days

R_error_code out int,-- error code

R_error_message out varchar2-error messa

) 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

-- gets the number of partitions except the default partition

Select count (1) into partitions_size

From user_tab_partitions

Where table_name = 'TYEBASSET_BEFORECASH_DS_SELF'

Delete_size: = partitions_size-1-days;-the number of partitions to be deleted: open centering partitions, fetch c_partitions into loop partitions, EXIT WHEN delete_size

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.