【IT168技术】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引擎。
FORALL
下面的两个例子对比了FORALL与FOR循环之间的区别：
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的情况下对异常的处理：
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这个集合中：
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:
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
BULK COLLECT
假设有一条SQL查询，返回的记录中包含5行，那么如果在PL/SQL中执行此查询，会有5次的PL/SQL – SQL交互，如果使用BULK COLLECT，可以降低到1次。
BULK COLLECT子句可以出现在以下语句中：
SELECT INTO
FETCH
RETURNING INTO
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循环也可以)：
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，我们来一个综合的例子：
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循环时是什么结果：
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获取所有插入的数据
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28867265/viewspace-760130/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28867265/viewspace-760130/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
