最近遇到一个这么一个业务:每晚根据另一个系统上报的数据(表 t_taemployee ,100W左右数据),更新正式系统里面对应名单(表tatable, 2000W左右数据)的状态等信息,基于商业原因,这里的表名及字段名都已做处理,分别采用别名代替.

实现方式:定义一个procedure,采用Job来定时调用procedure执行,其procedure代码(已处理过)如下

create or replace procedure upload_state as
  curyear  number;
  curmonth number;
  tatable varchar(100) := 't_test1_';
  fetable varchar(100) := 't_test2_';
  cout     number := 0;
  rowindex number := 0;
  exc_time exception;
  exc_ta exception;
  exc_fe exception;
  insert_err varchar(200) := 'insert into t_synerror2(message) values(:message)';
  type v_cursor is ref cursor;
  y_cursor  v_cursor;
  f_cursor v_cursor;
  query_y   varchar2(1000);
  query_f  varchar2(1000);
  type y_rec is record(
    state  number,
    sje   number,
    row_id varchar2(20));
  type f_rec is record(
    state  number,
    row_id varchar2(20));
  type y_array_type is table of y_rec;
  type f_array_type is table of f_rec;
  y_array  y_array_type;
  f_array f_array_type;
begin
  select count(1)
    into cout
    from user_tables
   where table_name = upper('t_synerror2');
  if cout = 0 then
    execute immediate 'create table t_synerror2(
                      notedate date default sysdate,
                      message varchar(100))';
  end if;

  select extract(year from(sysdate - 1)), extract(month from(sysdate - 1))
    into curyear, curmonth
    from dual;
  if curmonth = 2 or curmonth = 8 then
    raise exc_time;
  end if;

  tatable := tatable || curyear || curmonth;
  select count(1)
    into cout
    from user_tables
   where table_name = upper(tatable);
  if cout = 0 then
    raise exc_ta;
  end if;

  fetable := fetable || curyear || curmonth;
  select count(1)
    into cout
    from user_tables
   where table_name = upper(fetable);
  if cout = 0 then
    raise exc_fe;
  end if;

  --bluck collect
  --
  query_y := 'select ad.state, ad.sje, y.rowid row_id
      from st_goup sj, t_taemployee ad, ' ||
               tatable || ' y
     where sj.js_id = ad.sid
       and sj.state = 1
       and sj.q_id = y.sid';
  open y_cursor for query_y;
  fetch y_cursor bulk collect
    into y_array;
  close y_cursor;
  for r in y_array.first .. y_array.last loop
    execute immediate 'update ' || tatable ||
                      ' y set y.state = :state, y.sje  = :sje where rowid=:rowd'
      using y_array(r).state, y_array(r).sje, y_array(r).row_id;
    rowindex := rowindex + 1;
    if rowindex = 1000 then
      rowindex := 0;
      commit;
    end if;
  end loop;
  commit;
  /*
  execute immediate '
    update ' || tatable || ' y
       set y.state = (select ad.state
                        from st_goup sj, t_taemployee ad
                       where sj.js_id = ad.sid
                         and sj.state = 1
                         and q_id = y.sid),
           y.sje  = (select ad.sje
                        from st_goup sj, t_taemployee ad
                       where sj.js_id = ad.sid
                         and sj.state = 1
                         and q_id = y.sid)
     where exists (select sj.q_id
              from st_goup sj, t_taemployee ad
             where sj.js_id = ad.sid
               and sj.state = 1
               and q_id = y.sid)';
  commit;
  */
  --bluck collect
  --
  query_f := 'select fe.state, ff.rowid
      from st_goup    sj,
           t_femployee      fe,
           ' || fetable || ' ff
     where sj.js_id = fe.sid
       and sj.state = 1
       and q_id = ff.sid';
  open f_cursor for query_f;
  fetch f_cursor bulk collect
    into f_array;
  close f_cursor;
  rowindex := 0;
  for r in f_array.first .. f_array.last loop
    execute immediate 'update ' || fetable || ' f
       set f.state = :state where rowid = :row_id'
      using f_array(r).state, f_array(r).row_id;
    rowindex := rowindex + 1;
    if rowindex = 1000 then
      rowindex := 0;
      commit;
    end if;
  end loop;
  commit;
  /*
  execute immediate '
    update ' || fetable || ' f
       set f.state = (select fe.state
                        from st_goup sj, t_femployee fe
                       where sj.js_id = fe.sid
                         and sj.state = 1
                         and q_id = f.sid)
     where exists (select sj.q_id
              from st_goup sj, t_femployee fe
             where sj.js_id = fe.sid
               and sj.state = 1
               and q_id = f.sid)';
  commit;
  */
exception
  when exc_time then
    execute immediate insert_err
      using '当前系统月份为“' || curmonth || '”，...';
    commit;
  when exc_ta then
    execute immediate insert_err
      using '当前系统年月（' || curyear || '，' || curmonth || '）...“' || tatable || '”还未生成';
    commit;
  when exc_fe then
    execute immediate insert_err
      using '当前系统年月（' || curyear || '，' || curmonth || '）...“' || fetable || '”还未生成';
    commit;
end upload_state;
/

以上是使用bulk collect into优化后的效果,该脚本通过job调用,一分钟左右执行完成.

而优化前,没有用到bulk collect into ,而是直接update(注释掉的脚本),执行了近5小时,最后不得不手动停止Job执行.

这里仅仅提供给大家一个批量更新的参考,不做过多说明.