一个动态游标的完整示例

--*********************************************************************************
--功能说明：
--*********************************************************************************
procedure proc_cx_fee_budget(errbuf           out  varchar2        ,
                                                         retcode               out  varchar2        ,
                                                         p_year                              varchar2        ,
                                                         p_budget_type            varchar2        
                                )
is
  type t_set_of_books_id  is table of    cit.cit_plangroup_cx_expense.set_of_books_id        %type;
    type t_period_name      is table of    cit.cit_plangroup_cx_expense.period_name            %type;
    type t_currency_code    is table of    cit.cit_plangroup_cx_expense.currency_code          %type;
    type t_company_code     is table of    cit.cit_plangroup_cx_expense.company_code           %type;
    type t_business_code    is table of    cit.cit_plangroup_cx_expense.business_code          %type;
    type t_cost_center      is table of    cit.cit_plangroup_cx_expense.cost_center            %type;
    type t_production_code  is table of    cit.cit_plangroup_cx_expense.production_code        %type;
    type t_account_code     is table of    cit.cit_plangroup_cx_expense.account_code           %type;
    type t_period_amount    is table of    cit.cit_plangroup_cx_expense.period_amount          %type;
    type t_actual_flag      is table of    cit.cit_plangroup_cx_expense.actual_flag            %type;
    type t_budget_type      is table of    cit.cit_plangroup_cx_expense.budget_type            %type;
    type t_deal_flag        is table of cit.cit_plangroup_cx_expense.deal_flag              %type;

    type t_cust_type_code        is table of cit.cit_plangroup_cx_expense.cust_type_code         %type;

 

  s_set_of_books_id            t_set_of_books_id ;
    s_period_name           t_period_name     ;
    s_currency_code         t_currency_code   ;
    s_company_code          t_company_code    ;
    s_business_code         t_business_code   ;
    s_cost_center           t_cost_center     ;
    s_production_code       t_production_code ;
    s_account_code          t_account_code    ;
    s_period_amount         t_period_amount   ;
    s_actual_flag           t_actual_flag     ;
    s_budget_type           t_budget_type     ;
    s_deal_flag              t_deal_flag       ;
    s_cust_type_code            t_cust_type_code    ;
    
    v_created_by          number := nvl(fnd_profile.value('USER_ID'), -1);                                                                                                        
    v_last_updated_by       number := nvl(fnd_profile.value('USER_ID'), -1);                                                                                                 
                                                                                                                                                                                
  --动态cursor                                                                                                                                                                  
  type cursors is ref cursor;                                                                                                                                                   
  cur_cx_fee_budget cursors;                                                                                                                                                           
                                                                                                                                                                                
begin                                                                                                                                                                           
  begin                                                                                                                                                                         

      --删除之前数据
    delete cit.cit_plangroup_cx_expense
     where substr(period_name, 1, 4) = p_year
       and budget_type = p_budget_type;

    if p_budget_type = 'BEGIN_BUDGET' then 
    
      open cur_cx_fee_budget for
              select h.set_of_books_id, to_char(to_date(h.period_name,'mon-rr','nls_date_language=american'),'yyyymm') period_name,
                           h.currency_code, gcc.segment1 company_code, gcc.segment2 business_code,
                           gcc.segment3 cost_center, gcc.segment4 production_code, gcc.segment5 account_code,
                           sum(nvl(l.entered_dr,0)-nvl(l.entered_cr,0)) period_amount, h.actual_flag, 'BEGIN_BUDGET' BUDGET_TYPE,
                             'N' deal_flag, l.attribute11 cust_type_code
                  from gl_je_headers h, gl_je_lines l, gl_code_combinations gcc, gl_period_statuses s
                 where h.je_header_id = l.je_header_id
                   and l.code_combination_id = gcc.code_combination_id
                 and gcc.segment1 = gcc.segment1
                     and (gcc.segment5 like '2152%' or gcc.segment5 like '4401%' or gcc.segment5 like '4451%')
                   and h.je_category = '25'         
                   and h.set_of_books_id = 22       
                   and h.je_source = '303'          
                   and h.currency_code = 'CNY'
                   and h.actual_flag = 'B'          
                   and h.status = 'P'               
           and h.period_name = s.period_name
           and s.application_id = 101       
                   and s.set_of_books_id = h.set_of_books_id
                   and s.period_year = p_year
                     and nvl(l.entered_dr,0)-nvl(l.entered_cr,0) <> 0
                 group by h.set_of_books_id, h.period_name, h.currency_code, gcc.segment1, gcc.segment2,
                           gcc.segment3, gcc.segment4, gcc.segment5, h.actual_flag, l.attribute11;
    
    elsif p_budget_type = 'BUDGET_ADJUST' then 
    
      open cur_cx_fee_budget for        
              select h.set_of_books_id, to_char(to_date(h.period_name,'mon-rr','nls_date_language=american'),'yyyymm') period_name,
                           h.currency_code, gcc.segment1 company_code,
                           gcc.segment2 business_code, gcc.segment3 cost_center, gcc.segment4 production_code, gcc.segment5 account_code,
                           sum(nvl(l.entered_dr,0)-nvl(l.entered_cr,0)) period_amount, h.actual_flag, 'BUDGET_ADJUST' BUDGET_TYPE,
                             'N' deal_flag, '' cust_type_code
                  from gl_je_headers h, gl_je_lines l, gl_code_combinations gcc, gl_period_statuses s
                 where h.je_header_id = l.je_header_id
                   and l.code_combination_id = gcc.code_combination_id
                 and gcc.segment1 = gcc.segment1
                     and (gcc.segment5 like '2152%' or gcc.segment5 like '4401%' or gcc.segment5 like '4451%')
                   and h.je_category in('26','27')  
                   and h.set_of_books_id = 22       
                   and h.je_source = '303'          
                   and h.currency_code = 'CNY'
                   and h.actual_flag = 'B'          
                   and h.status = 'P'               
           and h.period_name = s.period_name
           and s.application_id = 101       
                   and s.set_of_books_id = h.set_of_books_id
                   and s.period_year = p_year
                     and nvl(l.entered_dr,0)-nvl(l.entered_cr,0) <> 0
                 group by h.set_of_books_id, h.period_name, h.currency_code, gcc.segment1,
                           gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, h.actual_flag;
    
    end if;
    
    loop fetch cur_cx_fee_budget bulk collect
          into s_set_of_books_id, s_period_name, s_currency_code, s_company_code, s_business_code,
                   s_cost_center, s_production_code, s_account_code, s_period_amount, s_actual_flag,
                   s_budget_type, s_deal_flag, s_cust_type_code
         limit 10000;
         
        forall i in 1..s_set_of_books_id.count
      
              insert into cit.cit_plangroup_cx_expense(
                                                                                                  set_of_books_id        ,
                                                                                                    period_name       ,
                                                                                                    currency_code     ,
                                                                                                    company_code      ,
                                                                                                    business_code     ,
                                                                                                    cost_center       ,
                                                                                                    production_code   ,
                                                                                                    account_code      ,
                                                                                                    period_amount     ,
                                                                                                    actual_flag       ,
                                                                                                    budget_type       ,
                                                                                                    deal_flag         ,
                                                                                                    created_by        ,
                                                                                                    creation_date     ,
                                                                                                    last_updated_by   ,
                                                                                                    last_update_date    ,
                                                                                                    cust_type_code
                                                                               )values(
                                                                                                  s_set_of_books_id     (i)        ,
                                                                                                    s_period_name      (i)    ,
                                                                                                    s_currency_code    (i)    ,
                                                                                                    s_company_code     (i)    ,
                                                                                                    s_business_code    (i)    ,
                                                                                                    s_cost_center      (i)    ,
                                                                                                    s_production_code  (i)    ,
                                                                                                    s_account_code     (i)    ,
                                                                                                    s_period_amount    (i)    ,
                                                                                                    s_actual_flag      (i)    ,
                                                                                                    s_budget_type      (i)    ,
                                                                                                    s_deal_flag        (i)    ,
                                                                                                   v_created_by                      ,
                                                                                                 sysdate                               ,
                                                                                                   v_last_updated_by              ,
                                                                                                 sysdate                                    ,
                                                                                                 s_cust_type_code    (i)
                                                                               );
          commit;           
        exit when cur_cx_fee_budget%notfound;
    end loop;
    
      close cur_cx_fee_budget;

  exception when others then

    fnd_file.put_line(fnd_file.log, sqlerrm);
    rollback;
    raise;
    
  end;
 
  commit;
end proc_cx_fee_budget;