Oracle數(shù)據庫之FORALL與BULK COLLECT語句

ronaldj

Oracle數(shù)據庫之FORALL與BULK COLLECT語句

   我們再來看一下PL/SQL塊的執(zhí)行過程：當PL/SQL運行時引擎處理一塊代碼時，它使用PL/SQL引擎來執(zhí)行過程化的代碼，而將SQL語句發(fā)送給SQL引擎來執(zhí)行；SQL引擎執(zhí)行完畢后，將結果再返回給PL/SQL引擎。這種在PL/SQL引擎和SQL引擎之間的交互，稱為上下文交換（context switch）。每發(fā)生一次交換，就會帶來一定的額外開銷。

ORALL，用于增強PL/SQL引擎到SQL引擎的交換。
BULK COLLECT，用于增強SQL引擎到PL/SQL引擎的交換。（前面我們已經介紹過了）
1. FORALL介紹

使用FORALL，可以將多個DML批量發(fā)送給SQL引擎來執(zhí)行，最大限度地減少上下文交互所帶來的開銷。下面是 FORALL 的一個示意圖：

語法：

1. FORALL index_name IN
   
2. 2     { lower_bound .. upper_bound
   
3. 3     | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]
   
4. 4     | VALUES OF index_collection
   
5. 5     }
   
6. 6  [ SAVE EXCEPTIONS ] dml_statement;

復制代碼

說明：

index_name：一個無需聲明的標識符，作為集合下標使用。

lower_bound .. upper_bound：數(shù)字表達式，來指定一組連續(xù)有效的索引數(shù)字下限和上限。該表達式只需解析一次。

INDICES OF collection_name：用于指向稀疏數(shù)組的實際下標。跳過沒有賦值的元素，例如被 DELETE 的元素，NULL 也算值。

VALUES OF index_collection_name：把該集合中的值當作下標，且該集合值的類型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS：可選關鍵字，表示即使一些DML語句失敗，直到FORALL LOOP執(zhí)行完畢才拋出異常�？梢允褂肧QL%BULK_EXCEPTIONS 查看異常信息。

dml_statement：靜態(tài)語句，例如：UPDATE或者DELETE；或者動態(tài)（EXECUTE IMMEDIATE）DML語句。

2. FORALL的使用

示例所使用表結構：

1. 1 CREATE TABLE tmp_tab(
   
2. 2   id NUMBER(5),
   
3. 3   name VARCHAR2(50)
   
4. 4 );

復制代碼

示例1，使用FORALL批量插入、修改、刪除數(shù)據：

1. --批量插入
   
2. 2 DECLARE
   
3. 3   -- 定義索引表類型
   
4. 4   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY  BINARY_INTEGER;
   
5. 5   tb_table tb_table_type;
   
6. 6 BEGIN
   
7. 7   FOR i IN 1..100 LOOP
   
8. 8     tb_table(i).id:=i;
   
9. 9     tb_table(i).name:='NAME'||i;
   
10. 10   END LOOP;
    
11. 11
    
12. 12   FORALL i IN 1..tb_table.count
    
13. 13     INSERT INTO  tmp_tab VALUES tb_table(i);
    
14. 14 END;

復制代碼

1. --批量修改
   
2. 2 DECLARE
   
3. 3   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
   
4. 4   tb_table tb_table_type;
   
5. 5 BEGIN
   
6. 6   FOR i IN 1..100 LOOP
   
7. 7     tb_table(i).id:=i;
   
8. 8     tb_table(i).name:='MY_NAME_'||i;
   
9. 9   END LOOP;
   
10. 10   FORALL i IN 1..tb_table.count
    
11. 11     UPDATE tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id;
    
12. 12 END;

復制代碼

1. --批量刪除
   
2. DECLARE
   
3.   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
   
4.   tb_table tb_table_type;
   
5. BEGIN
   
6.    FOR i IN 1..10 LOOP
   
7.     tb_table(i).id:=i;
   
8.     tb_table(i).name:='MY_NAME_'||i;
   
9.    END LOOP;
   
10.    FORALL i IN 1..tb_table.count
    
11.     DELETE FROM tmp_tab WHERE id =tb_table(i).id;
    
12. END;

復制代碼

示例2，使用INDICES OF子句：

1. DECLARE
   
2. 2   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
   
3. 3   demo_table demo_table_type;
   
4. 4 BEGIN
   
5. 5   FOR i IN 1..10 LOOP
   
6. 6     demo_table(i).id:=i;
   
7. 7     demo_table(i).name:='NAME'||i;
   
8. 8   END LOOP;
   
9. 9   -- 使用集合的delete方法移除第3、6、9三個成員
   
10. 10   demo_table.delete(3);
    
11. 11   demo_table.delete(6);
    
12. 12   demo_table.delete(9);
    
13. 13   FORALL i IN INDICES OF  demo_table
    
14. 14     INSERT INTO tmp_tab VALUES demo_table(i);
    
15. 15 END ;

復制代碼

示例3，使用VALUES OF子句：

1. DECLARE
   
2. 2 TYPE index_poniter_type IS TABLE OF pls_integer;
   
3. 3   index_poniter index_poniter_type;
   
4. 4   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
   
5. 5   demo_table demo_table_type;
   
6. 6 BEGIN
   
7. 7   index_poniter := index_poniter_type(1,3,5,7);
   
8. 8   FOR i IN 1..10 LOOP
   
9. 9     demo_table(i).id:=i;
   
10. 10     demo_table(i).name:='NAME'||i;
    
11. 11   END LOOP;
    
12. 12   FORALL i IN VALUES OF index_poniter
    
13. 13     INSERT INTO  tmp_tab VALUES demo_table(i);
    
14. 14 END;

復制代碼

3. FORALL注意事項

使用FORALL時，應該遵循如下規(guī)則：

FORALL語句的執(zhí)行體，必須是一個單獨的DML語句，比如INSERT，UPDATE或DELETE。
不要顯式定義index_row，它被PL/SQL引擎隱式定義為PLS_INTEGER類型，并且它的作用域也僅僅是FORALL。
這個DML語句必須與一個集合的元素相關，并且使用FORALL中的index_row來索引。注意不要因為index_row導致集合下標越界。
lower_bound和upper_bound之間是按照步進 1 來遞增的。
在sql_statement中，不能單獨地引用集合中的元素，只能批量地使用集合。
在sql_statement中使用的集合，下標不能使用表達式。
4. BULK COLLECT介紹

BULK COLLECT子句會批量檢索結果，即一次性將結果集綁定到一個集合變量中，并從SQL引擎發(fā)送到PL/SQL引擎。

通�？梢栽赟ELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在這幾種情形下的用法。

5. BULK COLLECT的使用

5.1 在SELECT INTO中使用BULK COLLECT

示例：

1. DECLARE
   
2. 2    -- 定義記錄類型
   
3. 3    TYPE emp_rec_type IS RECORD            
   
4. 4    (  
   
5. 5       empno      emp.empno%TYPE,
   
6. 6       ename      emp.ename%TYPE,
   
7. 7       hiredate   emp.hiredate%TYPE  
   
8. 8    );  
   
9. 9    -- 定義基于記錄的嵌套表
   
10. 10    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
    
11. 11    -- 聲明變量
    
12. 12    emp_tab   nested_emp_type;  
    
13. 13 BEGIN
    
14. 14    -- 使用BULK COLLECT將所得的結果集一次性綁定到記錄變量emp_tab中  
    
15. 15    SELECT empno, ename, hiredate  
    
16. 16    BULK   COLLECT INTO emp_tab      
    
17. 17    FROM   emp;  
    
18. 18
    
19. 19    FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
    
20. 20       DBMS_OUTPUT.PUT_LINE('當前記錄： '
    
21. 21                 ||emp_tab(i).empno||chr(9)
    
22. 22                 ||emp_tab(i).ename||chr(9)
    
23. 23                 ||emp_tab(i).hiredate);  
    
24. 24    END LOOP;
    
25. 25 END;  

復制代碼

說明：使用BULK COLLECT一次即可提取所有行并綁定到記錄變量，這就是所謂的批量綁定。

5.2 在FETCH INTO中使用BULK COLLECT

在游標中可以使用BLUK COLLECT一次取出一個數(shù)據集合，比用游標單條取數(shù)據效率高，尤其是在網絡不大好的情況下。

語法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];
在使用BULK COLLECT子句時，對于集合類型會自動對其進行初始化以及擴展。因此如果使用BULK COLLECT子句操作集合，則無需對集合進行初始化以及擴展。由于BULK COLLECT的批量特性，如果數(shù)據量較大，而集合在此時又自動擴展，為避免過大的數(shù)據集造成性能下降，因此可以使用LIMIT子句來限制一次提取的數(shù)據量。LIMIT子句只允許出現(xiàn)在FETCH操作語句的批量中。

示例：

1. DECLARE  
   
2. 2    CURSOR emp_cur IS  
   
3. 3       SELECT empno, ename, hiredate FROM emp;  
   
4. 4
   
5. 5    TYPE emp_rec_type IS RECORD  
   
6. 6    (  
   
7. 7       empno      emp.empno%TYPE,
   
8. 8       ename      emp.ename%TYPE ,
   
9. 9       hiredate   emp.hiredate%TYPE  
   
10. 10    );  
    
11. 11    -- 定義基于記錄的嵌套表  
    
12. 12    TYPE nested_emp_type IS TABLE OF emp_rec_type;
    
13. 13    -- 聲明集合變量
    
14. 14    emp_tab     nested_emp_type;
    
15. 15    -- 定義了一個變量來作為limit的值  
    
16. 16    v_limit     PLS_INTEGER := 5;
    
17. 17    -- 定義變量來記錄FETCH次數(shù)
    
18. 18    v_counter   PLS_INTEGER := 0;  
    
19. 19 BEGIN  
    
20. 20    OPEN emp_cur;
    
21. 21
    
22. 22    LOOP
    
23. 23       -- fetch時使用了BULK COLLECT子句  
    
24. 24       FETCH emp_cur  
    
25. 25       BULK   COLLECT INTO emp_tab         
    
26. 26       LIMIT v_limit; -- 使用limit子句限制提取數(shù)據量  
    
27. 27
    
28. 28       EXIT WHEN emp_tab.COUNT = 0; -- 注意此時游標退出使用了emp_tab.COUNT，而不是emp_cur%notfound  
    
29. 29       v_counter   := v_counter + 1;  -- 記錄使用LIMIT之后fetch的次數(shù)  
    
30. 30
    
31. 31       FOR i IN emp_tab.FIRST .. emp_tab.LAST  
    
32. 32       LOOP  
    
33. 33          DBMS_OUTPUT.PUT_LINE( '當前記錄： '
    
34. 34                     ||emp_tab(i).empno||CHR(9)
    
35. 35                     ||emp_tab(i).ename||CHR(9)
    
36. 36                     ||emp_tab(i).hiredate);  
    
37. 37       END LOOP;  
    
38. 38    END LOOP;  
    
39. 39
    
40. 40    CLOSE emp_cur;  
    
41. 41
    
42. 42    DBMS_OUTPUT.put_line( '總共獲取次數(shù)為：' || v_counter );  
    
43. 43 END;  

復制代碼

5.3 在RETURNING INTO中使用BULK COLLECT

BULK COLLECT除了與SELECT，F(xiàn)ETCH進行批量綁定之外，還可以與INSERT，DELETE，UPDATE語句結合使用。當與這幾個DML語句結合時，需要使用RETURNING子句來實現(xiàn)批量綁定。

示例：

1. DECLARE  
   
2. 2    TYPE emp_rec_type IS RECORD  
   
3. 3    (  
   
4. 4       empno      emp.empno%TYPE,
   
5. 5       ename      emp.ename%TYPE,
   
6. 6       hiredate   emp.hiredate%TYPE  
   
7. 7    );   
   
8. 8    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
   
9. 9    emp_tab   nested_emp_type;
   
10. 10 BEGIN
    
11. 11    DELETE FROM emp WHERE deptno = 20  
    
12. 12    RETURNING empno, ename, hiredate   -- 使用returning 返回這幾個列  
    
13. 13    BULK   COLLECT INTO emp_tab;       -- 將返回的列的數(shù)據批量插入到集合變量   
    
14. 14
    
15. 15    DBMS_OUTPUT.put_line( '刪除 ' || SQL%ROWCOUNT || ' 行記錄' );  
    
16. 16    COMMIT;  
    
17. 17
    
18. 18    IF emp_tab.COUNT > 0 THEN   -- 當集合變量不為空時，輸出所有被刪除的元素  
    
19. 19       FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
    
20. 20          DBMS_OUTPUT.PUT_LINE('當前記錄：'  
    
21. 21                     || emp_tab( i ).empno || CHR( 9 )  
    
22. 22                     || emp_tab( i ).ename || CHR( 9 )  
    
23. 23                     || emp_tab( i ).hiredate  
    
24. 24                     || ' 已被刪除' );  
    
25. 25       END LOOP;  
    
26. 26    END IF;  
    
27. 27 END;

復制代碼

6. BULK COLLECT的注意事項

BULK COLLECT INTO 的目標對象必須是集合類型。
只能在服務器端的程序中使用BULK COLLECT，如果在客戶端使用，就會產生一個不支持這個特性的錯誤。
不能對使用字符串類型作鍵的關聯(lián)數(shù)組使用BULK COLLECT子句。
復合目標(如對象類型)不能在RETURNING INTO子句中使用。
如果有多個隱式的數(shù)據類型轉換的情況存在，多重復合目標就不能在BULK COLLECT INTO子句中使用。
如果有一個隱式的數(shù)據類型轉換，復合目標的集合(如對象類型集合)就不能用于BULK COLLECTINTO子句中。
7. FORALL與BULK COLLECT綜合運用

FORALL與BULK COLLECT是實現(xiàn)批量SQL的兩個重要方式，我們可以將其結合使用以提高性能。

示例：

1. -- 創(chuàng)建表tb_emp
   
2. 2 CREATE TABLE tb_emp AS  
   
3. 3    SELECT empno, ename, hiredate  
   
4. 4    FROM   emp  
   
5. 5    WHERE  1 = 0;  
   
6. 6
   
7. 7 DECLARE
   
8. 8    -- 聲明游標
   
9. 9    CURSOR emp_cur IS
   
10. 10       SELECT empno, ename, hiredate FROM emp;
    
11. 11    -- 基于游標的嵌套表類型
    
12. 12    TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;
    
13. 13    -- 聲明變量
    
14. 14    emp_tab   nested_emp_type;
    
15. 15 BEGIN  
    
16. 16    SELECT empno, ename, hiredate  
    
17. 17    BULK   COLLECT INTO emp_tab
    
18. 18    FROM   emp  
    
19. 19    WHERE  sal > 1000;
    
20. 20
    
21. 21    -- 使用FORALL語句將變量中的數(shù)據插入到表tb_emp  
    
22. 22    FORALL i IN 1 .. emp_tab.COUNT                     
    
23. 23       INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  
    
24. 24       VALUES emp_tab( i );  
    
25. 25
    
26. 26    COMMIT;  
    
27. 27    DBMS_OUTPUT.put_line('總共向 tb_emp 表中插入記錄數(shù)： ' || emp_tab.COUNT);  
    
28. 28 END;

復制代碼

1. 什么是事務

在數(shù)據庫中事務是工作的邏輯單元，一個事務是由一個或多個完成一組的相關行為的SQL語句組成，通過事務機制確保這一組SQL語句所作的操作要么都成功執(zhí)行，完成整個工作單元操作，要么一個也不執(zhí)行。

如：網上轉帳就是典型的要用事務來處理，用以保證數(shù)據的一致性。

2. 事務特性

SQL92標準定義了數(shù)據庫事務的四個特點：（面試時可能會問的）

原子性(Atomicity)：一個事務里面所有包含的SQL語句是一個執(zhí)行整體，不可分割，要么都做，要么都不做。
一致性(Consistency)：事務開始時，數(shù)據庫中的數(shù)據是一致的，事務結束時，數(shù)據庫的數(shù)據也應該是一致的。
隔離性(Isolation)：是指數(shù)據庫允許多個并發(fā)事務同時對其中的數(shù)據進行讀寫和修改的能力，隔離性可以防止事務的并發(fā)執(zhí)行時，由于他們的操作命令交叉執(zhí)行而導致的數(shù)據不一致狀態(tài)。
持久性 (Durability) : 是指當事務結束后，它對數(shù)據庫中的影響是永久的，即便系統(tǒng)遇到故障的情況下，數(shù)據也不會丟失。
一組SQL語句操作要成為事務，數(shù)據庫管理系統(tǒng)必須保證這組操作的原子性（Atomicity）、一致性（consistency）、隔離性（Isolation）和持久性（Durability），這就是ACID特性。

renxiao2003

總結得不錯。