4个分区思想 4

-- 历史表对应的存储过程处理

  create or replace procedure prc_m_trans_auth_detail_dyn

(

    iv_min    in    varchar2,

    oi_return  out   integer

)

is 

    vv_day_or_min varchar2(10);

begin

    execute immediate 'alter session enable parallel dml'; 

    vv_min := substr(iv_min,-2);  --iv_min:input paramter

    case vv_min

      when '15' then

        vv_id := 1;

      when '30' then

        vv_id := 2;

      when '45' then

        vv_id := 3;

      when '00' then

        vv_id := 4;

    end case; 

    execute immediate 'alter table lbi_dm.t_m_trans_auth_detail_dyn truncate partition part_' ||vv_id; 

      insert /*+ append */

        into lbi_dm.t_m_trans_auth_detail_dyn

             (

             part_id            ,

             part_min           , 

             data_date          ,      

             trans_amount                )

     select /*+ parallel(trans,4) parallel(oper1,4) parallel(oper2,4)*/

             vv_id,

             case when vv_min-1 = -1 then 59 else vv_min-1 end,

             mid.data_date,           

             mid.trans_amount

        from lbi_dm.t_m_trans_auth_detail_mid mid,          

             lbi_edm.t_e_operator_dyn oper

       where mid.checker_id = oper.operator_id(+);        

    vi_result := vi_result + sql%rowcount;

    commit;

exception

end;