forall使用及异常处理

<pre name="code" class="sql">--forall使用及异常处理
declare
  type t_series       is table of aml_centric_task.series %type;
  type t_task_type    is table of aml_centric_task.task_type %type;
  type t_model_type   is table of aml_centric_task.model_type %type;
  type t_count        is table of number;
  v_count     number := 0;
  v_um        varchar2(10) := 'RISK3';
  
  s_series     t_series;
  s_task_type  t_task_type;
  s_model_type t_model_type;
  s_count      t_count;

  cursor cur_sle is
    select /*+index(a idx_updat_dat_aml_centric_task)*/a.series,
           a.task_type,
           case a.model_type when '03' then '03' else '01' end model_type,
           count(1) curr_comp_task
      from aml_centric_task a
     where a.task_status = '03'
       and trunc(a.updated_date) > trunc(sysdate)-30
       and a.ministrant = v_um
       and a.lapse_flag = '0'
     group by a.series, a.task_type, case a.model_type when '03' then '03' else '01' end;

  bulk_errors exception;
  pragma exception_init(bulk_errors, -24381);

begin

  select count(0)
    into v_count
    from aml_centric_task a,
         (select e.relevancy_id, e.task_type, e.model_type
            from aml_centric_task_sha e
          union all
          select f.relevancy_id, f.task_type, '01' model_type
            from aml_centric_task_sha_c f) r
   where r.relevancy_id = a.relevancy_id
     and r.task_type = a.task_type;

  if v_count != 0 then
  
    open cur_sle;
  
    loop
      fetch cur_sle bulk collect
        into s_series, s_task_type, s_model_type, s_count limit 5000;
      exit when s_series.count = 0;
      begin
      
        forall i in 1 .. s_series.count save exceptions
          update aml_task_refresh_single sl
             set curr_comp_task = 'XXX'--s_count(i)    --测试异常
           where sl.series = s_series(i)
             and sl.tasktype = s_task_type(i)
             and sl.model_type = s_model_type(i)
             and sl.um_task = v_um;
      
        commit;
      
      exception
        when bulk_errors then
        
          for i in 1 .. sql%bulk_exceptions.count loop
             dbms_output.put_line(sqlerrm (-1 * sql%bulk_exceptions(i).error_code));
          end loop;

      end;
    end loop;
  
    close cur_sle;
  
  end if;

end;