第五章_p的l-sql集合与记录2_-_pl-sql用户指南与参考.pdf

第五章 PL/SQL 集合与记录 （2） 十二、使用集合批量绑定减少循环开销 如下图所示，PL/SQL 引擎会执行过程化语句，但它把SQL 语句发送给SQL 引擎处理，然后SQL 引擎把 处理的结果返回给PL/SQL 引擎。 PL/SQL 和SQL 引擎间的频繁切换会大大降低效率。典型的情况就是在一个循环中反复执行SQL 语句。 例如，下面的DELETE 语句就会在FOR 循环中被多次发送到SQL 引擎中去： DECLARE TYPE numlist IS VARRAY(20) OF NUMBER; depts numlist : numlist(10, 30, 70); -- department numbers BEGIN ... FOR i IN depts.FIRST .. depts.LAST LOOP DELETE FROM emp WHERE deptno depts(i); END LOOP; END; 这种情况下，如果SQL 语句影响了四行或更多行时，使用批量绑定就会显著地提高性能。 1、批量绑定如何提高性能 用SQL 语句中为PL/SQL 变量赋值称为绑定，PL/SQL 绑定操作可以分为三种： 1. 内绑定(in-bind)：用INSERT 或UPDATE 语句将PL/SQL 变量或主变量保存到数据库。 2. 外绑定(out-bind)：通过INSERT、UPDATE 或DELETE 语句的RETURNING 子句的返回值 为PL/SQL 变量或主变量赋值。 3. 定义(define)：使用SELECT 或FETCH 语句为PL/SQL 变量或主变量赋值。 DML 语句可以一次性传递集合中所有的元素，这个过程就是批量绑定。如果集合有20 个元素，批量绑定 的一次操作就相当于执行20 次SELECT、 INSERT、UPDATE 或DELETE 语句。这项技术是靠减少 PL/SQL 和SQL 引擎间的切换次数来提高性能的。要对INSERT、UPDATE 和 DELETE 语句使用批量绑 定，就要用PL/SQL 的FORALL 语句。 如果要在SELECT 语句中使用批量绑定，我们就要在SELECT 语句后面加上一个BULK COLLECT 子句 来代替INTO 子句。  例一：对DELETE 语句应用批量绑定 下面的DELETE 语句只往SQL 引擎中发送一次，即使是执行了三次DELETE 操作： DECLARE TYPE numlist IS VARRAY(20) OF NUMBER; depts numlist : numlist(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST .. depts.LAST DELETE FROM emp WHERE deptno depts(i); END;  例二：对INSERT 语句应用批量绑定 下例中，我们把5000 个零件编号和名称放到索引表中。所有的表元素都向数据库插入两次：第一次使用 FOR 循环，然后使用FORALL 语句。实际上，FORALL 版本的代码执行速度要比FOR 语句版本的快得多。 SQL SET SERVEROUTPUT ON SQL CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15)); Table created. SQL GET test.sql 1 DECLARE 2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER; 3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; 4 pnums NumTab; 5 pnames NameTab; 6 t1 NUMBER(5);