分区语句整理 7

/**

     * @description call add partition procedure to add the latest partotion

     * @description partition day is to_char(sysdate,'yyyymmdd')

     */

 

     prc_s_maintain_partition(iv_char_date => to_char((trunc(sysdate,'DD')-to_char(sysdate,'D')+1),'yyyymmdd'),

                          on_statuscode => vv_par_flag);

 

    /**

     * @description exc_error2 will be raised if the return of  sp_dw_tab_add_partition procedure is not 0

     * @description get the vv_par_flag

     */

 

     if vv_par_flag <>0 then

        raise exc_error2;

     end if;

 

    /**

     * @description clean all expired partitions, only the latest partition is keeping

     * @description the latest partition's date is to_char(sysdate,'yyyymmdd')

     */

 

    open vc_table_info;

    loop

        fetch vc_table_info

            into l_table_info;

        exit when vc_table_info%notfound;

        if l_table_info.table_owner = vv_user1 or l_table_info.table_owner = vv_user2 or l_table_info.table_owner = vv_user3

        or l_table_info.table_owner = vv_user4 then

            if l_table_info.partition_name = l_table_info.partition_name2 then

            vv_sql := '';

            else

                vv_sql := 'alter table ' ||l_table_info.table_owner||'.'|| l_table_info.table_name ||

                          ' drop partition ' || l_table_info.partition_name;

                execute immediate vv_sql;

            end if;

        end if;