ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载--ORACLE批量绑定FORALL与BULK COLLECT

转载--ORACLE批量绑定FORALL与BULK COLLECT

原创 Linux操作系统 作者:feng2371 时间:2012-01-12 16:21:18 0 删除 编辑
关键字: oracle forall bulk collect
   FORALL与BULK COLLECT的使用方法: 
1.使用FORALL比FOR效率高,因为前者只切换一次上下文,而后者将是在循环次数一样多个上下文间切换。 

2.使用BLUK COLLECT一次取出一个数据集合,比用游标条取数据效率高,尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。 

例子: 
Sql代码  收藏代码
  1. create table test_forall ( user_id number(10), user_name varchar2(20));  

select into 中使用bulk collect 
Sql代码  收藏代码
  1. DECLARE  
  2.   TYPE table_forall IS TABLE OF test_forall%ROWTYPE;  
  3.   v_table table_forall;  
  4. BEGIN  
  5.     SELECT mub.user_id,mub.user_name  
  6.          BULK COLLECT INTO v_table  
  7.     FROM mag_user_basic mub  
  8.          WHERE mub.user_id BETWEEN 10000 AND 10100;  
  9.     FORALL idx IN 1..v_table.COUNT  
  10.            INSERT INTO test_forall VALUES v_table(idx);  
  11.            --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error  
  12.            --在PL/SQL中,BULK In-BIND与RECORD,%ROWTYPE是不能在一块使用的,  
  13.            --也就是说,BULK In-BIND只能与简单类型的数组一块使用  
  14.     COMMIT;  
  15.   
  16. EXCEPTION  
  17.     WHEN OTHERS THEN  
  18.         ROLLBACK;  
  19.     
  20. END;  

fetch into 中使用bulk collect 
Sql代码  收藏代码
  1. DECLARE  
  2.   TYPE table_forall IS TABLE OF test_forall%ROWTYPE;  
  3.   v_table table_forall;     
  4.     
  5.   CURSOR c1 IS  
  6.     SELECT mub.user_id,mub.user_name  
  7.          FROM mag_user_basic mub  
  8.            WHERE mub.user_id BETWEEN 10000 AND 10100;  
  9. BEGIN  
  10.    OPEN c1;  
  11.    --在fetch into中使用bulk collect  
  12.    FETCH c1 BULK COLLECT INTO v_table;  
  13.      
  14.    FORALL idx IN 1..v_table.COUNT  
  15.          INSERT INTO test_forall VALUES v_table(idx);  
  16.     COMMIT;  
  17.   
  18. EXCEPTION  
  19.     WHEN OTHERS THEN  
  20.         ROLLBACK;  
  21. END;  

在returning into中使用bulk collect 
Sql代码  收藏代码
  1. CREATE TABLE test_forall2 AS SELECT * FROM test_forall;  
  2. ----在returning into中使用bulk collect  
  3. DECLARE  
  4.    TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;  
  5.    enums IdList;  
  6.    TYPE NameList IS TABLE OF test_forall.user_name%TYPE;  
  7.    names NameList;  
  8. BEGIN  
  9.    DELETE FROM test_forall2 WHERE user_id = 10100  
  10.         RETURNING user_id, user_name BULK COLLECT INTO enums, names;  
  11.    dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');  
  12.    FOR i IN enums.FIRST .. enums.LAST  
  13.    LOOP  
  14.      dbms_output.put_line('User #' || enums(i) || ': ' || names(i));  
  15.    END LOOP;  
  16.    COMMIT;  
  17.      
  18. EXCEPTION  
  19.     WHEN OTHERS THEN  
  20.         ROLLBACK;  
  21.          
  22. END;  

批量更新中,将for改成forall 
Sql代码  收藏代码
  1. DECLARE   
  2.     TYPE NumList IS VARRAY(20) OF NUMBER;                                                  
  3.     depts NumList := NumList(10, 30, 70, ...);  
  4.  -- department numbers                      
  5.      BEGIN          
  6.      ...             
  7.        FOR i IN depts.FIRST..depts.LAST  
  8.        LOOP  
  9.        ...  
  10.        --UPDATE statement is sent to the SQL engine   
  11.        -- with each iteration of the FOR loop!  
  12.          UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);  
  13.       END LOOP:   
  14.     END;            

Sql代码  收藏代码
  1. --UPDATE statement is sent to the SQL engine just once, with the entire nested table  
  2. FORALL i IN depts.FIRST..depts.LAST   
  3.   UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);   

To maximize performance, rewrite your programs as follows: 
a. If an INSERT, UPDATE, or DELETE statement executes inside a loop and References collection elements, move it into a FORALL statement. 
b. If a SELECT INTO, FETCH INTO, or RETURNING INTO clause references a 
Collection, incorporate the BULK COLLECT clause. 
c. If possible, use host arrays to pass collections back and forth between your Programs and the database server. 
d. If the failure of a DML operation on a particular row is not a serious problem,Include the keywords SAVE EXCEPTIONS in the FORALL statement and report Or clean up the errors in a subsequent loop using the %BULK_EXCEPTIONS Attribute.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11893231/viewspace-714838/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-06

  • 博文量
    56
  • 访问量
    180604