PL/SQL 集合相關功能探討

　　Oracle PL/SQL變得更快更易於使用特性集也更加豐富了Oracle數據庫g通過一系列有益的改進繼續保持了PL/SQL在速度簡單易用性和特性擴展方面的傳統優勢這些改進包括
　　
　　大大提高了執行速度這要歸功於透明的性能改進其中包括一個新的優化編譯器更優的集成化本地編譯功能以及幫助解決數字運算應用程序問題的新的數據類型
　　
　　FORALL語句更加靈活更加有用例如FORALL現在支持非連續索引
　　
　　正則表達式以三個新函數（REGEXP_INSTRREGEXP_REPLACE和REGEXP_SUBSTR）和用於比較的REGEXP_LIKE運算符的形式用於PL/SQL語言中（要獲得更多信息參見本期雜志中Jonathan Gennick撰寫的《一流的表達式》一文）
　　
　　集合得到了改進包括比較集合是否相同支持對嵌套表進行集合運算等
　　Oracle數據庫g使PL/SQL繼續保持其作為Oracle數據庫的最高效最富生產力的編程語言的地位其在性能方面的大大提升以及對IEEE算法和正則表達式的支持如今完全開啟了將PL/SQL作為首選語言的新的功能領域
　　
　　作為介紹Oracle數據庫g中的PL/SQL系列文章的第一篇本文將對Oracle數據庫g中與集合相關的一些改進進行探討
　　
　　比較集合
　　世紀年代末期當我首次對開發人員進行PL/SQL培訓的時候很少有人使用（甚至知道）包（package）而現在很多人（包括我自己在內）都認為它是任何設計精良的PL/SQL應用程序的基礎如今打包軟件得到了廣泛使用 目前非常重要但未得到充分利用的PL/SQL特性的前沿領域似乎就是集合的使用了
　　
　　集合是Oracle中的數組集合是一維列表早在Oracle中就已經用到了集合（那時稱作PL/SQL表）但其功能及性能卻很有限不過Oracle後來的各個版本都對集合進行了改進在Oracle數據庫g中這些數據結構對於幾乎所有的復雜PL/SQL應用程序項目來說都是強有力的快速的和必不可少的
　　
　　Oracle數據庫g對集合的一個關鍵性改進是能夠比較兩個集合內容的相同之處（和不同之處）在Oracle數據庫g之前你也可以對兩個集合進行比較但必須為此編寫一個函數編寫這樣的程序時需要考慮數個復雜因素包括
　　
　　必須為正在使用的各個集合類型分別編寫程序即使兩個集合中的數據類型相同但如果它們不是基於完全相同的類型定義的你都需要使用不同的函數來進行比較
　　
　　必須對表的內容進行逐行比較這就意味著必須進行全集合掃描完成這一任務的代碼不是很復雜但是這一代碼冗長乏味易於出錯特別是比較諸如記錄對象或者XMLType等復雜數據類型的集合時更是如此
　　
　　你必須決定如何處理NULL如果兩行都包含NULL那麼它們相同嗎？Oracle認為它們既不是相同也不是不相同但是你的判斷可能會與此不同你必須編寫代碼來處理這個問題
　　這種復雜性導致你不願意經常編寫這類程序你甚至會在應用程序中回避編寫這類程序
　　
　　在Oracle數據庫g中collcomparesql文件包含了這類程序的一個示例該程序是為基於employee表的記錄集合而編寫的
　　
　　假設我在collcomparesql腳本中安裝了emp_coll_pkgequal函數我可以按照如下方式來使用它
　　
　　DECLARE
　　dbas emp_coll_pkgemployee_tt;
　　developers emp_coll_pkgemployee_tt;
　　
　　BEGIN
　　populate_lists (dbas developers);
　　
　　IF emp_coll_pkgequal (dbas developers)
　　THEN
　　DBMS_OUTPUTPUT_LINE (
　　Likely a very small IT organization!);
　　END IF;
　　END;
　　
　　這段代碼簡單明了可讀性好（你不用再經歷編寫此類函數的痛苦過程多好啊！）為了讓你工作更輕松Oracle數據庫g現在允許你對兩個嵌套表進行原始比較（native compare）了換句話說你不必再編寫任何特定於集合的比較邏輯而可以直接進行比較如下所示
　　
　　DECLARE
　　dbas emp_coll_pkgemployee_tt;
　　developers emp_coll_pkgemployee_tt;
　　BEGIN
　　populate_lists (dbas developers);
　　
　　IF dbas = developers
　　THEN
　　DBMS_OUTPUTPUT_LINE (
　　Likely a very small IT organization!);
　　
　　END IF;
　　END;
　　
　　在這種情況下集合比較僅適用於嵌套表換句話說你還不能直接比較兩個關聯數組（過去稱作索引表）或者變長數組的內容希望Oracle數據庫的下一版本會增加關聯數組和變長數組的比較功能
　　
　　集合理論與Multiset Union運算
　　SQL語言很久以前就提供了將集合運算（UNIONINTERSECT和MINUS）用於查詢結果集的功能如今在Oracle數據庫g中你可以對PL/SQL程序中的嵌套表（且僅限於嵌套表）和在關系表中聲明為列的嵌套表使用上述功能強大的高級運算符
　　
　　現在我們從UNION開始看看這樣做所需的一些語法
　　
　　首先我創建一個模式級別的嵌套表類型
　　
　　CREATE OR REPLACE TYPE strings_nt
　　
　　IS TABLE OF VARCHAR();
　　/
　　
　　然後我定義一個包在該包中我創建並填充兩個此種類型的嵌套表每個嵌套表都包含一些我和我父親最喜歡的東西
　　
　　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　 VARCHAR
　　 favs_in　　IN　 strings_nt
　　);
　　END;
　　/
　　
　　在該包中我還創建了一個用於顯示strings_nt 嵌套表內容的過程下面很快就會用到它
　　
　　通過在任意程序外的包中定義這些集合這些集合在我的對話期間會一直保持不變（保持其狀態和值）直到我將更改或刪除它們為止這就是說現在我可以在包外編寫程序來對這些集合的內容進行操作了
　　
　　注意出於介紹集合功能的目的我對該包進行了簡化在生產應用程序中你應該時刻注意隱藏包主體中的包數據（如同這些集合一樣）然後提供過程和函數來管理這些數據
　　
　　例如假設我想把這兩個集合合並成一個我們的最愛的集合在Oracle數據庫g出現以前我必須編寫一個將一個集合的內容轉移到另一個集合的循環而現在我可以依賴MULTISET UNION運算符來實現這一點了如下所示
　　
　　DECLARE
　　our_favorites
　　strings_nt := strings_nt ();
　　BEGIN
　　our_favorites :=
　　favorites_pkgmy_favorites
　　MULTISET UNION
　　favorites_pkgdad_favorites;
　　
　　favorites_pkgshow_favorites (
　　ME then DAD our_favorites);
　　
　　END;
　　/
　　
　　此腳本的輸出結果為
　　
　　ME then DAD
　　 = CHOCOLATE
　　 = BRUSSEL SPROUTS
　　 = SPIDER ROLL
　　 = PICKLED HERRING
　　 = POTATOES
　　 = PASTRAMI
　　 = CHOCOLATE
　　
　　可以看出兩個嵌套表的值被合並到一起了而且立刻就可以看到MULTISET UNION運算符不同於SQL UNION運算符（實際上與SQL的UNION ALL運算符完全相同）對兩個SELECT結果集進行UNION運算時SQL引擎會自動生成一個惟一的有序結果集換句話說如果我的兩個嵌套表都是查詢那麼UNION將生成這樣的結果集
　　
　　BRUSSEL SPROUTS
　　CHOCOLATE
　　PASTRAMI
　　PICKLED HERRING
　　POTATOES
　　SPIDER ROLL
　　
　　這些數據是按照字母順序排列的且CHOCOLATE僅出現一次為什麼會產生不同的結果呢？因為嵌套表是一種多集合（multiset）m/l 對其做了如下定義
　　
　　一種類似於集合的對象在其內部順序沒有意義而多重性卻具有明確的意義因此多集合{ }和{ }是等同的而{ }和{ }卻是不同的
　　
　　Oracle文件中說嵌套表和變長數組的區別在於嵌套表列中存儲的數據不保存其順序而在變長數組中存儲的數據保存其順序在Oracle數據庫g之前這種區別在PL/SQL中沒多大意義現在有了集合運算符之後多集合（或嵌套表）的特性便顯得極為重要了
　　
　　為了更好地理解嵌套表數據沒有順序MULTISET UNION也不會對結果嵌套表應用順序這一特性請我們來看看面的這個程序塊
　　
　　DECLARE
　　our_favorites
　　strings_nt := strings_nt ();
　　BEGIN
　　our_favorites :=
　　favorites_pkgdad_favorites
　　MULTISET UNION
　　favorites_pkgmy_favorites;
　　favorites_pkgshow_favorites (
　　DAD then ME our_favorites);
　　END;
　　/
　　
　　與前面的程序塊惟一不同的是我改變了MULTISET UNION運算中嵌套表的順序結果就變為
　　
　　DAD then ME
　　 = PICKLED HERRING
　　 = POTATOES
　　
　　 = PASTRAMI
　　 = CHOCOLATE
　　 = CHOCOLATE
　　 = BRUSSEL SPROUTS
　　 = SPIDER ROLL
　　
　　如果你不希望合並之後的嵌套表中出現重復項那麼可
From:http://tw.wingwit.com/Article/program/Oracle/201311/18364.html