ORACLE HANDBOOK系列之四:批量SQL(BULK SQL)

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

 

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

 

(1)FORALL

 

下面的两个例子对比了FORALLFOR循环之间的区别:

 

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(20OF NUMBER;
  depts NumList :
= NumList(103070);  
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(20OF NUMBER;
  depts NumList :
= NumList(103070);  -- 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(123000456777778910001);
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(123000456777778910001);
  
--
  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(1234);
  
--
  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: #
11 rows were impacted.
Statement: #
22 rows were impacted.
Statement: #
34 rows were impacted.
Statement: #
40 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(1235);
  
--
  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(1235);
  
--
  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获取所有插入的数据

posted @ 2011-03-26 14:18  Morven.Huang  阅读(1492)  评论(0编辑  收藏  举报