1.情景展示
一共有22w条数据， 需要将a表的主键更新至b表的指定字段，如何快速完成更新？
2.解决方案
声明：
解决方案不只一种，该文章只介绍快速游标法及代码实现；
两张表的id和id_card字段都建立了索引。
方式一：使用隐式游标（更新一次提交1次）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
--快速游标法
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次）（推荐使用）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
/* 使用隐式游标进行分批次更新 */
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提交）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
/* 使用游标进行分批次更新 */
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）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
/* 使用游标+数组进行更新（更新一次提交一次） */
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）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
/* 使用游标+数组进行更新（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）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
/* 使用游标+数组进行更新（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 游标的资料请关注服务器之家其它相关文章！
原文链接：https://www.cnblogs.com/Marydon20170307/p/10097243.html