Oracle 的 FORALL用法（批量增刪改），oracleforall

Oracle 的 FORALL用法（批量增刪改），oracleforall

FORALL語句的一個關鍵性改進，它可大大簡化代碼，並且對於那些要在PL/SQL程式中更新很多行資料的程式來說，它可顯著提高其效能。

1:
用FORALL來增強DML的處理能力
Oracle為Oracle8i中的PL/SQL引入了兩個新的資料操縱語言（DML）語句：BULK COLLECT和FORALL。這兩個語句在PL/SQL內部進行一種數組處理

；BULK COLLECT提供對資料的高速檢索，FORALL可大大改進INSERT、UPDATE和DELETE操作的效能。Oracle資料庫使用這些語句大大減少了

PL/SQL與SQL語句執行引擎的環境切換次數，從而使其效能有了顯著提高。

使用BULK COLLECT，你可以將多個行引入一個或多個集合中，而不是單獨變數或記錄中。下面這個BULK COLLECT的執行個體是將標題中包含

有"PL/SQL"的所有書籍檢索出來共置於記錄的一個關聯陣列中，它們都位於通向該資料庫的單一通道中。
DECLARE
   TYPE books_aat

      IS TABLE OF book%ROWTYPE 
      INDEX BY PLS_INTEGER;
   books books_aat;
BEGIN
   SELECT *
     BULK COLLECT INTO book
     FROM books
    WHERE title LIKE '%PL/SQL%';
   ...
END;

類似地，FORALL將資料從一個PL/SQL集合傳送給指定的使用集合的表。下面的代碼執行個體給出一個過程，即接收書籍資訊的一個巢狀表格，並將該

集合（綁定數組）的全部內容插入該書籍表中。注意，這個例子還利用了Oracle9i的FORALL的增強功能，可以將一條記錄直接插入到表中。

BULK COLLECT和FORALL都非常有用，它們不僅提高了效能，而且還簡化了為PL/SQL中的SQL操作所編寫的代碼。下面的多行FORALL INSERT相當

清楚地說明了為什麼PL/SQL被認為是Oracle資料庫的最佳程式設計語言。
CREATE TYPE books_nt 
IS TABLE OF book%ROWTYPE; 
/
CREATE OR REPLACE PROCEDURE add_books (

books_in IN books_nt)
IS
BEGIN
FORALL book_index 
    IN books_in.FIRST .. books_in.LAST
    INSERT INTO book 
       VALUES books_in(book_index);
   ...
END;

不過在Oracle資料庫10g之前，以FORAll方式使用集合有一個重要的限制：該資料庫從IN範圍子句中的第一行到最後一行，依次讀取集合的內容

。如果在該範圍內遇到一個未定義的行，Oracle資料庫將引發ORA-22160例外狀況事件：

ORA-22160: element at index [N] does not exist

對於FORALL的簡單應用，這一規則不會引起任何麻煩。但是，如果想儘可能地充分利用FORALL，那麼要求任意FORALL驅動數組都要依次填充可

能會增加程式的複雜性並降低效能。

在Oracle資料庫10g中，PL/SQL現在在FORALL語句中提供了兩個新子句：INDICES OF與VALUES OF，它們使你能夠仔細選擇驅動數組中該由擴充

DML語句來處理的行。
當綁定數組為稀疏數組或者包含有間隙時，INDICES OF會非常有用。該語句的文法結構為： 
FORALL indx IN INDICES

OF sparse_collection 
INSERT INTO my_table 
VALUES sparse_collection (indx);

VALUES OF用於一種不同的情況：綁定數組可以是稀疏數組，也可以不是，但我只想使用該數組中元素的一個子集。那麼我就可以使用VALUES

OF來指向我希望在DML操作中使用的值。該語句的文法結構為： 
FORALL indx IN VALUES OF pointer_array 
INSERT INTO my_table 
VALUES binding_array (indx);

不用FOR迴圈而改用FORALL
假定我需要編寫一個程式，對合格員工（由comp_analysis.is_eligible函數確定）加薪，編寫關於不符合加薪條件的員工的報告並寫入

employee_history表。我在一個非常大的公司工作；我們的員工非常非常多。

對於一位PL/SQL開發人員來說，這並不是一項十分困難的工作。我甚至不需要使用BULK COLLECT或FORALL就可以完成這項工作，如清單 1所示

，我使用一個CURSOR FOR迴圈和單獨的INSERT及UPDATE語句。這樣的代碼簡潔明了；不幸地是，我花了10分鐘來運行此代碼，我的"老式"方法

要運行30分鐘或更長時間。 
清單 1:
CREATE OR REPLACE PROCEDURE give_raises_in_department (
        dept_in IN employee.department_id%TYPE
      , newsal IN employee.salary%TYPE
     )
     IS
        CURSOR emp_cur
        IS
           SELECT employee_id, salary, hire_date
             FROM employee
            WHERE department_id = dept_in;
     BEGIN
        FOR emp_rec IN emp_cur
        LOOP
           IF comp_analysis.is_eligible (emp_rec.employee_id)
           THEN
              UPDATE employee
                 SET salary = newsal
               WHERE employee_id = emp_rec.employee_id;
           ELSE
              INSERT INTO employee_history
                          (employee_id, salary
                         , hire_date, activity
                          )
                   VALUES (emp_rec.employee_id, emp_rec.salary
                         , emp_rec.hire_date, 'RAISE DENIED'
                          );
           END IF;
        END LOOP;
     END give_raises_in_department;

好在我公司的資料庫升級到了Oracle9i，而且更幸運的是，在最近的Oracle研討會上（以及Oracle技術網站提供的非常不錯的示範中）我瞭解

到了批量處理方法。所以我決定使用集合與批量處理方法重新編寫程式。寫好的程式如清單 2所示。
清單 2:
1 CREATE OR REPLACE PROCEDURE give_raises_in_department (
2     dept_in IN employee.department_id%TYPE
3   , newsal IN employee.salary%TYPE
4 )
5 IS
6     TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
7        INDEX BY PLS_INTEGER;
8     TYPE salary_aat IS TABLE OF employee.salary%TYPE
9        INDEX BY PLS_INTEGER;
10     TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE
11        INDEX BY PLS_INTEGER;
12
13     employee_ids employee_aat;
14     salaries salary_aat;
15     hire_dates hire_date_aat;
16
17     approved_employee_ids employee_aat;
18
19     denied_employee_ids employee_aat;
20     denied_salaries salary_aat;
21     denied_hire_dates hire_date_aat;
22
23     PROCEDURE retrieve_employee_info
24     IS
25     BEGIN
26        SELECT employee_id, salary, hire_date
27        BULK COLLECT INTO employee_ids, salaries, hire_dates
28          FROM employee
29         WHERE department_id = dept_in;
30     END;
31
32     PROCEDURE partition_by_eligibility
33     IS
34     BEGIN
35        FOR indx IN employee_ids.FIRST .. employee_ids.LAST
36        LOOP
37           IF comp_analysis.is_eligible (employee_ids (indx))
38           THEN
39              approved_employee_ids (indx) := employee_ids (indx);
40           ELSE
41              denied_employee_ids (indx) := employee_ids (indx);
42              denied_salaries (indx) := salaries (indx);
43              denied_hire_dates (indx) := hire_dates (indx);
44           END IF;
45        END LOOP;
46     END;
47
48     PROCEDURE add_to_history
49     IS
50     BEGIN
51        FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
52           INSERT INTO employee_history
53                       (employee_id
54                      , salary
55                      , hire_date, activity
56                       )
57                VALUES (denied_employee_ids (indx)
58                      , denied_salaries (indx)
59                      , denied_hire_dates (indx), 'RAISE DENIED'
60                       );
61     END;
62
63     PROCEDURE give_the_raise
64     IS
65     BEGIN
66        FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
67           UPDATE employee
68              SET salary = newsal
69            WHERE employee_id = approved_employee_ids (indx);
70     END;
71 BEGIN
72     retrieve_employee_info;
73     partition_by_eligibility;
74     add_to_history;
75     give_the_raise;
76 END give_raises_in_department;

掃一眼清單1 和清單2 就會清楚地認識到：改用集合和批量處理方法將增加代碼量和複雜性。但是，如果你需要大幅度提升效能，這還是值得

的。下面，我們不看這些代碼，我們來看一看當使用FORALL時，用什麼來處理CURSOR FOR迴圈內的條件邏輯。

定義集合類型與集合

在清單 2中，聲明段的第一部分（第6行至第11行）定義了幾種不同的集合類型，與我將從員工表檢索出的列相對應。我更喜歡基於employee%

ROWTYPE來聲明一個集合類型，但是FORALL還不支援對某些記錄集合的操作，在這樣的記錄中，我將引用個別欄位。所以，我還必須為員工ID、

薪金和僱用日期分別聲明其各自的集合。

接下來為每一列聲明所需的集合（第13行至第21行）。首先定義與所查詢列相對應的集合（第13行至第15行）：

employee_ids employee_aat;
salaries salary_aat;
hire_dates hire_date_aat;

然後我需要一個新的集合，用於存放已被批准加薪的員工的ID（第17行）：

approved_employee_ids employee_aat;

最後，我再為每一列聲明一個集合（第19行至第21行），用於記錄沒有加薪資格的員工：

denied_employee_ids employee_aat;
denied_salaries salary_aat;
denied_hire_dates hire_date_aat;

深入瞭解代碼

資料結構確定後，我們現在跳過該程式的執行部分（第72行至第75行），瞭解如何使用這些集合來加速進程。

retrieve_employee_info;
partition_by_eligibility;
add_to_history;
give_the_raise;

我編寫此程式使用了逐步細化法（也被稱為"自頂向下設計"）。所以執行部分不是很長，也不難理解，只有四行，按名稱對過程中的每一步進

行了描述。首先檢索員工資訊（指定部門的所有員工）。然後進行劃分，將要加薪和不予加薪的員工區分出來。完成之後，我就可以將那些不

予加薪的員工添加至員工曆史表中，對其他員工進行加薪。

以這種方式編寫代碼使最終結果的可讀性大大增強。因而我可以深入到該程式中對我有意義的任何部分。

有了已聲明的集合，我現在就可以使用BULK COLLECT來檢索員工資訊（第23行至第30行）。這一部分有效地替代了CURSOR FOR迴圈。至此，數

據被載入到集合中。

劃分邏輯（第32行至第46行）要求對剛剛填充的集合中的每一行進行檢查，看其是否符合加薪條件。如果符合，我就將該員工ID從查詢填充的

集合複製到合格員工的集合。如果不符合，則複製該員工ID、薪金和僱用日期，因為這些都需要插入到employee_history表中。

初始資料現在已被分為兩個集合，可以將其分別用作兩個不同的FORALL語句（分別從第51行和第66行開始）的磁碟機。我將不合格員工的集合

中的資料批量插入到employee_history（add_to_history）表中，並通過give_the_raise過程，在employee表中批次更新合格員工的資訊。

最後再仔細地看一看add_to_history（第48行至第61行），以此來結束對這個重新編寫的程式的分析。FORALL語句（第51行）包含一個IN子句

，它指定了要用於批量INSERT的行號範圍。在對程式進行第二次重寫的說明中，我將把用於定義範圍的集合稱為"驅動集合"。但在

add_to_history的這一版本中，我簡單地假定： 使用在denied_employee_ids中定義的所有行。在INSERT自身內部，關於不合格員工的三個集

合都會被用到；我將把這些集合稱為"資料集合"。可以看到，驅動集合與資料集合無需匹配。在學習Oracle資料庫10g的新特性時，這是一個關

鍵點。

結果，清單 2 的行數大約是清單 1行數的2倍，但是清單 2 中的代碼會在要求的時間內運行。在使用Oracle資料庫10g之前，在這種情況下，

我只會對能夠在這一時間內運行代碼並開始下一個任務這一點感到高興。

不過，有了Oracle資料庫10g中最新版的PL/SQL，現在我就可以在效能、可讀性和代碼量方面作出更多的改進。
將VALUES OF用於此過程

在Oracle資料庫10g中，可以指定FORALL語句使用的驅動集合中的行的子集。可以使用以下兩種方法之一來定義該子集：

將資料集合中的行號與驅動集合中的行號進行匹配。你需要使用INDICES OF子句。 
將資料集合中的行號與驅動集合中所定義行中找到的值進行匹配。這需要使用VALUES OF子句。 
在對give_raises_in_department進行第二次和最後一次改寫中我將使用VALUES OF子句。清單 3 包含這個版本的全部代碼。我將略過這一程式

中與前一版本相同的部分。

從聲明集合開始，請注意我不再另外定義集合來存放合格的和不合格的員工資訊，而是在清單 3 （第17行至第21行）中聲明兩個"引導"集合：

一個用於符合加薪要求的員工，另一個用於不符合加薪要求的員工。這兩個集合的資料類型都是布爾型；不久將會看到，這些集合的資料類型

與FORALL語句毫無關係。FORALL語句只關心定義了哪些行。 在員工表中擁有50 000行資訊的give_raises_in_department的三種執行方法的占

用時間 執行方法 用時 
CURSOR FOR迴圈 00:00:38.01 
Oracle資料庫10g之前的批量處理 00:00:06.09 
Oracle資料庫10g的批量處理 00:00:02.06

在員工表中擁有100,000行資料的give_raises_in_department的三種執行方法的佔用時間 執行方法 用時
CURSOR FOR迴圈 00:00:58.01 
Oracle資料庫10g之前的批量處理 00:00:12.00 
Oracle資料庫10g的批量處理 00:00:05.05

表1：處理50,000行和100,000行資料的用時測試結果

retrieve_employee_info子程式與前面的相同，但是對資料進行劃分的方式完全不同（第32行至第44行）。我沒有將記錄從一個集合複製到另

一個集合（這個操作相對較慢），而只是確定與員工ID集合中的行號相匹配的相應引導集合中的行（通過為其指定一個TRUE值）。

現在可以在兩個不同FORALL語句（由第49行和第65行開始）中，將approved_list和denied_list集合用作驅動集合。

為了插入到employee_history表中，我使用了如下語句：

FORALL indx IN VALUES OF denied_list

為了進行更新（給員工進行加薪），我使用這一格式：

FORALL indx IN VALUES OF approved_list

在這兩個DML語句中，資料集合是在BULK COLLECT 檢索步驟中填充的最初的集合；沒有進行過複製。利用VALUES OF，Oracle資料庫在這些資料

集合的行中進行篩選，僅使用行號與驅動集合中行號相匹配的行
利用本程式中的VALUES OF，可以避免複製對全部記錄進行複製，而是用行號的一個簡單列表來替換它們。對於大型數組，進行這些複製的開銷

是非常可觀的。為了測試Oracle資料庫10g的優越性，我裝入employee表並對50,000行和100,000行的資料運行測試。為了類比更多的現實情況

，我將Oracle資料庫10g之前的批量處理的執行方法作了修改以進行集合內容的多次複製。然後我使用SQL*Plus SET TIMING ON來顯示運行各個

不同的執行方法所用的時間。表 1 給出了結果。

從這些時間測定得到的結論非常清楚：由單個DML語句變為批量處理將大幅縮短耗用時間，資料為50,000行時的用時由38秒減為6秒，資料為

100,000行時的用時由58秒減為12秒。而且，通過使用VALUES OF來避免複製資料，我可以將用時縮短一半左右。

即使沒有效能上的改進，VALUES OF及其同類子句--INDICES OF也提高了PL/SQL語言的靈活性，使開發人員能夠更輕鬆地編寫出更直觀和更容易

維護的代碼。

在產品壽命這一點上，PL/SQL是一種成熟且功能強大的語言。因而，其很多新特性都是逐漸增加和改進而成的。不過，這些新特性還是使應用

程式的效能和開發人員的開發效率有了重大改變。VALUES OF就是這種特性的一個很好的例子。