Oracle使用BULK COLLECT和FORALL语句批量处理数据
程序员文章站
2022-04-01 07:58:14
...
Oracle使用BULK COLLECT和FORALL语句批量处理数据
PL/SQL块的执行过程：当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给SQL引擎来执行；SQL引擎执行完毕后，将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互，称为上下文交换。过多的上下文交换将带来额外的开销，导致效率降低，处理速度变慢。
从Oracle8i开始PL/SQL引入了两个新的数据操纵语句：FORALL、BULK COLLECT，这些语句大大减少了上下文切换次数（一次切换多次执行），同时提高DML性能。
一、BULK COLLECT语句
使用Bulk Collect进行批量检索，会将检索结果结果一次性绑定到一个集合变量中，而不是通过游标一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT。
1、在SELECT INTO语句中使用BULK COLLECT INTO
语法如下：
SELECT 字段列表 BULK COLLECT INTO var_collect
FROM 表名 WHERE 条件;
说明：
（1）字段列表：和后面的集合变量要向对应。
（2）var_collect：集合变量（联合数组等），用来存放查到的结果。
例如：定义一个存储过程，根据用户输入的部门编号显示此部门员工的编号、姓名、雇佣日期和工资：
create or replace procedure sp_emp_deptno
(v_deptno number)
as
type t_row_emp is record(
empno emp.empno%type,
ename emp.ename%type,
hiredate emp.hiredate%type,
sal emp.sal%type
);
type t_table_emp is table
of t_row_emp index by binary_integer;
v_table_emp t_table_emp;
my_err exception;
begin
select empno,ename,hiredate,sal bulk collect
into v_table_emp
from emp
where deptno=v_deptno;
if v_table_emp.count=0 then
raise my_err;
end if;
for i in v_table_emp.first..v_table_emp.last loop
dbms_output.put_line('雇员编号：'||v_table_emp(i).empno
||' 姓名：'||v_table_emp(i).ename
||' 雇佣日期：'||v_table_emp(i).hiredate
||' 工资：'||v_table_emp(i).sal);
end loop;
exception
when my_err then
dbms_output.put_line('不存在此部门');
end;
/
调用该存储过程两次，一次参数为20，一次为21，结果如下：
SQL> call sp_emp_deptno(10);
雇员编号：7934 姓名：MILLER 雇佣日期：1982-01-23 00:00:00 工资：1300
雇员编号：7782 姓名：CLARK 雇佣日期：1981-06-09 00:00:00 工资：2450
雇员编号：7839 姓名：KING 雇佣日期：1981-11-17 00:00:00 工资：5000
Call completed.
SQL> call sp_emp_deptno(12);
不存在此部门
Call completed.
SQL> call sp_emp_deptno(20);
雇员编号：7788 姓名：SCOTT 雇佣日期：1987-04-19 00:00:00 工资：2000
雇员编号：7876 姓名：ADAMS 雇佣日期：1987-05-23 00:00:00 工资：1100
雇员编号：7566 姓名：JONES 雇佣日期：1981-04-02 00:00:00 工资：2975
雇员编号：7902 姓名：FORD 雇佣日期：1981-12-03 00:00:00 工资：3000
雇员编号：7369 姓名：SMITH 雇佣日期：1980-12-17 00:00:00 工资：800
Call completed.
SQL> call sp_emp_deptno(21);
不存在此部门
Call completed.
2、在FETCH语句中使用BULK COLLECT INTO
语法如下：
FETCH cur BULK COLLECT INTO var_collect [LIMIT rows];
说明：
（1）cur：数据集合，比如游标。
（2）var_collect：集合变量（联合数组等），用来存放查到的结果；
（3）[LIMIT rows]：限制每次获取的数据量，如果省略则获取全部数据。
举例：
SQL>
create or replace procedure sp_fetch_emp
as
cursor cur_emp is
select empno,ename,sal,comm from emp;
type t_table_emp is table
of cur_emp%rowtype index by binary_integer;
v_table_emp t_table_emp;
my_err exception;
v_count number:=1;
begin
open cur_emp;
loop
fetch cur_emp bulk collect
into v_table_emp limit 5;
if v_table_emp.count=0 then
raise my_err;
end if;
dbms_output.put_line('第'||trim(to_char((v_count-1)*5+1,9999))||
'~'||trim(to_char(v_count*5,9999))||'名员工名单如下：');
v_count:=v_count+1;
for i in 1..v_table_emp.count loop
dbms_output.put_line(
'雇员编号：'||v_table_emp(i).empno||
'姓名：'||v_table_emp(i).ename||
'工资：'||v_table_emp(i).sal||
'奖金：'||v_table_emp(i).comm
);
end loop;
exit when cur_emp%notfound;
end loop;
close cur_emp;
exception
when my_err then
dbms_output.put_line('没有员工！');
end;
/
Procedure created.
调用该存储过程，结果如下：
SQL> call sp_fetch_emp();
第1~5名员工名单如下：
雇员编号：7934姓名：MILLER工资：1301奖金：100
雇员编号：7698姓名：BLAKE工资：2850奖金：
雇员编号：7499姓名：ALLEN工资：1600奖金：300
雇员编号：7788姓名：SCOTT工资：2001奖金：100
雇员编号：7876姓名：ADAMS工资：1101奖金：100
第6~10名员工名单如下：
雇员编号：7654姓名：MARTIN工资：1250奖金：1400
雇员编号：7900姓名：JAMES工资：950奖金：
雇员编号：7566姓名：JONES工资：2976奖金：100
雇员编号：7902姓名：FORD工资：3001奖金：100
雇员编号：7369姓名：SMITH工资：801奖金：100
第11~15名员工名单如下：
雇员编号：7521姓名：WARD工资：1250奖金：500
雇员编号：7844姓名：TURNER工资：1500奖金：0
雇员编号：7782姓名：CLARK工资：2451奖金：
雇员编号：7839姓名：KING工资：5001奖金：
雇员编号：8101姓名：TOMMY工资：8000奖金：1200
第16~20名员工名单如下：
雇员编号：8101姓名：MARK DOWN工资：3000奖金：
Call completed.
3、在DML语句中配合RETURNING使用BULK COLLECT INTO
BULK COLLECT可以与INSERT，DELETE，UPDATE语句结合使用，返回这些DML语句执行后所影响的记录内容。语法如下：
DML语句
RETURNING 字段列表 BULK COLLECT INTO var_field;
说明：var_field是一个和字段列表对应的集合。
例子：
SQL>
create or replace procedure sp_update_emp_bulk
(v_deptno number)
as
type t_table_emp is table of
scott.emp%rowtype index by binary_integer;
v_table_emp t_table_emp;
my_err exception;
begin
update emp set sal=sal+1 where deptno=v_deptno
returning empno,ename,job,mgr,hiredate,sal,comm,deptno
bulk collect into v_table_emp;
if v_table_emp.count=0 then
raise my_err;
end if;
dbms_output.put_line('有以下雇员的工资信息被更新，更新结果如下：');
for i in v_table_emp.first..v_table_emp.last loop
dbms_output.put_line('雇员编号：'||v_table_emp(i).empno||'，姓名：'||v_table_emp(i).ename||'，工资：'||v_table_emp(i).sal);
end loop;
exception
when my_err then
dbms_output.put_line('没有记录被更新!');
end;
/
Procedure created.
调用该存储过程：
SQL> call sp_update_emp_bulk(20);
有以下雇员的工资信息被更新，更新结果如下：
雇员编号：7788，姓名：SCOTT，工资：2003
雇员编号：7876，姓名：ADAMS，工资：1103
雇员编号：7566，姓名：JONES，工资：2978
雇员编号：7902，姓名：FORD，工资：3003
雇员编号：7369，姓名：SMITH，工资：803
Call completed.
二、FORALL
使用FORALL语句，可以将多个DML批量发送给SQL引擎来执行，最大限度地减少上下文交互所带来的开销。
FORALL语句的语法格式如下：
格式一：
FORALL 下标变量 IN 下限..上限
[SAVE EXCEPTIONS] DML语句;
格式二：
FORALL 下标变量 IN INDICES OF(跳过没有赋值的元素) 集合
[BETWEEN 下限 AND 上限]
[SAVE EXCEPTIONS] DML语句;
格式三：
FORALL 下标变量 IN VALUES OF 集合(把该集合中的值当作下标，且该集合值的类型只能是PLS_INTEGER BINARY_INTEGER)
[SAVE EXCEPTIONS] DML语句;
说明：
（1）下标变量的下限和上限之间是按照1来递增的。
（2）SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。
（3）DML语句必须是必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。不能单独地引用集合中的元素，只能批量地使用集合。
1、批量插入数据测试
（1）创建表t1和一个序列
create table t1(id number(10) primary key,name varchar(20));
create sequence seq_t01_id;
（2）编写一个存储过程，向t1表插入100万条记录（直接插入，不使用forall）
SQL>
create or replace procedure sp_insert_t01
as
begin
for i in 1..1000000 loop
insert into t1 values(seq_t01_id.nextval,'name'||i);
end loop;
end;
/
Procedure created.
执行存储过程，插入100万条记录一共使用1分15秒：
SQL> set timing on;
SQL> call sp_insert_t01();
Call completed.
Elapsed: 00:01:15.81
SQL> select count(*) from t1;
COUNT(*)
----------
1000000
Elapsed: 00:00:00.09
（3）编写一个存储过程，使用forall命令向t1表插入100万条记录
SQL>
create or replace procedure sp_insert_t01_forall
as
type t_table_t1 is table of
t1%rowtype index by binary_integer;
v_table_t1 t_table_t1;
begin
for i in 1..100 loop
for j in 1..10000 loop
v_table_t1(j).id:=seq_t01_id.nextval;
v_table_t1(j).name:='name'||
trim(to_char((i-1)*10000+j,9999999));
end loop;
forall j in 1..10000
insert into t1 values v_table_t1(j);
end loop;
end;
17 /
Procedure created.
执行存储过程，插入100万条记录一共使用45秒：
SQL> call sp_insert_t01_forall();
Call completed.
Elapsed: 00:00:44.77
SQL> select count(*) from t1;
COUNT(*)
----------
1000000
Elapsed: 00:00:00.04
2、批量更新数据测试
（1）直接更新t01表的name字段取值
用update直接更新200万条记录，用时1分钟8秒。
SQL> update t1 set name='Tom';
2000101 rows updated.
Elapsed: 00:01:08.33
SQL>
SQL> select * from (select rownum rn,id,name from t1) where rn=1989900;
RN ID NAME
---------- ---------- --------------------------------------------------
1989900 16405325 Tom
（2）编写一个存储过程，使用forall更新t01表
create or replace procedure sp_update_t1_forall
as
cursor cur_t1 is (select id,name from t1);
type t_table_t1 is table of t1%rowtype
index by binary_integer;
v_table_t1 t_table_t1;
begin
open cur_t1;
loop
fetch cur_t1 bulk collect into v_table_t1 limit 2000;
for i in v_table_t1.first..v_table_t1.last loop
v_table_t1(i).name:='Jack-01';
end loop;
forall i in v_table_t1.first..v_table_t1.last
update t1 set row=v_table_t1(i)
where id=v_table_t1(i).id;
exit when cur_t1%notfound;
end loop;
close cur_t1;
end;
/
执行存储过程，更新200万条记录一共使用41秒：
SQL> call sp_update_t1_forall();
Call completed.
Elapsed: 00:00:41.43
SQL> select * from (select rownum rn,id,name from t1) where rn=1989900;
RN ID NAME
---------- ---------- --------------------------------------------------
1989900 16405325 Jack-01
3、批量删除数据测试
（1）使用delete from命令直接删除
删除200万条记录一共用时1分钟6秒。
SQL> select count(*) from t01;
COUNT(*)
----------
2000000
Elapsed: 00:00:00.11
SQL> delete from t01;
2000000 rows deleted.
Elapsed: 00:01:05.98
（2）编写一个存储过程，使用forall删除t1表中的记录
create or replace procedure sp_delete_t1_forall
as
cursor cur_t1 is (select id from t1);
type t_table_t1 is table of number(10)
index by binary_integer;
v_table_t1 t_table_t1;
begin
open cur_t1;
loop
fetch cur_t1 bulk collect into v_table_t1 limit 2000;
forall i in v_table_t1.first..v_table_t1.last
delete t1
where id=v_table_t1(i);
exit when cur_t1%notfound;
end loop;
close cur_t1;
end;
/
执行存储过程，删除200万条记录一共使用1分29秒：
SQL> select count(*) from t1;
COUNT(*)
----------
2000000
Elapsed: 00:00:00.24
SQL>
SQL>
SQL>
SQL> call sp_delete_t1_forall();
Call completed.
Elapsed: 00:01:28.82
4、indices of 参数
例子：
SQL> create table emp_bakup as select * from emp where 2=1;
Table created.
SQL>
declare
type t_table_emp is table of emp%rowtype
index by binary_integer;
v_table_emp t_table_emp;
begin
select * bulk collect into v_table_emp from emp;
v_table_emp.delete(3);
v_table_emp.delete(5);
v_table_emp.delete(6);
v_table_emp.delete(8);
v_table_emp.delete(11);
v_table_emp.delete(13);
v_table_emp.delete(15);
forall i in indices of v_table_emp
insert into emp_bakup values v_table_emp(i);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL> select * from emp_bakup;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- -----
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1301 100 10
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 2003 100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3003 100 20
7369 SMITH CLERK 7902 1980-12-17 00:00:00 803 100 20
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7839 KING PRESIDENT 1981-11-17 00:00:00 5001 10
8101 MARK DOWN ANALYST 7566 2020-02-13 10:59:11 3000 40
9 rows selected.
Elapsed: 00:00:00.01
5、in values of 参数
SQL> truncate table emp_bakup;
Table truncated.
Elapsed: 00:00:00.07
SQL>
declare
type t_table_index is table of binary_integer
index by binary_integer;
v_table_index t_table_index;
type t_table_emp is table of emp%rowtype
index by binary_integer;
v_table_emp t_table_emp;
begin
v_table_index(1):=3;
v_table_index(2):=5;
v_table_index(3):=8;
v_table_index(4):=11;
select * bulk collect into v_table_emp from emp;
for i in v_table_emp.first..v_table_emp.last loop
v_table_emp(i).empno:=i;
end loop;
forall i in values of v_table_index
insert into emp_bakup values v_table_emp(i);
end;
20 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL> select * from emp_bakup;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- -----
3 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
5 ADAMS CLERK 7788 1987-05-23 00:00:00 1103 100 20
8 JONES MANAGER 7839 1981-04-02 00:00:00 2978 100 20
11 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
Elapsed: 00:00:00.00
上一篇： OpenCV图像识别-模板匹配
下一篇： ITK学习笔记——RGB图像读取写入