ORACLE 批量实例分析

Oracle学习：批量SQL实例分析与比较

2011年12月14日17:23 来源： 博客 作者：Snowtoday 编辑： 苏巧红 评论： 0条

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

　　相关阅读：

　　Oracle学习：结构化查询实例演示与解析

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

　　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

 　　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获取所有插入

转载于:https://www.cnblogs.com/Mayvar/archive/2012/02/01/wanghonghua_201202010821.html