PL/SQL 集合相關(guān)功能探討
2024-07-21 02:34:41
供稿：網(wǎng)友
Oracle PL/SQL變得更快、更易于使用，特性集也更加豐富了。Oracle數(shù)據(jù)庫10g通過一系列有益的改進，繼續(xù)保持了PL/SQL在速度、簡單易用性和特性擴展方面的傳統(tǒng)優(yōu)勢，這些改進包括：
大大提高了執(zhí)行速度,這要歸功于透明的性能改進，其中包括一個新的優(yōu)化編譯器、更優(yōu)的集成化本地編譯功能，以及幫助解決數(shù)字運算應(yīng)用程序問題的新的數(shù)據(jù)類型。
FORALL語句更加靈活、更加有用。例如：FORALL現(xiàn)在支持非連續(xù)索引。
正則表達(dá)式以三個新函數(shù)（REGEXP_INSTR、REGEXP_REPLACE和REGEXP_SUBSTR）和用于比較的REGEXP_LIKE運算符的形式用于PL/SQL語言中。（要獲得更多信息，參見本期雜志中Jonathan Gennick撰寫的《一流的表達(dá)式》一文。）
集合得到了改進，包括比較集合是否相同、支持對嵌套表進行集合運算等。
Oracle數(shù)據(jù)庫10g使PL/SQL繼續(xù)保持其作為Oracle數(shù)據(jù)庫的最高效、最富生產(chǎn)力的編程語言的地位。其在性能方面的大大提升，以及對IEEE算法和正則表達(dá)式的支持，如今完全開啟了將PL/SQL作為首選語言的新的功能領(lǐng)域。
作為介紹Oracle數(shù)據(jù)庫10g中的PL/SQL系列文章的第一篇，本文將對Oracle數(shù)據(jù)庫10g中與集合相關(guān)的一些改進進行探討。
比較集合
20世紀(jì)90年代末期，當(dāng)我首次對開發(fā)人員進行PL/SQL培訓(xùn)的時候，很少有人使用（甚至知道）包（package），而現(xiàn)在，很多人（包括我自己在內(nèi)）都認(rèn)為它是任何設(shè)計精良的PL/SQL應(yīng)用程序的基礎(chǔ)。如今，打包軟件得到了廣泛使用。 目前，非常重要但未得到充分利用的PL/SQL特性的前沿領(lǐng)域似乎就是集合的使用了。
集合是Oracle中的數(shù)組，集合是一維列表。早在Oracle7中，就已經(jīng)用到了集合（那時，稱作“PL/SQL表”），但其功能及性能卻很有限。不過，Oracle后來的各個版本都對集合進行了改進。在Oracle數(shù)據(jù)庫10g中，這些數(shù)據(jù)結(jié)構(gòu)對于幾乎所有的復(fù)雜PL/SQL應(yīng)用程序項目來說都是強有力的、快速的和必不可少的。
Oracle數(shù)據(jù)庫10g對集合的一個要害性改進是能夠比較兩個集合內(nèi)容的相同之處（和不同之處）。在Oracle數(shù)據(jù)庫10g之前，你也可以對兩個集合進行比較，但必須為此編寫一個函數(shù)。編寫這樣的程序時需要考慮數(shù)個復(fù)雜因素，包括：
必須為正在使用的各個集合類型分別編寫程序。即使兩個集合中的數(shù)據(jù)類型相同，但假如它們不是基于完全相同的類型定義的，你都需要使用不同的函數(shù)來進行比較。
必須對表的內(nèi)容進行逐行比較，這就意味著必須進行“全集合掃描”。完成這一任務(wù)的代碼不是很復(fù)雜。但是，這一代碼冗長乏味，易于出錯，非凡是比較諸如記錄、對象或者xmlType等復(fù)雜數(shù)據(jù)類型的集合時更是如此。
你必須決定如何處理NULL。假如兩行都包含NULL，那么它們相同嗎？Oracle認(rèn)為：“它們既不是相同，也不是不相同，”但是，你的判定可能會與此不同，你必須編寫代碼來處理這個問題。
這種復(fù)雜性導(dǎo)致你不愿意經(jīng)常編寫這類程序，你甚至?xí)趹?yīng)用程序中回避編寫這類程序。
在Oracle數(shù)據(jù)庫10g中，collcompare.sql文件包含了這類程序的一個示例，該程序是為基于employee表的記錄集合而編寫的。
假設(shè)我在collcompare.sql腳本中安裝了emp_coll_pkg.equal函數(shù)。我可以按照如下方式來使用它：
DECLARE
dbas emp_coll_pkg.employee_tt;
developers emp_coll_pkg.employee_tt;
BEGIN
populate_lists (dbas, developers);
IF emp_coll_pkg.equal (dbas, developers)
THEN
DBMS_OUTPUT.PUT_LINE (
'Likely a very small IT organization!');
END IF;
END;
這段代碼簡單明了、可讀性好。（你不用再經(jīng)歷編寫此類函數(shù)的痛苦過程多好啊！）為了讓你工作更輕松，Oracle數(shù)據(jù)庫10g現(xiàn)在答應(yīng)你對兩個嵌套表進行“原始”比較（native compare）了。換句話說，你不必再編寫任何特定于集合的比較邏輯，而可以直接進行比較，如下所示：
DECLARE
dbas emp_coll_pkg.employee_tt;
developers emp_coll_pkg.employee_tt;
BEGIN
populate_lists (dbas, developers);
IF dbas = developers
THEN
DBMS_OUTPUT.PUT_LINE (
'Likely a very small IT organization!');
END IF;
END;
在這種情況下，集合比較僅適用于嵌套表。
換句話說，你還不能直接比較兩個關(guān)聯(lián)數(shù)組（過去稱作"索引表"）或者變長數(shù)組的內(nèi)容。希望Oracle數(shù)據(jù)庫的下一版本會增加關(guān)聯(lián)數(shù)組和變長數(shù)組的比較功能。
集合理論與Multiset Union運算
SQL語言很久以前就提供了將集合運算（UNION、INTERSECT和MINUS）用于查詢結(jié)果集的功能。如今在Oracle數(shù)據(jù)庫10g中，你可以對PL/SQL程序中的嵌套表（且僅限于嵌套表）和在關(guān)系表中聲明為列的嵌套表使用上述功能強大的高級運算符。
現(xiàn)在我們從UNION開始，看看這樣做所需的一些語法。
首先，我創(chuàng)建一個模式級別的嵌套表類型：
CREATE OR REPLACE TYPE strings_nt
IS TABLE OF VARCHAR2(100);
/
然后，我定義一個包，在該包中，我創(chuàng)建并填充兩個此種類型的嵌套表，每個嵌套表都包含一些我和我父親最喜歡的東西：
CREATE OR REPLACE PACKAGE favorites_pkg
IS
my_favorites strings_nt
:= strings_nt ('CHOCOLATE'
, 'BRUSSEL SPROUTS'
, 'SPIDER ROLL'
);
dad_favorites strings_nt
:= strings_nt ('PICKLED HERRING
, 'POTATOES'
, 'PASTRAMI'
, 'CHOCOLATE'
);
PROCEDURE show_favorites (
title_in IN VARCHAR2
, favs_in IN strings_nt
);
END;
/
在該包中，我還創(chuàng)建了一個用于顯示strings_nt 嵌套表內(nèi)容的過程。下面很快就會用到它。
通過在任意程序外的包中定義這些集合，這些集合在我的對話期間會一直保持不變（保持其狀態(tài)和值），直到我將更改或刪除它們?yōu)橹埂＿@就是說，現(xiàn)在我可以在包外編寫程序來對這些集合的內(nèi)容進行操作了。
注重，出于介紹集合功能的目的，我對該包進行了簡化。在生產(chǎn)應(yīng)用程序中，你應(yīng)該時刻注重“隱藏”包主體中的包數(shù)據(jù)（如同這些集合一樣），然后提供過程和函數(shù)來治理這些數(shù)據(jù)。
例如，假設(shè)我想把這兩個集合合并成一個“我們的最愛”的集合。在Oracle數(shù)據(jù)庫10g出現(xiàn)以前，我必須編寫一個將一個集合的內(nèi)容轉(zhuǎn)移到另一個集合的循環(huán)。而現(xiàn)在，我可以依靠MULTISET UNION運算符來實現(xiàn)這一點了，如下所示：
DECLARE
our_favorites
strings_nt := strings_nt ();
BEGIN
our_favorites :=
favorites_pkg.my_favorites
MULTISET UNION
favorites_pkg.dad_favorites;
favorites_pkg.show_favorites (
'ME then DAD', our_favorites);
END;
/
此腳本的輸出結(jié)果為：
ME then DAD
1 = CHOCOLATE
2 = BRUSSEL SPROUTS
3 = SPIDER ROLL
4 = PICKLED HERRING
5 = POTATOES
6 = PASTRAMI
7 = CHOCOLATE
可以看出，兩個嵌套表的值被合并到一起了。而且馬上就可以看到，MULTISET UNION運算符不同于SQL UNION運算符（實際上，與SQL的UNION ALL運算符完全相同）。對兩個SELECT結(jié)果集進行UNION運算時，SQL引擎會自動生成一個惟一的有序結(jié)果集。換句話說，假如我的兩個嵌套表都是查詢，那么UNION將生成這樣的結(jié)果集：
BRUSSEL SPROUTS
CHOCOLATE
PASTRAMI
PICKLED HERRING
POTATOES
SPIDER ROLL
這些數(shù)據(jù)是按照字母順序排列的，且CHOCOLATE僅出現(xiàn)一次。為什么會產(chǎn)生不同的結(jié)果呢？因為嵌套表是一種多集合（multiset），mathworld.wolfram.com/Multiset.Html 對其做了如下定義：
“一種類似于集合的對象，在其內(nèi)部，順序沒有意義，而多重性卻具有明確的意義；因此，多集合{1, 2, 3}和{2, 1, 3}是等同的，而{1, 1, 2, 3}和{1, 2, 3}卻是不同的。”
Oracle文件中說，嵌套表和變長數(shù)組的區(qū)別在于嵌套表列中存儲的數(shù)據(jù)不保存其順序，而在變長數(shù)組中存儲的數(shù)據(jù)保存其順序。在Oracle數(shù)據(jù)庫10g之前，這種區(qū)別在PL/SQL中沒多大意義。現(xiàn)在，有了集合運算符之后，多集合（或嵌套表）的特性便顯得極為重要了。
為了更好地理解嵌套表數(shù)據(jù)沒有順序，MULTISET UNION也不會對結(jié)果嵌套表應(yīng)用順序這一特性，請我們來看看面的這個程序塊：
DECLARE
our_favorites
strings_nt := strings_nt ();
BEGIN
our_favorites :=
favorites_pkg.dad_favorites
MULTISET UNION
favorites_pkg.my_favorites;
favorites_pkg.show_favorites (
'DAD then ME', our_favorites);
END;
/
與前面的程序塊惟一不同的是，我改變了MULTISET UNION運算中嵌套表的順序。
結(jié)果就變?yōu)椋?br />
DAD then ME
1 = PICKLED HERRING
2 = POTATOES
3 = PASTRAMI
4 = CHOCOLATE
5 = CHOCOLATE
6 = BRUSSEL SPROUTS
7 = SPIDER ROLL
假如你不希望合并之后的嵌套表中出現(xiàn)重復(fù)項，那么可
