深入了解Oracle 10g新的多重集運算符

　　Oracle g 中針對 SQL Server 新添加了一系列支持 Advanced Multiset 的運算符他們可以用於對 Oracle 數據庫中的嵌套表進行處理這個SQL 新語法具有強大的功能並彌補了 Oracle i中對於嵌套表的不足之處但是用戶手冊中給出的相關例子非常復雜很難了解到這些新函數的使用方法
　　
　　下面舉個非常簡單的例子來說明一下假設我們要使用一個記錄了整型數據的嵌套表在創建嵌套表時首先必須為將要創建的表指定一個表名這個表將用來存放嵌套表的數據
　　
　　create or replace type num_tab_typ as table of number;
　　/
　　create table num_tab (num num_tab_typ)
　　nested table num store as num_tab_store;
　　
　　然後往表中插入一些數列這些數列分別為從到的數字以及一些奇數數字和偶數數字然後對其進行查詢
　　
　　insert into num_tab values (num_tab_typ());
　　insert into num_tab values (num_tab_typ());
　　insert into num_tab values (num_tab_typ());
　　select num from num_tab;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　多重集運算符 EXCEPT 可以實現查詢中查詢出不包含於另一個嵌套表的所有的值 例如在下面這個例子中查詢語句可以查出在嵌套表中除了以外其他所有的值
　　
　　select num multiset except num_tab_typ() num from num_tab;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　多重集運算符INTERSECT 則是與 Except 相反它可以返回在兩個嵌套表中相同的值那麼如果使用一個嵌套表表中只有一個數字那麼如果在原來的嵌套表中沒有則返回的結果為一個空集但是如原來的嵌套表中確實存在那麼返回的結果就是如下面的例子所示
　　
　　select num multiset intersect num_tab_typ() num from num_tab
　　
　　NUM
　　
　　NUM_TAB_TYP()
　　NUM_TAB_TYP()
　　NUM_TAB_TYP()
　　
　　多重集運算符UNION 返回的是這兩個嵌套表中所有的值並將這些值結合到一個嵌套表中如下例
　　
　　select num multiset union num_tab_typ() num from num_tab;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　大家可以注意到在嵌入表中的值不一定是唯一的正如上面的例子使用 UNION 運算符後在最後的結果中有兩項出現了重復的值如果你想只保留嵌套表中不重復的值那麼你只需要向下面這個例子中一樣在語句中加上關鍵字DISTINCT 就可以除去重復的值
　　
　　select num multiset union distinct num_tab_typ() num from num_tab;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　對於每個嵌套表的多重集運算符都只會從每個集合中選擇一個值來進行合並或者排除也就是說在兩個嵌套表中如果出現重復的值那麼對於重復的值只有一個會被除去例如num_tab_typ() MULTISET EXCEPT num_tab_typ() 的運行結果將會是num_tab_typ()
　　
　　在 Oracle g 中你可以比較兩個嵌套表是否相等只要這兩個表中的元素個數相等並且值也相等那麼這兩個嵌套表就是相等的而與這兩個表中元素的順序無關
　　
　　select num from num_tab where num = num_tab_typ();
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　
　　select num from num_tab where num = num_tab_typ();
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　
　　select num from num_tab where num = num_tab_typ();
　　
　　no rows selected
　　
　　你還可以使用CARDINALITY 函數來得到某個嵌套表中元素的個數
　　
　　select cardinality(num) from num_tab;
　　
　　CARDINALITY(NUM)
　　
　　
　　
　　
　　
　　數據庫認為不存在重復值的嵌套表是一個SET事實上存在函數SET 它可以通過刪除嵌套表中重復的數據從而將一個嵌套表轉化成一個SET同樣還有一個新的 SQL 子句——IS A SET在嵌套表中不存在重復值的時候返回真值
　　
　　select num from num_tab where num is a set;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　select num from num_tab where num multiset union num_tab_typ() is a set;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　
　　現在你也可以使用IS EMPTY 這個新的SQL 子句來判斷嵌套表中的值是否為空
　　
　　select num from num_tab where num multiset intersect
　　num_tab_typ() is empty;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　
　　除此之外還有另外一個新的 SQL 子句是用於判斷某個值是否是一個嵌套表的成員如下例所示
　　
　　select num from num_tab where member of num;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　select num from num_tab where not member of num;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　
　　你也可以通過使用 SUBMULTISET 來判斷一個嵌套表是否是一個多重集的子集
　　
　　select num from num_tab where num_tab_typ() submultiset of num;
　　
　　NUM
　　
　　NUM_TAB_TYP( )
　　NUM_TAB_TYP( )
　　
　　最後還有一個非常有趣的函數——POWERMULTISET 它可以得到某個給定的多重集的所有子集下面給出了關於這個函數的一個例子
　　
　　create or replace type num_tab_tab_typ as table of num_tab_typ;
　　/
　　show errors;
　　
　　select cast(powermultiset(num) as num_tab_tab_typ) from num_tab
　　where num = num_tab_typ();
　　
　　CAST(POWERMULTISET(NUM)ASNUM_TAB_TAB_TYP)
　　
　　NUM_TAB_TAB_TYP(NUM_TAB_TYP() NUM_TAB_TYP() NUM_TAB_TYP( )
　　NUM_TAB_TYP() NUM_TAB_TYP( ) NUM_TAB_TYP( ) NUM_TAB_TYP( ))
　　
　　你還可以使用 POWERMULTISET_BY_CARDINALITY 來約束 POWERMULTISET 函數的返回結果最後的結果必須滿足其包含元素的個數等於某個指定的值還是以上面的那個例子為例下面有一段代碼功能是返回所有只包含了兩個元素的子集
　　
　　select cast(powermultiset_by_cardinality(num) as num_tab_tab_typ) num
　　from num_tab
　　where num = num_tab_typ();
　　
　　NUM
　　
　　NUM_TAB_TAB_TYP(NUM_TAB_TYP( ) NUM_TAB_TYP( ) NUM_TAB_TYP( ))
From:http://tw.wingwit.com/Article/program/Oracle/201311/17950.html