vv_etl_a varchar2(250) ; --add partition date/week/month
vv_etl_d varchar2(250) ; --drop partition date/week/month
vv_etl_c varchar2(250) ; --add partition date/week/month + 1day
vv_partition_name_a varchar2(250) ; --add partition name
vv_partition_name_d varchar2(250) ; --drop partition name
vv_add_p_sql varchar2(1000); --automatic SQL variable
vv_last_day varchar2(250) ; --last day
exc_partition_type exception ; --partition_type error
begin

open c_table_list ;
loop
<>
fetch c_table_list into l_table_list;
exit when c_table_list%notfound;
/** @description initialize variable according to deal_circle*/
if upper(l_table_list.deal_cycle) = 'D' then
vv_etl_a := to_char(vd_deal_date,'yyyymmdd');
vv_etl_d := to_char(vd_deal_date - l_table_list.partition_count,'yyyymmdd');
vv_etl_c := to_char(vd_deal_date + 1,'yyyymmdd');
vv_last_day := to_char(last_day(vd_deal_date - l_table_list.partition_count),'yyyymmdd');
vv_partition_name_a := upper(l_table_list.partition_name||vv_etl_a);
vv_partition_name_d := upper(l_table_list.partition_name||vv_etl_d);
/** The last day of the week is configurable according to local conventions and system language*/
elsif upper(l_table_list.deal_cycle) = 'W' and to_char(vd_deal_date,'D') = '7' then -- '7'
vv_etl_a := to_char(vd_deal_date,'yyyyiw');
vv_etl_d := to_char(vd_deal_date - l_table_list.partition_count*7,'yyyyiw');
vv_etl_c := to_char(vd_deal_date + 1,'yyyymmdd');
vv_last_day := null;
vv_partition_name_a := upper(l_table_list.partition_name||vv_etl_a);
vv_partition_name_d := upper(l_table_list.partition_name||vv_etl_d);
elsif upper(l_table_list.deal_cycle) = 'M' and vd_deal_date = last_day(vd_deal_date) then
vv_etl_a := to_char(vd_deal_date,'yyyymm');

ITPub博客

分区管理5

最新文章