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

ronaldj

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

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

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

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

語法：

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;

復(fù)制代碼

說明：

index_name：一個(gè)無需聲明的標(biāo)識(shí)符，作為集合下標(biāo)使用。

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

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

VALUES OF index_collection_name：把該集合中的值當(dāng)作下標(biāo)，且該集合值的類型只能是 PLS_INTEGER/BINARY_INTEGER。

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

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

2. FORALL的使用

示例所使用表結(jié)構(gòu)：

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

復(fù)制代碼

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

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;

復(fù)制代碼

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;

復(fù)制代碼

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;

復(fù)制代碼

示例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三個(gè)成員
   
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 ;

復(fù)制代碼

示例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;

復(fù)制代碼

3. FORALL注意事項(xiàng)

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

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

BULK COLLECT子句會(huì)批量檢索結(jié)果，即一次性將結(jié)果集綁定到一個(gè)集合變量中，并從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將所得的結(jié)果集一次性綁定到記錄變量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('當(dāng)前記錄： '
    
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;  

復(fù)制代碼

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

5.2 在FETCH INTO中使用BULK COLLECT

在游標(biāo)中可以使用BLUK COLLECT一次取出一個(gè)數(shù)據(jù)集合，比用游標(biāo)單條取數(shù)據(jù)效率高，尤其是在網(wǎng)絡(luò)不大好的情況下。

語法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];
在使用BULK COLLECT子句時(shí)，對(duì)于集合類型會(huì)自動(dòng)對(duì)其進(jìn)行初始化以及擴(kuò)展。因此如果使用BULK COLLECT子句操作集合，則無需對(duì)集合進(jìn)行初始化以及擴(kuò)展。由于BULK COLLECT的批量特性，如果數(shù)據(jù)量較大，而集合在此時(shí)又自動(dòng)擴(kuò)展，為避免過大的數(shù)據(jù)集造成性能下降，因此可以使用LIMIT子句來限制一次提取的數(shù)據(jù)量。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    -- 定義了一個(gè)變量來作為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時(shí)使用了BULK COLLECT子句  
    
24. 24       FETCH emp_cur  
    
25. 25       BULK   COLLECT INTO emp_tab         
    
26. 26       LIMIT v_limit; -- 使用limit子句限制提取數(shù)據(jù)量  
    
27. 27
    
28. 28       EXIT WHEN emp_tab.COUNT = 0; -- 注意此時(shí)游標(biāo)退出使用了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( '當(dāng)前記錄： '
    
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;  

復(fù)制代碼

5.3 在RETURNING INTO中使用BULK COLLECT

BULK COLLECT除了與SELECT，F(xiàn)ETCH進(jìn)行批量綁定之外，還可以與INSERT，DELETE，UPDATE語句結(jié)合使用。當(dāng)與這幾個(gè)DML語句結(jié)合時(shí)，需要使用RETURNING子句來實(shí)現(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 返回這幾個(gè)列  
    
13. 13    BULK   COLLECT INTO emp_tab;       -- 將返回的列的數(shù)據(jù)批量插入到集合變量   
    
14. 14
    
15. 15    DBMS_OUTPUT.put_line( '刪除 ' || SQL%ROWCOUNT || ' 行記錄' );  
    
16. 16    COMMIT;  
    
17. 17
    
18. 18    IF emp_tab.COUNT > 0 THEN   -- 當(dāng)集合變量不為空時(shí)，輸出所有被刪除的元素  
    
19. 19       FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
    
20. 20          DBMS_OUTPUT.PUT_LINE('當(dāng)前記錄：'  
    
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;

復(fù)制代碼

6. BULK COLLECT的注意事項(xiàng)

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

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

示例：

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    -- 聲明游標(biāo)
   
9. 9    CURSOR emp_cur IS
   
10. 10       SELECT empno, ename, hiredate FROM emp;
    
11. 11    -- 基于游標(biāo)的嵌套表類型
    
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ù)據(jù)插入到表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;

復(fù)制代碼

1. 什么是事務(wù)

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

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

2. 事務(wù)特性

SQL92標(biāo)準(zhǔn)定義了數(shù)據(jù)庫事務(wù)的四個(gè)特點(diǎn)：（面試時(shí)可能會(huì)問的）

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

renxiao2003

總結(jié)得不錯(cuò)。