文章目录
1 概述
1. 作用:提高在 pl/sql 块中,处理 dml 语句的效率
2. "上下文切换":
(1) 在 pl/sql 中,'pl/sql 块' 由 'pl/sql 引擎' 处理
(2) 而其中的 'sql 语句' 则由 'pl/sql 引擎' 发送至 'sql 引擎处理'
(3) 后者处理完毕后再向前者返回数据,两者之间的通信称为 "上下文切换",
过多的上下文切换将带来过量的性能负载。
3. pl/sql 和 sql 引擎之间的 交互("上下文切换")
(1) forall : 用于增强 'pl/sql 引擎' -> 'sql 引擎' 的交互
(2) bulk collect: 用于增强 'sql 引擎' -> 'pl/sql 引擎' 的交互
1.1 图示
举个例子: 在 pl/sql 中处理 10 个 update 语句
(1) 一般的 : 'pl/sql 引擎' '每次' 发送 1 个 update 语句给 'sql 引擎' 处理, "上下文切换" 1 次,共计 10 次
(2) forall: 'pl/sql 引擎' '一次性' 发送 10 个 update 语句给 'sql 引擎' 处理, "上下文切换" 1 次,共计 1 次
2 forall 的三种用法
1. 三种用法
(1) forall i in index_min .. index_max: '下标必须存在',否则报错
(2) forall i in indices of collection : 若下标 '不存在,就跳过'(上述的扩展)
(3) forall i in values of collection : 仅插入 values 中 '存在的记录'
2. 注意:forall 后只能紧跟 1 条 dml 语句
3. 基础数据准备
create table stu_info (
sno number(10),
sname varchar2(10)
);
2.1 forall i in index_min … index_max
-- 注意:'下标必须存在',否则报错
declare
type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
v_stu_info_rows stu_info_table;
begin
-- 制造数据
for i in 1 .. 3 loop
v_stu_info_rows(i).sno := i;
v_stu_info_rows(i).sname := 'a' || i;
end loop;
-- 演示报错(下标为 2 的元素不存在)
-- v_stu_info_rows.delete(2);
-- insert 演示(update、delete 同理)
forall i in v_stu_info_rows.first .. v_stu_info_rows.last
insert into stu_info values v_stu_info_rows (i);
-- commit;
end;
测试结果:
sno sname
1 a1
2 a2
3 a3
2.2 forall i in indices of collection
-- 注意:若下标 '不存在,就跳过'
declare
type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
v_stu_info_rows stu_info_table;
begin
-- 制造数据
for i in 1 .. 3 loop
v_stu_info_rows(i).sno := i;
v_stu_info_rows(i).sname := 'a' || i;
end loop;
-- 演示报错(下标为 2 的元素不存在)
v_stu_info_rows.delete(2);
-- insert 演示(update、delete 同理)
forall i in indices of v_stu_info_rows
insert into stu_info values v_stu_info_rows (i);
-- commit;
end;
测试结果:(跳过了 sno = 2 的记录)
sno sname
1 a1
3 a3
2.3 forall i in values of collection
-- 注意: 仅插入 values 中 '存在的记录'
declare
type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
type index_table is table of pls_integer;
v_stu_info_rows stu_info_table;
v_index_table index_table;
begin
-- 制造数据
for i in 1 .. 3 loop
v_stu_info_rows(i).sno := i;
v_stu_info_rows(i).sname := 'a' || i;
end loop;
-- values 记录列表
v_index_table := index_table(1, 3);
-- insert 演示(update、delete 同理)
forall i in values of v_index_table
insert into stu_info values v_stu_info_rows (i);
-- commit;
end;
测试结果:(没有 sno = 2 的记录)
sno sname
1 a1
3 a3
提示:一般用 pls_integer,除非批处理业务量大于 21,4748,3647,才考虑用 binary_integer
关键字 | 解释 |
---|---|
pls_integer | 检索速度快,超过最大长度时会溢出(最大长度:-2^31 至 2^31 - 1 ) |
binary_integer | 检索速度一般,超过最大长度是不会溢出 |
3 forall 与 for 效率对比
结论:
dml 执行效率: forall > for
数据准备:
-- 清空数据,方便测试
truncate table stu_info;
create table stu_info_for as select * from stu_info where 1 = 2; -- for
create table stu_info_forall as select * from stu_info where 1 = 2; -- forall
效率验证:(50W 数据插入)
declare
type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
v_stu_info_rows stu_info_table;
v_init_time pls_integer;
v_for_time pls_integer;
v_forall_time pls_integer;
begin
-- 制造数据
for i in 1 .. 500000 loop
v_stu_info_rows(i).sno := i;
v_stu_info_rows(i).sname := 'a' || i;
end loop;
-- 初始化时间
v_init_time := dbms_utility.get_time;
-- 1 演示:for
for i in 1 .. 500000 loop
insert into stu_info_for values v_stu_info_rows (i);
end loop;
v_for_time := dbms_utility.get_time;
-- 2 演示:forall
forall i in 1 .. 500000
insert into stu_info_forall values v_stu_info_rows (i);
v_forall_time := dbms_utility.get_time;
dbms_output.put_line('for 用时:' || to_char(v_for_time - v_init_time));
dbms_output.put_line('forall 用时:' || to_char(v_forall_time - v_for_time));
-- commit;
exception
when others then
dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
测试结果:(forall > for,且数据量越大,差异越大)
for 用时:2326
forall 用时:30
提示:dbms_utility.get_time -> 100 = 1s
验证思路:Oracle dbms_utility.get_time 用法