Oracle數(shù)據(jù)庫的BULK COLLECT用法之批量增刪改的相關(guān)知識是本文我們主要要介紹的內(nèi)容，FORALL語句的一個關(guān)鍵性改進，它可以大大簡化代碼，并且對于那些要在PL/SQL程序中更新很多行數(shù)據(jù)的程序來說，它可顯著提高其性能。

用FORALL來增強DML的處理能力

Oracle為Oracle8i中的PL/SQL引入了兩個新的數(shù)據(jù)操縱語言（DML）語句：BULK COLLECT和FORALL。這兩個語句在PL/SQL內(nèi)部進行一種數(shù)組處理；BULK COLLECT提供對數(shù)據(jù)的高速檢索，F(xiàn)ORALL可大大改進INSERT、UPDATE和DELETE操作的性能。Oracle數(shù)據(jù)庫使用這些語句大大減少了。

PL/SQL與SQL語句執(zhí)行引擎的環(huán)境切換次數(shù)，從而使其性能有了顯著提高。使用BULK COLLECT，你可以將多個行引入一個或多個集合中，而不是單獨變量或記錄中。下面這個BULK COLLECT的實例是將標題中包含有"PL/SQL"的所有書籍檢索出來并置于記錄的一個關(guān)聯(lián)數(shù)組中，它們都位于通向該數(shù)據(jù)庫的單一通道中。

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; 

類似地，F(xiàn)ORALL將數(shù)據(jù)從一個PL/SQL集合傳送給指定的使用集合的表。下面的代碼實例給出一個過程，即接收書籍信息的一個嵌套表，并將該集合（綁定數(shù)組）的全部內(nèi)容插入該書籍表中。注意，這個例子還利用了Oracle9i的FORALL的增強功能，可以將一條記錄直接插入到表中。BULK COLLECT和FORALL都非常有用，它們不僅提高了性能，而且還簡化了為PL/SQL中的SQL操作所編寫的代碼。下面的多行FORALL INSERT相當清楚地說明了為什么PL/SQL被認為是Oracle數(shù)據(jù)庫的***編程語言。

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數(shù)據(jù)庫10g之前，以FORAll方式使用集合有一個重要的限制：該數(shù)據(jù)庫從IN范圍子句中的***行到***一行，依次讀取集合的內(nèi)容。如果在該范圍內(nèi)遇到一個未定義的行，Oracle數(shù)據(jù)庫將引發(fā)ORA-22160異常事件：ORA-22160: element at index [N] does notexist，對于FORALL的簡單應用，這一規(guī)則不會引起任何麻煩。但是，如果想盡可能地充分利用FORALL，那么要求任意FORALL驅(qū)動數(shù)組都要依次填充可能會增加程序的復雜性并降低性能。

在Oracle數(shù)據(jù)庫10g中，PL/SQL現(xiàn)在在FORALL語句中提供了兩個新子句：INDICES OF與VALUES OF，它們使你能夠仔細選擇驅(qū)動數(shù)組中該由擴展DML語句來處理的行。

當綁定數(shù)組為稀疏數(shù)組或者包含有間隙時，INDICES OF會非常有用。該語句的語法結(jié)構(gòu)為：

FORALL indx IN INDICES  
OF sparse_collection  
INSERT INTO my_table  
VALUES sparse_collection (indx); 

VALUES OF用于一種不同的情況：綁定數(shù)組可以是稀疏數(shù)組，也可以不是，但我只想使用該數(shù)組中元素的一個子集。那么我就可以使用VALUES OF來指向我希望在DML操作中使用的值。該語句的語法結(jié)構(gòu)為：

FORALL indx IN VALUES OF pointer_array  
INSERT INTO my_table  
VALUES binding_array (indx); 

不用FOR循環(huán)而改用FORALL

假定我需要編寫一個程序，對合格員工（由comp_analysis.is_eligible函數(shù)確定）加薪，編寫關(guān)于不符合加薪條件的員工的報告并寫入employee_history表。我在一個非常大的公司工作；我們的員工非常非常多。對于一位PL/SQL開發(fā)人員來說，這并不是一項十分困難的工作。我甚至不需要使用BULKCOLLECT或FORALL就可以完成這項工作，如清單1所示，我使用一個CURSORFOR循環(huán)和單獨的INSERT及UPDATE語句。這樣的代碼簡潔明了；不幸地是，我花了10分鐘來運行此代碼，我的"老式"方法要運行30分鐘或更長時間。

清單 1:

CREATE OR REPLACE PROCEDUREgive_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; 

好在我公司的數(shù)據(jù)庫升級到了Oracle9i，而且更幸運的是，在最近的Oracle研討會上（以及Oracle技術(shù)網(wǎng)站提供的非常不錯的演示中）我了解到了批量處理方法。所以我決定使用集合與批量處理方法重新編寫程序。寫好的程序如清單2所示。

清單 2:

CREATE OR REPLACE PROCEDUREgive_raises_in_department (  
dept_in IN employee.department_id%TYPE  
,newsal IN employee.salary%TYPE  
 )  
IS  
TYPE employee_aat IS TABLE OF employee.employee_id%TYPE  
INDEX BY PLS_INTEGER;  
TYPE salary_aat IS TABLE OF employee.salary%TYPE  
INDEX BY PLS_INTEGER;  
TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE  
INDEX BY PLS_INTEGER;  
employee_ids employee_aat;  
salaries salary_aat;  
hire_dates hire_date_aat;  
approved_employee_ids employee_aat;  
denied_employee_ids employee_aat;  
denied_salaries salary_aat;  
denied_hire_dates hire_date_aat;  
PROCEDURE retrieve_employee_info  
IS  
BEGIN  
SELECT employee_id, salary, hire_date  
BULK COLLECT INTO employee_ids, salaries, hire_dates  
FROM employee  
WHERE department_id = dept_in;  
END;  
PROCEDURE partition_by_eligibility  
IS  
BEGIN  
FOR indx IN employee_ids.FIRST .. employee_ids.LAST  
LOOP  
IF comp_analysis.is_eligible (employee_ids (indx))  
THEN  
approved_employee_ids (indx) :=employee_ids (indx);  
ELSE  
denied_employee_ids (indx) :=employee_ids (indx);  
denied_salaries (indx) :=salaries (indx);  
denied_hire_dates (indx) :=hire_dates (indx);  
END IF;  
END LOOP;  
END;  
PROCEDURE add_to_history  
IS  
BEGIN  
FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST  
INSERT INTO employee_history  
(employee_id  
, salary  
, hire_date, activity  
)  
VALUES (denied_employee_ids(indx)  
, denied_salaries (indx)  
, denied_hire_dates(indx), 'RAISE DENIED'  
 );  
END;  
PROCEDURE give_the_raise  
IS  
BEGIN  
FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST  
UPDATE employee  
SET salary = newsal 
WHERE employee_id =approved_employee_ids (indx);  
END;  
BEGIN  
retrieve_employee_info;  
partition_by_eligibility;  
add_to_history;  
give_the_raise;  
END give_raises_in_department; 

掃一眼清單1 和清單2 就會清楚地認識到：改用集合和批量處理方法將增加代碼量和復雜性。但是，如果你需要大幅度提升性能，這還是值得的。下面，我們不看這些代碼，我們來看一看當使用FORALL時，用什么來處理CURSORFOR循環(huán)內(nèi)的條件邏輯。

定義集合類型與集合

在清單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; 

關(guān)于Oracle數(shù)據(jù)庫的bulk collect用法之批量增刪改的相關(guān)操作就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

1. Oracle學習筆記之DECODE及常用窗口函數(shù)
2. Oracle數(shù)據(jù)庫各類控制語句的使用詳細介紹
3. Oracle數(shù)據(jù)庫日期范圍查詢的兩種實現(xiàn)方式
4. Oracle數(shù)據(jù)庫只讀模式的CACHE BUFFERS CHAINS測試
5. Oracle 10g數(shù)據(jù)庫中UNDO_RETENTION參數(shù)的使用詳解