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;

执行时间：

方式二：使用隐式游标(更新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;

执行时间：

方式三：显式游标+分批次更新(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;

执行时间：

10000条1提交，执行时间：

方式四：显式游标+数组(更新一次提交一次)(使用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;

执行时间：

方式五： 显式游标+数组(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;

执行时间：

方式六：推荐使用(使用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;

执行时间：

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

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

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

小结：

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

一定要建索引。

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

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！