Oracle数据库之FORALL与BULK COLLECT语句
我们再来看一下PL/SQL块的执行过程:当PL/SQL运行时引擎处理一块代码时,它使用PL/SQL引擎来执行过程化的代码,而将SQL语句发送给SQL引擎来执行;SQL引擎执行完毕后,将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互,称为上下文交换(context switch)。每发生一次交换,就会带来一定的额外开销。
使用FORALL,可以将多个DML批量发送给SQL引擎来执行,最大限度地减少上下文交互所带来的开销。下面是 FORALL 的一个示意图:
语法:
1 FORALL index_name IN 2 { lower_bound .. upper_bound 3 | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ] 4 | VALUES OF index_collection 5 } 6 [ SAVE EXCEPTIONS ] dml_statement;
说明:
index_name:一个无需声明的标识符,作为集合下标使用。
lower_bound .. upper_bound:数字表达式,来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。
INDICES OF collection_name:用于指向稀疏数组的实际下标。跳过没有赋值的元素,例如被 DELETE 的元素,NULL 也算值。
VALUES OF index_collection_name:把该集合中的值当作下标,且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。
SAVE EXCEPTIONS:可选关键字,表示即使一些DML语句失败,直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。
dml_statement:静态语句,例如:UPDATE或者DELETE;或者动态(EXECUTE IMMEDIATE)DML语句。
示例所使用表结构:
1 CREATE TABLE tmp_tab( 2 id NUMBER(5), 3 name VARCHAR2(50) 4 );
示例1,使用FORALL批量插入、修改、删除数据:
1 --批量插入 2 DECLARE 3 -- 定义索引表类型 4 TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER; 5 tb_table tb_table_type; 6 BEGIN 7 FOR i IN 1..100 LOOP 8 tb_table(i).id:=i; 9 tb_table(i).name:='NAME'||i; 10 END LOOP; 11 12 FORALL i IN 1..tb_table.count 13 INSERT INTO tmp_tab VALUES tb_table(i); 14 END;
1 --批量修改 2 DECLARE 3 TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER; 4 tb_table tb_table_type; 5 BEGIN 6 FOR i IN 1..100 LOOP 7 tb_table(i).id:=i; 8 tb_table(i).name:='MY_NAME_'||i; 9 END LOOP; 10 FORALL i IN 1..tb_table.count 11 UPDATE tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id; 12 END;
--批量删除 DECLARE TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER; tb_table tb_table_type; BEGIN FOR i IN 1..10 LOOP tb_table(i).id:=i; tb_table(i).name:='MY_NAME_'||i; END LOOP; FORALL i IN 1..tb_table.count DELETE FROM tmp_tab WHERE id =tb_table(i).id; END;
示例2,使用INDICES OF子句:
1 DECLARE 2 TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER; 3 demo_table demo_table_type; 4 BEGIN 5 FOR i IN 1..10 LOOP 6 demo_table(i).id:=i; 7 demo_table(i).name:='NAME'||i; 8 END LOOP; 9 -- 使用集合的delete方法移除第3、6、9三个成员 10 demo_table.delete(3); 11 demo_table.delete(6); 12 demo_table.delete(9); 13 FORALL i IN INDICES OF demo_table 14 INSERT INTO tmp_tab VALUES demo_table(i); 15 END ;
示例3,使用VALUES OF子句:
1 DECLARE 2 TYPE index_poniter_type IS TABLE OF pls_integer; 3 index_poniter index_poniter_type; 4 TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER; 5 demo_table demo_table_type; 6 BEGIN 7 index_poniter := index_poniter_type(1,3,5,7); 8 FOR i IN 1..10 LOOP 9 demo_table(i).id:=i; 10 demo_table(i).name:='NAME'||i; 11 END LOOP; 12 FORALL i IN VALUES OF index_poniter 13 INSERT INTO tmp_tab VALUES demo_table(i); 14 END;
使用FORALL时,应该遵循如下规则:
BULK COLLECT子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。
通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在这几种情形下的用法。
示例:
1 DECLARE 2 -- 定义记录类型 3 TYPE emp_rec_type IS RECORD 4 ( 5 empno emp.empno%TYPE, 6 ename emp.ename%TYPE, 7 hiredate emp.hiredate%TYPE 8 ); 9 -- 定义基于记录的嵌套表 10 TYPE nested_emp_type IS TABLE OF emp_rec_type; 11 -- 声明变量 12 emp_tab nested_emp_type; 13 BEGIN 14 -- 使用BULK COLLECT将所得的结果集一次性绑定到记录变量emp_tab中 15 SELECT empno, ename, hiredate 16 BULK COLLECT INTO emp_tab 17 FROM emp; 18 19 FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP 20 DBMS_OUTPUT.PUT_LINE('当前记录: ' 21 ||emp_tab(i).empno||chr(9) 22 ||emp_tab(i).ename||chr(9) 23 ||emp_tab(i).hiredate); 24 END LOOP; 25 END;
说明:使用BULK COLLECT一次即可提取所有行并绑定到记录变量,这就是所谓的批量绑定。
在游标中可以使用BLUK COLLECT一次取出一个数据集合,比用游标单条取数据效率高,尤其是在网络不大好的情况下。
语法:
FETCH ... BULK COLLECT INTO ...[LIMIT row_number];
在使用BULK COLLECT子句时,对于集合类型会自动对其进行初始化以及扩展。因此如果使用BULK COLLECT子句操作集合,则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性,如果数据量较大,而集合在此时又自动扩展,为避免过大的数据集造成性能下降,因此可以使用LIMIT子句来限制一次提取的数据量。LIMIT子句只允许出现在FETCH操作语句的批量中。
示例:
1 DECLARE 2 CURSOR emp_cur IS 3 SELECT empno, ename, hiredate FROM emp; 4 5 TYPE emp_rec_type IS RECORD 6 ( 7 empno emp.empno%TYPE, 8 ename emp.ename%TYPE , 9 hiredate emp.hiredate%TYPE 10 ); 11 -- 定义基于记录的嵌套表 12 TYPE nested_emp_type IS TABLE OF emp_rec_type; 13 -- 声明集合变量 14 emp_tab nested_emp_type; 15 -- 定义了一个变量来作为limit的值 16 v_limit PLS_INTEGER := 5; 17 -- 定义变量来记录FETCH次数 18 v_counter PLS_INTEGER := 0; 19 BEGIN 20 OPEN emp_cur; 21 22 LOOP 23 -- fetch时使用了BULK COLLECT子句 24 FETCH emp_cur 25 BULK COLLECT INTO emp_tab 26 LIMIT v_limit; -- 使用limit子句限制提取数据量 27 28 EXIT WHEN emp_tab.COUNT = 0; -- 注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound 29 v_counter := v_counter + 1; -- 记录使用LIMIT之后fetch的次数 30 31 FOR i IN emp_tab.FIRST .. emp_tab.LAST 32 LOOP 33 DBMS_OUTPUT.PUT_LINE( '当前记录: ' 34 ||emp_tab(i).empno||CHR(9) 35 ||emp_tab(i).ename||CHR(9) 36 ||emp_tab(i).hiredate); 37 END LOOP; 38 END LOOP; 39 40 CLOSE emp_cur; 41 42 DBMS_OUTPUT.put_line( '总共获取次数为:' || v_counter ); 43 END;
BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,需要使用RETURNING子句来实现批量绑定。
示例:
1 DECLARE 2 TYPE emp_rec_type IS RECORD 3 ( 4 empno emp.empno%TYPE, 5 ename emp.ename%TYPE, 6 hiredate emp.hiredate%TYPE 7 ); 8 TYPE nested_emp_type IS TABLE OF emp_rec_type; 9 emp_tab nested_emp_type; 10 BEGIN 11 DELETE FROM emp WHERE deptno = 20 12 RETURNING empno, ename, hiredate -- 使用returning 返回这几个列 13 BULK COLLECT INTO emp_tab; -- 将返回的列的数据批量插入到集合变量 14 15 DBMS_OUTPUT.put_line( '删除 ' || SQL%ROWCOUNT || ' 行记录' ); 16 COMMIT; 17 18 IF emp_tab.COUNT > 0 THEN -- 当集合变量不为空时,输出所有被删除的元素 19 FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP 20 DBMS_OUTPUT.PUT_LINE('当前记录:' 21 || emp_tab( i ).empno || CHR( 9 ) 22 || emp_tab( i ).ename || CHR( 9 ) 23 || emp_tab( i ).hiredate 24 || ' 已被删除' ); 25 END LOOP; 26 END IF; 27 END;
如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中。
FORALL与BULK COLLECT是实现批量SQL的两个重要方式,我们可以将其结合使用以提高性能。
示例:
1 -- 创建表tb_emp 2 CREATE TABLE tb_emp AS 3 SELECT empno, ename, hiredate 4 FROM emp 5 WHERE 1 = 0; 6 7 DECLARE 8 -- 声明游标 9 CURSOR emp_cur IS 10 SELECT empno, ename, hiredate FROM emp; 11 -- 基于游标的嵌套表类型 12 TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE; 13 -- 声明变量 14 emp_tab nested_emp_type; 15 BEGIN 16 SELECT empno, ename, hiredate 17 BULK COLLECT INTO emp_tab 18 FROM emp 19 WHERE sal > 1000; 20 21 -- 使用FORALL语句将变量中的数据插入到表tb_emp 22 FORALL i IN 1 .. emp_tab.COUNT 23 INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp) 24 VALUES emp_tab( i ); 25 26 COMMIT; 27 DBMS_OUTPUT.put_line('总共向 tb_emp 表中插入记录数: ' || emp_tab.COUNT); 28 END;
在数据库中事务是工作的逻辑单元,一个事务是由一个或多个完成一组的相关行为的SQL语句组成,通过事务机制确保这一组SQL语句所作的操作要么都成功执行,完成整个工作单元操作,要么一个也不执行。
如:网上转帐就是典型的要用事务来处理,用以保证数据的一致性。
SQL92标准定义了数据库事务的四个特点:(面试时可能会问的)
一组SQL语句操作要成为事务,数据库管理系统必须保证这组操作的原子性(Atomicity)、一致性(consistency)、隔离性(Isolation)和持久性(Durability),这就是ACID特性。