欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle使用游标进行分批次更新数据的6种方式及速度比对

程序员文章站 2022-03-08 10:05:38
1.情景展示  一共有22w条数据,需要将a表的主键更新至b表的指定字段,如何快速完成更新?2.解决方案  声明:  解决方案不只一种,该文章只介绍快速游标法及代码实现;  两张表的id和id_car...

1.情景展示

  一共有22w条数据, 需要将a表的主键更新至b表的指定字段,如何快速完成更新?

2.解决方案

  声明:

  解决方案不只一种,该文章只介绍快速游标法及代码实现;

  两张表的id和id_card字段都建立了索引。 

  方式一:使用隐式游标(更新一次提交1次)

--快速游标法
begin
  for temp_cursor in (select t2.id, t2.id_card
                        from virtual_card10 t1, primary_index10 t2
                       where t1.id_card = t2.id_card
                         and t1.remark = '**市****区数据'
                         and t2.remark = '**市****区数据') loop
    /* loop循环的是temp_cursor(逐条读取temp_cursor) */
    update virtual_card10
       set index_id = temp_cursor.id
     where id_card = temp_cursor.id_card;
    commit; --提交
  end loop;
end;

  执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  方式二:使用隐式游标(更新1000次提交1次)(推荐使用)

/* 使用隐式游标进行分批次更新 */
declare
 v_count number(10);
begin
 /* 隐式游标 */
 for temp_cursor in (select t2.id, t2.id_card
            from virtual_card10 t1, primary_index10 t2
            where t1.id_card = t2.id_card
             and t1.remark = '**市****区数据'
             and t2.remark = '**市****区数据') loop
  /* 业务逻辑 */
  update virtual_card10
    set index_id = temp_cursor.id
   where id_card = temp_cursor.id_card;
  /* 更新一次,+1 */
  v_count := v_count + 1;
  /* 1000条提交1次 */
  if v_count >= 1000 then
   commit; --提交
   v_count := 0; --重置
  end if;
 end loop;
 commit; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交
end;

  执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  方式三:显式游标+分批次更新(1000条1提交)

/* 使用游标进行分批次更新 */
declare
  v_count    number(10);
  v_index_id primary_index10.id%type;
  v_id_card  primary_index10.id_card%type;
  cursor temp_cursor is
    select t2.id, t2.id_card
      from virtual_card10 t1, primary_index10 t2
     where t1.id_card = t2.id_card
       and t1.remark = '**市****区数据'
       and t2.remark = '**市****区数据';
begin
  open temp_cursor;
  loop
    /* 取得一行游标数据并放到对应变量中 */
    fetch temp_cursor
      into v_index_id, v_id_card;
    /* 如果没有数据则退出 */
    exit when temp_cursor%notfound;
    /* 业务逻辑 */
    update virtual_card10
       set index_id = v_index_id
     where id_card = v_id_card;
    /* 更新一次,+1 */
    v_count := v_count + 1;
    /* 1000条提交1次 */
    if v_count >= 1000 then
      commit; --提交
      v_count := 0; --重置
    end if;
  end loop;
  commit; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交
  close temp_cursor;
end;

  执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  10000条1提交,执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  方式四:显式游标+数组(更新一次提交一次)(使用bulk collect)

/* 使用游标+数组进行更新(更新一次提交一次) */
declare
  /* 创建数组:一列多行 */
  type type_index_id is table of primary_index10.id%type;
  type type_id_card is table of primary_index10.id_card%type;
  /* 起别名 */
  v_index_id type_index_id;
  v_id_card  type_id_card;
  /* 将查询出来的数据放到游标里 */
  cursor temp_cursor is
    select t2.id, t2.id_card
      from virtual_card10 t1, primary_index10 t2
     where t1.id_card = t2.id_card
       and t1.remark = '**市****区数据'
       and t2.remark = '**市****区数据';
begin
  open temp_cursor;
  loop
    /* 取得1000行游标数据并放到对应数组中,每次读取1000条数据 */
    fetch temp_cursor bulk collect
      into v_index_id, v_id_card limit 1000;
    /* 如果没有数据则退出 */
    exit when temp_cursor%notfound;
    /* 遍历数据 */
    for i in v_index_id.first .. v_index_id.last loop
      /* 业务逻辑 */
      update virtual_card10
         set index_id = v_index_id(i)
       where id_card = v_id_card(i);
      commit;
    end loop;
  end loop;
  close temp_cursor;
end;

  执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  方式五: 显式游标+数组(1000条提交一次)(使用bulk collect)

/* 使用游标+数组进行更新(1000条提交一次) */
declare
  /* 创建数组:一列多行 */
  type type_index_id is table of primary_index10.id%type;
  type type_id_card is table of primary_index10.id_card%type;
  /* 起别名 */
  v_index_id type_index_id;
  v_id_card  type_id_card;
  /* 将查询出来的数据放到游标里 */
  cursor temp_cursor is
    select t2.id, t2.id_card
      from virtual_card10 t1, primary_index10 t2
     where t1.id_card = t2.id_card
       and t1.remark = '**市****区数据'
       and t2.remark = '**市****区数据';
begin
  open temp_cursor;
  loop
    /* 取得1000行游标数据并放到对应数组中 */
    fetch temp_cursor bulk collect
      into v_index_id, v_id_card limit 1000;
    /* 如果没有数据则退出 */
    exit when temp_cursor%notfound;
    /* 遍历数据 */
    for i in v_index_id.first .. v_index_id.last loop --或者:for i in 1 .. v_index_id.count loop
      /* 业务逻辑 */
      update virtual_card10
         set index_id = v_index_id(i)
       where id_card = v_id_card(i);
      if i >= v_index_id.last then
        commit; --提交
      end if;
    end loop;
  end loop;
  close temp_cursor;
end;

  执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  方式六:推荐使用(使用bulk collect和forall)

/* 使用游标+数组进行更新(bulk collect和forall) */
declare
  /* 创建数组:一列多行 */
  type type_index_id is table of primary_index10.id%type;
  type type_id_card is table of primary_index10.id_card%type;
  /* 起别名 */
  v_index_id type_index_id;
  v_id_card  type_id_card;
  /* 将查询出来的数据放到游标里 */
  cursor temp_cursor is
    select t2.id, t2.id_card
      from virtual_card10 t1, primary_index10 t2
     where t1.id_card = t2.id_card
       and t1.remark = '**市****区数据'
       and t2.remark = '**市****区数据';
begin
  open temp_cursor;
  loop
    /* 取得1000行游标数据并放到对应数组中 */
    fetch temp_cursor bulk collect
      into v_index_id, v_id_card limit 1000;
    /* 如果没有数据则退出 */
    exit when temp_cursor%notfound;
    /* 遍历数据 */
    forall i in 1 .. v_index_id.count-- 或者v_index_id.first .. v_index_id.last
    /* 业务逻辑 */
      update virtual_card10
         set index_id = v_index_id(i)
       where id_card = v_id_card(i);
    commit; --提交
  end loop;
  close temp_cursor;
end;

  执行时间:

Oracle使用游标进行分批次更新数据的6种方式及速度比对

  从oracle8开始,oracle为pl/sql引入了两个新的数据操纵语言(dml)语句:bulk collect和forall。

  这两个语句在pl/sql内部进行一种数组处理;bulk collect提供对数据的高速检索,forall可大大改进insert、update和delete操作的性能。

  oracle数据库使用这些语句大大减少了pl/sql与sql语句执行引擎的环境切换次数,从而使其性能有了显著提高。 

小结:

  数据量小的时候可以用方式二,数据量大的时候推荐使用方式六;

  一定要建索引。

以上就是oracle使用游标进行分批次更新的6种方式及速度比对的详细内容,更多关于oracle 游标的资料请关注其它相关文章!

相关标签: Oracle 游标