oracle forall
FORALL語(yǔ)句的一個(gè)關(guān)鍵性改進(jìn)，它可大大簡(jiǎn)化代碼，并且對(duì)于那些要在PL/SQL程序中更新很多行數(shù)據(jù)的程序來(lái)說(shuō)，它可顯著提高其性能。
1:
用FORALL來(lái)增強(qiáng)DML的處理能力
Oracle為Oracle8i中的PL/SQL引入了兩個(gè)新的數(shù)據(jù)操縱語(yǔ)言（DML）語(yǔ)句：BULK COLLECT和FORALL。這兩個(gè)語(yǔ)句在PL/SQL內(nèi)部進(jìn)行一種數(shù)組處理
；BULK COLLECT提供對(duì)數(shù)據(jù)的高速檢索，F(xiàn)ORALL可大大改進(jìn)INSERT、UPDATE和DELETE操作的性能。Oracle數(shù)據(jù)庫(kù)使用這些語(yǔ)句大大減少了
PL/SQL與SQL語(yǔ)句執(zhí)行引擎的環(huán)境切換次數(shù)，從而使其性能有了顯著提高。
使用BULK COLLECT，你可以將多個(gè)行引入一個(gè)或多個(gè)集合中，而不是單獨(dú)變量或記錄中。下面這個(gè)BULK COLLECT的實(shí)例是將標(biāo)題中包含
有"PL/SQL"的所有書(shū)籍檢索出來(lái)并置于記錄的一個(gè)關(guān)聯(lián)數(shù)組中，它們都位于通向該數(shù)據(jù)庫(kù)的單一通道中。
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;
類(lèi)似地，F(xiàn)ORALL將數(shù)據(jù)從一個(gè)PL/SQL集合傳送給指定的使用集合的表。下面的代碼實(shí)例給出一個(gè)過(guò)程，即接收書(shū)籍信息的一個(gè)嵌套表，并將該
集合（綁定數(shù)組）的全部?jī)?nèi)容插入該書(shū)籍表中。注意，這個(gè)例子還利用了Oracle9i的FORALL的增強(qiáng)功能，可以將一條記錄直接插入到表中。
BULK COLLECT和FORALL都非常有用，它們不僅提高了性能，而且還簡(jiǎn)化了為PL/SQL中的SQL操作所編寫(xiě)的代碼。下面的多行FORALL INSERT相當(dāng)
清楚地說(shuō)明了為什么PL/SQL被認(rèn)為是Oracle數(shù)據(jù)庫(kù)的最佳編程語(yǔ)言。
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;
不過(guò)在Oracle數(shù)據(jù)庫(kù)10g之前，以FORAll方式使用集合有一個(gè)重要的限制：該數(shù)據(jù)庫(kù)從IN范圍子句中的第一行到最后一行，依次讀取集合的內(nèi)容
。如果在該范圍內(nèi)遇到一個(gè)未定義的行，Oracle數(shù)據(jù)庫(kù)將引發(fā)ORA-22160異常事件：
ORA-22160: element at index [N] does not exist
對(duì)于FORALL的簡(jiǎn)單應(yīng)用，這一規(guī)則不會(huì)引起任何麻煩。但是，如果想盡可能地充分利用FORALL，那么要求任意FORALL驅(qū)動(dòng)數(shù)組都要依次填充可
能會(huì)增加程序的復(fù)雜性并降低性能。
在Oracle數(shù)據(jù)庫(kù)10g中，PL/SQL現(xiàn)在在FORALL語(yǔ)句中提供了兩個(gè)新子句：INDICES OF與VALUES OF，它們使你能夠仔細(xì)選擇驅(qū)動(dòng)數(shù)組中該由擴(kuò)展
DML語(yǔ)句來(lái)處理的行。
當(dāng)綁定數(shù)組為稀疏數(shù)組或者包含有間隙時(shí)，INDICES OF會(huì)非常有用。該語(yǔ)句的語(yǔ)法結(jié)構(gòu)為：
FORALL indx IN INDICES
OF sparse_collection
INSERT INTO my_table
VALUES sparse_collection (indx);
VALUES OF用于一種不同的情況：綁定數(shù)組可以是稀疏數(shù)組，也可以不是，但我只想使用該數(shù)組中元素的一個(gè)子集。那么我就可以使用VALUES
OF來(lái)指向我希望在DML操作中使用的值。該語(yǔ)句的語(yǔ)法結(jié)構(gòu)為：
FORALL indx IN VALUES OF pointer_array
INSERT INTO my_table
VALUES binding_array (indx);
不用FOR循環(huán)而改用FORALL
假定我需要編寫(xiě)一個(gè)程序，對(duì)合格員工（由comp_analysis.is_eligible函數(shù)確定）加薪，編寫(xiě)關(guān)于不符合加薪條件的員工的報(bào)告并寫(xiě)入
employee_history表。我在一個(gè)非常大的公司工作；我們的員工非常非常多。
對(duì)于一位PL/SQL開(kāi)發(fā)人員來(lái)說(shuō)，這并不是一項(xiàng)十分困難的工作。我甚至不需要使用BULK COLLECT或FORALL就可以完成這項(xiàng)工作，如清單 1所示
，我使用一個(gè)CURSOR FOR循環(huán)和單獨(dú)的INSERT及UPDATE語(yǔ)句。這樣的代碼簡(jiǎn)潔明了；不幸地是，我花了10分鐘來(lái)運(yùn)行此代碼，我的"老式"方法
要運(yùn)行30分鐘或更長(zhǎng)時(shí)間。
清單 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;
好在我公司的數(shù)據(jù)庫(kù)升級(jí)到了Oracle9i，而且更幸運(yùn)的是，在最近的Oracle研討會(huì)上（以及Oracle技術(shù)網(wǎng)站提供的非常不錯(cuò)的演示中）我了解
到了批量處理方法。所以我決定使用集合與批量處理方法重新編寫(xiě)程序。寫(xiě)好的程序如清單 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 就會(huì)清楚地認(rèn)識(shí)到：改用集合和批量處理方法將增加代碼量和復(fù)雜性。但是，如果你需要大幅度提升性能，這還是值得
的。下面，我們不看這些代碼，我們來(lái)看一看當(dāng)使用FORALL時(shí)，用什么來(lái)處理CURSOR FOR循環(huán)內(nèi)的條件邏輯。
定義集合類(lèi)型與集合
在清單 2中，聲明段的第一部分（第6行至第11行）定義了幾種不同的集合類(lèi)型，與我將從員工表檢索出的列相對(duì)應(yīng)。我更喜歡基于employee%
ROWTYPE來(lái)聲明一個(gè)集合類(lèi)型，但是FORALL還不支持對(duì)某些記錄集合的操作，在這樣的記錄中，我將引用個(gè)別字段。所以，我還必須為員工ID、
薪金和雇用日期分別聲明其各自的集合。
接下來(lái)為每一列聲明所需的集合（第13行至第21行）。首先定義與所查詢(xún)列相對(duì)應(yīng)的集合（第13行至第15行）：
employee_ids employee_aat;
salaries salary_aat;
hire_dates hire_date_aat;
然后我需要一個(gè)新的集合，用于存放已被批準(zhǔn)加薪的員工的ID（第17行）：
approved_employee_ids employee_aat;
最后，我再為每一列聲明一個(gè)集合（第19行至第21行），用于記錄沒(méi)有加薪資格的員工：
denied_employee_ids employee_aat;
denied_salaries salary_aat;
denied_hire_dates hire_date_aat;
深入了解代碼
數(shù)據(jù)結(jié)構(gòu)確定后，我們現(xiàn)在跳過(guò)該程序的執(zhí)行部分（第72行至第75行），了解如何使用這些集合來(lái)加速進(jìn)程。
retrieve_employee_info;
partition_by_eligibility;
add_to_history;
give_the_raise;
我編寫(xiě)此程序使用了逐步細(xì)化法（也被稱(chēng)為"自頂向下設(shè)計(jì)"）。所以執(zhí)行部分不是很長(zhǎng)，也不難理解，只有四行，按名稱(chēng)對(duì)過(guò)程中的每一步進(jìn)
行了描述。首先檢索員工信息（指定部門(mén)的所有員工）。然后進(jìn)行劃分，將要加薪和不予加薪的員工區(qū)分出來(lái)。完成之后，我就可以將那些不
予加薪的員工添加至員工歷史表中，對(duì)其他員工進(jìn)行加薪。
以這種方式編寫(xiě)代碼使最終結(jié)果的可讀性大大增強(qiáng)。因而我可以深入到該程序中對(duì)我有意義的任何部分。
有了已聲明的集合，我現(xiàn)在就可以使用BULK COLLECT來(lái)檢索員工信息（第23行至第30行）。這一部分有效地替代了CURSOR FOR循環(huán)。至此，數(shù)
據(jù)被加載到集合中。
劃分邏輯（第32行至第46行）要求對(duì)剛剛填充的集合中的每一行進(jìn)行檢查，看其是否符合加薪條件。如果符合，我就將該員工ID從查詢(xún)填充的
集合復(fù)制到符合條件的員工的集合。如果不符合，則復(fù)制該員工ID、薪金和雇用日期，因?yàn)檫@些都需要插入到employee_history表中。
初始數(shù)據(jù)現(xiàn)在已被分為兩個(gè)集合，可以將其分別用作兩個(gè)不同的FORALL語(yǔ)句（分別從第51行和第66行開(kāi)始）的驅(qū)動(dòng)器。我將不合格員工的集合
中的數(shù)據(jù)批量插入到employee_history（add_to_history）表中，并通過(guò)give_the_raise過(guò)程，在employee表中批量更新合格員工的信息。
最后再仔細(xì)地看一看add_to_history（第48行至第61行），以此來(lái)結(jié)束對(duì)這個(gè)重新編寫(xiě)的程序的分析。FORALL語(yǔ)句（第51行）包含一個(gè)IN子句
，它指定了要用于批量INSERT的行號(hào)范圍。在對(duì)程序進(jìn)行第二次重寫(xiě)的說(shuō)明中，我將把用于定義范圍的集合稱(chēng)為"驅(qū)動(dòng)集合"。但在
add_to_history的這一版本中，我簡(jiǎn)單地假定： 使用在denied_employee_ids中定義的所有行。在INSERT自身內(nèi)部，關(guān)于不合格員工的三個(gè)集
合都會(huì)被用到；我將把這些集合稱(chēng)為"數(shù)據(jù)集合"?？梢钥吹?，驅(qū)動(dòng)集合與數(shù)據(jù)集合無(wú)需匹配。在學(xué)習(xí)Oracle數(shù)據(jù)庫(kù)10g的新特性時(shí)，這是一個(gè)關(guān)
鍵點(diǎn)。
結(jié)果，清單 2 的行數(shù)大約是清單 1行數(shù)的2倍，但是清單 2 中的代碼會(huì)在要求的時(shí)間內(nèi)運(yùn)行。在使用Oracle數(shù)據(jù)庫(kù)10g之前，在這種情況下，
我只會(huì)對(duì)能夠在這一時(shí)間內(nèi)運(yùn)行代碼并開(kāi)始下一個(gè)任務(wù)這一點(diǎn)感到高興。
不過(guò)，有了Oracle數(shù)據(jù)庫(kù)10g中最新版的PL/SQL，現(xiàn)在我就可以在性能、可讀性和代碼量方面作出更多的改進(jìn)。
將VALUES OF用于此過(guò)程
在Oracle數(shù)據(jù)庫(kù)10g中，可以指定FORALL語(yǔ)句使用的驅(qū)動(dòng)集合中的行的子集?？梢允褂靡韵聝煞N方法之一來(lái)定義該子集：
將數(shù)據(jù)集合中的行號(hào)與驅(qū)動(dòng)集合中的行號(hào)進(jìn)行匹配。你需要使用INDICES OF子句。
將數(shù)據(jù)集合中的行號(hào)與驅(qū)動(dòng)集合中所定義行中找到的值進(jìn)行匹配。這需要使用VALUES OF子句。
在對(duì)give_raises_in_department進(jìn)行第二次和最后一次改寫(xiě)中我將使用VALUES OF子句。清單 3 包含這個(gè)版本的全部代碼。我將略過(guò)這一程序
中與前一版本相同的部分。
從聲明集合開(kāi)始，請(qǐng)注意我不再另外定義集合來(lái)存放合格的和不合格的員工信息，而是在清單 3 （第17行至第21行）中聲明兩個(gè)"引導(dǎo)"集合：
一個(gè)用于符合加薪要求的員工，另一個(gè)用于不符合加薪要求的員工。這兩個(gè)集合的數(shù)據(jù)類(lèi)型都是布爾型；不久將會(huì)看到，這些集合的數(shù)據(jù)類(lèi)型
與FORALL語(yǔ)句毫無(wú)關(guān)系。FORALL語(yǔ)句只關(guān)心定義了哪些行。 在員工表中擁有50 000行信息的give_raises_in_department的三種執(zhí)行方法的占
用時(shí)間 執(zhí)行方法 用時(shí)
CURSOR FOR循環(huán) 00:00:38.01
Oracle數(shù)據(jù)庫(kù)10g之前的批量處理 00:00:06.09
Oracle數(shù)據(jù)庫(kù)10g的批量處理 00:00:02.06
在員工表中擁有100,000行數(shù)據(jù)的give_raises_in_department的三種執(zhí)行方法的占用時(shí)間 執(zhí)行方法 用時(shí)
CURSOR FOR循環(huán) 00:00:58.01
Oracle數(shù)據(jù)庫(kù)10g之前的批量處理 00:00:12.00
Oracle數(shù)據(jù)庫(kù)10g的批量處理 00:00:05.05
表1：處理50,000行和100,000行數(shù)據(jù)的用時(shí)測(cè)試結(jié)果
retrieve_employee_info子程序與前面的相同，但是對(duì)數(shù)據(jù)進(jìn)行劃分的方式完全不同（第32行至第44行）。我沒(méi)有將記錄從一個(gè)集合復(fù)制到另
一個(gè)集合（這個(gè)操作相對(duì)較慢），而只是確定與員工ID集合中的行號(hào)相匹配的相應(yīng)引導(dǎo)集合中的行（通過(guò)為其指定一個(gè)TRUE值）。
現(xiàn)在可以在兩個(gè)不同F(xiàn)ORALL語(yǔ)句（由第49行和第65行開(kāi)始）中，將approved_list和denied_list集合用作驅(qū)動(dòng)集合。
為了插入到employee_history表中，我使用了如下語(yǔ)句：
FORALL indx IN VALUES OF denied_list
為了進(jìn)行更新（給員工進(jìn)行加薪），我使用這一格式：
FORALL indx IN VALUES OF approved_list
在這兩個(gè)DML語(yǔ)句中，數(shù)據(jù)集合是在BULK COLLECT 檢索步驟中填充的最初的集合；沒(méi)有進(jìn)行過(guò)復(fù)制。利用VALUES OF，Oracle數(shù)據(jù)庫(kù)在這些數(shù)據(jù)
集合的行中進(jìn)行篩選，僅使用行號(hào)與驅(qū)動(dòng)集合中行號(hào)相匹配的行
利用本程序中的VALUES OF，可以避免復(fù)制對(duì)全部記錄進(jìn)行復(fù)制，而是用行號(hào)的一個(gè)簡(jiǎn)單列表來(lái)替換它們。對(duì)于大型數(shù)組，進(jìn)行這些復(fù)制的開(kāi)銷(xiāo)
是非常可觀的。為了測(cè)試Oracle數(shù)據(jù)庫(kù)10g的優(yōu)越性，我裝入employee表并對(duì)50,000行和100,000行的數(shù)據(jù)運(yùn)行測(cè)試。為了模擬更多的現(xiàn)實(shí)情況
，我將Oracle數(shù)據(jù)庫(kù)10g之前的批量處理的執(zhí)行方法作了修改以進(jìn)行集合內(nèi)容的多次復(fù)制。然后我使用SQL*Plus SET TIMING ON來(lái)顯示運(yùn)行各個(gè)
不同的執(zhí)行方法所用的時(shí)間。表 1 給出了結(jié)果。
從這些時(shí)間測(cè)定得到的結(jié)論非常清楚：由單個(gè)DML語(yǔ)句變?yōu)榕刻幚韺⒋蠓s短耗用時(shí)間，數(shù)據(jù)為50,000行時(shí)的用時(shí)由38秒減為6秒，數(shù)據(jù)為
100,000行時(shí)的用時(shí)由58秒減為12秒。而且，通過(guò)使用VALUES OF來(lái)避免復(fù)制數(shù)據(jù)，我可以將用時(shí)縮短一半左右。
即使沒(méi)有性能上的改進(jìn)，VALUES OF及其同類(lèi)子句--INDICES OF也提高了PL/SQL語(yǔ)言的靈活性，使開(kāi)發(fā)人員能夠更輕松地編寫(xiě)出更直觀和更容易
維護(hù)的代碼。
在產(chǎn)品壽命這一點(diǎn)上，PL/SQL是一種成熟且功能強(qiáng)大的語(yǔ)言。因而，其很多新特性都是逐漸增加和改進(jìn)而成的。不過(guò)，這些新特性還是使應(yīng)用
程序的性能和開(kāi)發(fā)人員的開(kāi)發(fā)效率有了重大改變。VALUES OF就是這種特性的一個(gè)很好的例子。
|
