Oracle 10g 中針對 SQL Server 2003 新添加了一系列支持 Advanced Multiset 的運算符，他們可以用于對 Oracle 數(shù)據(jù)庫中的嵌套表進行處理。這個SQL 新語法具有強大的功能，并彌補了 Oracle 8i中對于嵌套表的不足之處。 但是用戶手冊中給出的相關(guān)例子非常復(fù)雜，很難了解到這些新函數(shù)的使用方法。
下面舉個非常簡單的例子來說明一下，假設(shè)我們要使用一個記錄了整型數(shù)據(jù)的嵌套表。在創(chuàng)建嵌套表時，首先必須為將要創(chuàng)建的表指定一個表名，這個表將用來存放嵌套表的數(shù)據(jù)。
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;
然后往表中插入一些數(shù)列，這些數(shù)列分別為從1到5的數(shù)字，以及一些奇數(shù)數(shù)字和偶數(shù)數(shù)字。然后對其進行查詢：
insert into num_tab values (num_tab_typ(1,2,3,4,5));
insert into num_tab values (num_tab_typ(1,3,5));
insert into num_tab values (num_tab_typ(2,4,6));
select num from num_tab;
NUM
--------------------------
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
NUM_TAB_TYP(2, 4, 6)
多重集運算符 EXCEPT 可以實現(xiàn)查詢中查詢出不包含于另一個嵌套表的所有的值。 例如在下面這個例子中，查詢語句可以查出在嵌套表中除了1以外其他所有的值：
select num multiset except num_tab_typ(1) num from num_tab;
NUM
--------------------------
NUM_TAB_TYP(2, 3, 4, 5)
NUM_TAB_TYP(3, 5)
NUM_TAB_TYP(2, 4, 6)
多重集運算符INTERSECT 則是與 Except 相反，它可以返回在兩個嵌套表中相同的值。那么，假如使用一個嵌套表，表中只有一個數(shù)字“1”，那么假如在原來的嵌套表中沒有“1”則返回的結(jié)果為一個空集，但是如原來的嵌套表中確實存在“1”，那么返回的結(jié)果就是“1”，如下面的例子所示：
select num multiset intersect num_tab_typ(1) num from num_tab
NUM
--------------------------
NUM_TAB_TYP(1)
NUM_TAB_TYP(1)
NUM_TAB_TYP()
多重集運算符UNION 返回的是這兩個嵌套表中所有的值，并將這些值結(jié)合到一個嵌套表中。如下例：
select num multiset union num_tab_typ(1) num from num_tab;
NUM
--------------------------
NUM_TAB_TYP(1, 2, 3, 4, 5, 1)
NUM_TAB_TYP(1, 3, 5, 1)
NUM_TAB_TYP(2, 4, 6, 1)
大家可以注重到，在嵌入表中的值不一定是唯一的。正如上面的例子，使用 UNION 運算符后，在最后的結(jié)果中有兩項出現(xiàn)了重復(fù)的值。假如你想只保留嵌套表中不重復(fù)的值，那么你只需要向下面這個例子中一樣，在語句中加上要害字DISTINCT 就可以除去重復(fù)的值。
select num multiset union distinct num_tab_typ(1) num from num_tab;
NUM
--------------------------
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
NUM_TAB_TYP(2, 4, 6, 1)
對于每個嵌套表的多重集運算符，都只會從每個集合中選擇一個值來進行合并或者排除。也就是說在兩個嵌套表中假如出現(xiàn)重復(fù)的值，那么對于重復(fù)的值只有一個會被除去。例如，num_tab_typ(1,1,2,3) MULTISET EXCEPT num_tab_typ(1,2,3) 的運行結(jié)果將會是num_tab_typ(1).
在 Oracle 10g 中，你可以比較兩個嵌套表是否相等。只要這兩個表中的元素個數(shù)相等并且值也相等，那么這兩個嵌套表就是相等的，而與這兩個表中元素的順序無關(guān)。
select num from num_tab where num = num_tab_typ(1,3,5);
NUM
--------------------------
NUM_TAB_TYP(1, 3, 5)
select num from num_tab where num = num_tab_typ(1,5,3);
NUM
--------------------------
NUM_TAB_TYP(1, 3, 5)
select num from num_tab where num = num_tab_typ(1,1,3,5);
no rows selected
你還可以使用CARDINALITY 函數(shù)來得到某個嵌套表中元素的個數(shù)。
select cardinality(num) from num_tab;
CARDINALITY(NUM)
----------------
5
3
3
數(shù)據(jù)庫認為不存在重復(fù)值的嵌套表是一個SET。事實上，存在函數(shù)SET ，它可以通過刪除嵌套表中重復(fù)的數(shù)據(jù)從而將一個嵌套表轉(zhuǎn)化成一個SET。同樣還有一個新的 SQL 子句——IS A SET，在嵌套表中不存在重復(fù)值的時候返回真值。
select num from num_tab where num is a set;
NUM
--------------------------
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
NUM_TAB_TYP(2, 4, 6)
select num from num_tab where num multiset union num_tab_typ(1) is a set;
NUM
--------------------------
NUM_TAB_TYP(2, 4, 6)
現(xiàn)在你也可以使用IS EMPTY 這個新的SQL 子句來判定嵌套表中的值是否為空。.
select num from num_tab where num multiset intersect
num_tab_typ(1) is empty;
NUM
--------------------------
NUM_TAB_TYP(2, 4, 6)
除此之外，還有另外一個新的 SQL 子句，是用于判定某個值是否是一個嵌套表的成員。如下例所示：
select num from num_tab where 3 member of num;
NUM
--------------------------
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
select num from num_tab where 3 not member of num;
NUM
--------------------------
NUM_TAB_TYP(2, 4, 6)
你也可以通過使用 SUBMULTISET 來判定一個嵌套表是否是一個多重集的子集。
select num from num_tab where num_tab_typ(1,3,5) submultiset of num;
NUM
--------------------------
NUM_TAB_TYP(1, 2, 3, 4, 5)
NUM_TAB_TYP(1, 3, 5)
最后，還有一個非常有趣的函數(shù)——POWERMULTISET ，它可以得到某個給定的多重集的所有子集。下面給出了關(guān)于這個函數(shù)的一個例子：
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(2,4,6);
CAST(POWERMULTISET(NUM)ASNUM_TAB_TAB_TYP)
--------------------------
NUM_TAB_TAB_TYP(NUM_TAB_TYP(2), NUM_TAB_TYP(4), NUM_TAB_TYP(2, 4),
NUM_TAB_TYP(6), NUM_TAB_TYP(2, 6), NUM_TAB_TYP(4, 6), NUM_TAB_TYP(2, 4, 6))
你還可以使用 POWERMULTISET_BY_CARDINALITY 來約束 POWERMULTISET 函數(shù)的返回結(jié)果，最后的結(jié)果必須滿足其包含元素的個數(shù)等于某個指定的值。還是以上面的那個例子為例，下面有一段代碼，功能是返回所有只包含了兩個元素的子集。
select cast(powermultiset_by_cardinality(num,2) as num_tab_tab_typ) num
from num_tab
where num = num_tab_typ(2,4,6);
NUM
--------------------------
NUM_TAB_TAB_TYP(NUM_TAB_TYP(2, 4), NUM_TAB_TYP(2, 6), NUM_TAB_TYP(4, 6))
