forall and bulk collect

   FORALL与BULK COLLECT的使用方法:
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。

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

例子:

Sql代码

1. create table test_forall ( user_id number(10), user_name varchar2(20));  

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;  

DECLARE
  TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
  v_table table_forall;
BEGIN
    SELECT mub.user_id,mub.user_name
         BULK COLLECT INTO v_table
    FROM mag_user_basic mub
         WHERE mub.user_id BETWEEN 10000 AND 10100;
    FORALL idx IN 1..v_table.COUNT
           INSERT INTO test_forall VALUES v_table(idx);
           --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
           --在PL/SQL中，BULK In-BIND与RECORD，%ROWTYPE是不能在一块使用的，
           --也就是说，BULK In-BIND只能与简单类型的数组一块使用
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
  
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;  

DECLARE
  TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
  v_table table_forall;   
  
  CURSOR c1 IS
    SELECT mub.user_id,mub.user_name
         FROM mag_user_basic mub
           WHERE mub.user_id BETWEEN 10000 AND 10100;
BEGIN
   OPEN c1;
   --在fetch into中使用bulk collect
   FETCH c1 BULK COLLECT INTO v_table;
   
   FORALL idx IN 1..v_table.COUNT
         INSERT INTO test_forall VALUES v_table(idx);
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
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;  

CREATE TABLE test_forall2 AS SELECT * FROM test_forall;
----在returning into中使用bulk collect
DECLARE
   TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;
   enums IdList;
   TYPE NameList IS TABLE OF test_forall.user_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM test_forall2 WHERE user_id = 10100
        RETURNING user_id, user_name BULK COLLECT INTO enums, names;
   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
     dbms_output.put_line('User #' || enums(i) || ': ' || names(i));
   END LOOP;
   COMMIT;
   
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
       
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;            

DECLARE 
    TYPE NumList IS VARRAY(20) OF NUMBER;                                                
    depts NumList := NumList(10, 30, 70, ...);
 -- department numbers                    
     BEGIN        
     ...           
       FOR i IN depts.FIRST..depts.LAST
       LOOP
       ...
       --UPDATE statement is sent to the SQL engine 
       -- with each iteration of the FOR loop!
         UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
      END LOOP: 
    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);   

--UPDATE statement is sent to the SQL engine just once, with the entire nested table
FORALL i IN depts.FIRST..depts.LAST 
  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.

 

 

The FORALL keyword can improve the performance of INSERT, UPDATE, or DELETE statements that reference collection elements.

For example, the following PL/SQL block increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, both with and without using bulk binds:

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN

-- Efficient method, using a bulk bind
   FORALL i IN Id.FIRST..Id.LAST   -- bulk-bind the VARRAY
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);

-- Slower method, running the UPDATE statements within a regular loop
   FOR i IN Id.FIRST..Id.LAST LOOP
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);
   END LOOP;
END;


Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.

If you have a set of rows prepared in a PL/SQL table, you can bulk-insert or bulk-update the data using a loop like:

FORALL i in Emp_Data.FIRST..Emp_Data.LAST
    INSERT INTO Emp_tab VALUES(Emp_Data(i));

Ref: Oracle Document (PL/SQL Procedures and Packages)