ORACLE HANDBOOK系列之四：批量SQL，BULK SQL

PL/SQL引入过程化语言的相应元素，比如条件分支或者循环，不过，SQL本身仍然作为主体嵌套于其中，由于需要SQL引擎才能执行SQL命令，对于PL/SQL程序，往往存在许多PL/SQL引擎 - SQL引擎之间的交互，过多这样的交互会对性能产生负面影响。

Oracle在PL/SQL中引入了BULK SQL，用于尽量减少PL/SQL – SQL引擎之间的交互，以期提高性能。具体而言，Oracle BULK SQL包括FORALL语句、BULK COLLECT子句。前者将多条语句（通常是DML）一次性发送给SQL引擎；后者将SQL引擎所获得的结果一次性返回给PL/SQL引擎。

（1）FORALL

下面的两个例子对比了FORALL与FOR循环之间的区别：

SQL> create table t_bulk as select * from employees;

SQL> desc t_bulk;

Name Type Nullable Default Comments

-------------- ------------ -------- ------- --------

EMPLOYEE_ID NUMBER(6) Y

FIRST_NAME VARCHAR2(20) Y

LAST_NAME VARCHAR2(25)

EMAIL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20) Y

HIRE_DATE DATE

JOB_ID VARCHAR2(10)

SALARY NUMBER(8,2) Y

COMMISSION_PCT NUMBER(2,2) Y

MANAGER_ID NUMBER(6) Y

DEPARTMENT_ID NUMBER(4) Y

DECLARE

TYPE NumList IS VARRAY(20) OF NUMBER;

depts NumList := NumList(10, 30, 70);

BEGIN

FOR i IN depts.FIRST..depts.LAST LOOP

DELETE FROM t_bulk

WHERE department_id = depts(i);

END LOOP;

END;

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 t_bulk

WHERE department_id = depts(i);

END;

虽然从内部执行机制上来说，两个循环有很大的区别，但从语法上来说，还是非常类似的。不过有一个小细节需要注意，就是FORALL语句并没有对应的END语句。

我们再来看看使用FORALL的情况下对异常的处理：

CREATE TABLE t_bulk2(f1 NUMBER(3));

DECLARE

TYPE type1 IS TABLE OF NUMBER;

v type1:=type1(1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001);

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE t_bulk2';

--

FORALL idx IN v.FIRST..v.LAST

INSERT INTO t_bulk2 VALUES(v(idx));

--

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

ORA-01438: 值大于为此列指定的允许精度

PL/SQL procedure successfully completed

SQL> SELECT * FROM t_bulk2;

F1

----

1

2

Oracle 9i中引入了SAVE EXCEPTIONS语法及与之对应的“ORA-24381： error(s) in array DML”异常，使用它们，我们可以跳过FORALL中出现异常的语句，并将异常保存在SQL%BULK_EXCEPTIONSP这个集合中：

DECLARE

TYPE type1 IS TABLE OF NUMBER;

v type1:=type1(1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001);

--

BULK_ERROR EXCEPTION;

PRAGMA EXCEPTION_INIT(BULK_ERROR, -24381);

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE t_bulk2';

--

FORALL idx IN v.FIRST..v.LAST SAVE EXCEPTIONS

INSERT INTO t_bulk2 VALUES(v(idx));

--

EXCEPTION

WHEN BULK_ERROR THEN

FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||', Statement: #'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

END LOOP;

WHEN OTHERS THEN

NULL;

END;

ORA-01438: 值大于为此列指定的允许精度, Statement: #3

ORA-01438: 值大于为此列指定的允许精度, Statement: #7

ORA-01438: 值大于为此列指定的允许精度, Statement: #10

PL/SQL procedure successfully completed

SQL> SELECT * FROM t_bulk2;

F1

----

1

2

4

5

6

8

9

（注意使用ERROR_CODE时要加上负号。）

下面介绍如何获取第一条语句所影响的行数，这需要使用SQL%BULK_ROWCOUNT:

create table t_bulk3(fid number);

insert into t_bulk3 values(1);

insert into t_bulk3 values(2);

insert into t_bulk3 values(2);

insert into t_bulk3 values(3);

insert into t_bulk3 values(3);

insert into t_bulk3 values(3);

insert into t_bulk3 values(3);

DECLARE

TYPE type1 IS TABLE OF NUMBER;

v type1:=type1(1, 2, 3, 4);

--

BULK_ERROR EXCEPTION;

PRAGMA EXCEPTION_INIT(BULK_ERROR, -24381);

BEGIN

FORALL idx IN v.FIRST..v.LAST SAVE EXCEPTIONS

DELETE FROM t_bulk3 WHERE fid=v(idx);

--

FOR idx IN v.FIRST..v.LAST LOOP

DBMS_OUTPUT.PUT_LINE('Statement: #'||idx||', '||SQL%BULK_ROWCOUNT(idx)||' rows were impacted.');

END LOOP;

--

EXCEPTION

WHEN BULK_ERROR THEN

FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||', Statement: #'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

END LOOP;

WHEN OTHERS THEN

NULL;

END;

Statement: #1, 1 rows were impacted.

Statement: #2, 2 rows were impacted.

Statement: #3, 4 rows were impacted.

Statement: #4, 0 rows were impacted.

PL/SQL procedure successfully completed

（2）BULK COLLECT

假设有一条SQL查询，返回的记录中包含5行，那么如果在PL/SQL中执行此查询，会有5次的PL/SQL – SQL交互，如果使用BULK COLLECT，可以降低到1次。

BULK COLLECT子句可以出现在以下语句中：

SELECT INTO

FETCH

RETURNING INTO

create table t_bulk4(fid number, fval varchar2(20));

insert into t_bulk4 values(1,'abc');

insert into t_bulk4 values(2,'def');

insert into t_bulk4 values(3,'xyz');

insert into t_bulk4 values(4,'xxx');

insert into t_bulk4 values(5,'123');

commit;

DECLARE

TYPE type1 IS TABLE OF t_bulk4%ROWTYPE;

v type1;

BEGIN

SELECT * BULK COLLECT INTO v FROM t_bulk4;

--

FOR i IN 1..v.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(v(i).fid||' '||v(i).fval);

END LOOP;

END;

1 abc

2 def

3 xyz

4 xxx

5 123

PL/SQL procedure successfully completed

另一个INDEX BY集合的示例（实际上使用跟上例一样的FOR循环也可以）：

DECLARE

TYPE type1 IS TABLE OF t_bulk4%ROWTYPE INDEX BY PLS_INTEGER;

v type1;

idx PLS_INTEGER;

BEGIN

SELECT * BULK COLLECT INTO v FROM t_bulk4;

--

idx:=v.FIRST;

WHILE(idx IS NOT NULL) LOOP

DBMS_OUTPUT.PUT_LINE(v(idx).fid||' '||v(idx).fval);

idx := v.NEXT(idx);

END LOOP;

END;

FETCH cursor BULK COLLECT INTO的使用与上述例子都类似，不多写了。

关于RETURNING INTO + BULK COLLECT，我们来一个综合的例子：

create table t_bulk5(fid number);

DECLARE

TYPE type1 IS TABLE OF NUMBER;

v type1:=type1(1, 2, 3, 5);

--

TYPE type2 IS TABLE OF t_bulk5.fid%TYPE;

v2 type2;

BEGIN

FORALL idx IN v.FIRST..v.LAST

INSERT INTO t_bulk5 VALUES(v(idx)) RETURNING fid BULK COLLECT INTO v2;

--

DBMS_OUTPUT.PUT_LINE(v2.COUNT);

END;

4

PL/SQL procedure successfully completed

顺便比较一下使用FOR循环时是什么结果：

DECLARE

TYPE type1 IS TABLE OF NUMBER;

v type1:=type1(1, 2, 3, 5);

--

TYPE type2 IS TABLE OF t_bulk5.fid%TYPE;

v2 type2;

BEGIN

FOR idx IN v.FIRST..v.LAST LOOP

INSERT INTO t_bulk5 VALUES(v(idx)) RETURNING fid BULK COLLECT INTO v2;

END LOOP;

--

DBMS_OUTPUT.PUT_LINE(v2.COUNT);

END;

1

PL/SQL procedure successfully completed

这实际上也好理解，因为上面的INSERT语句每次影响的只有一行，所以第二例中，保留的是循环中最后一次执行的INSERT所影响的行数，当然是1；而由于FORALL语句会将所有语句一次性提交到数据库，这也使得我们可以使用RETURNING INTO + BULK COLLECT获取所有插入的数据。