Oracle10g中新的多重集运算符解析_数据库

Oracle10g中新的多重集运算符解析

发表于：2007-06-20来源：作者：点击数：标签：

Oracle 10g 中针对 SQL Server 2003 新添加了一系列支持 Advanced Multiset 的运算符，他们可以用于对 Oracle 数据库中的嵌套表进行处理。这个SQL 新语法具有强大的功能，并弥补了 Oracle 8i中对于嵌套表的不足之处。但是用户手册中给出的相关例子非常复杂

Oracle 10g 中针对 SQL Server 2003 新添加了一系列支持 Advanced Multiset 的运算符，他们可以用于对 Oracle 数据库中的嵌套表进行处理。这个SQL 新语法具有强大的功能，并弥补了 Oracle 8i中对于嵌套表的不足之处。但是用户手册中给出的相关例子非常复杂，很难了解到这些新函数的使用方法。

下面举个非常简单的例子来说明一下，假设我们要使用一个记录了整型数据的嵌套表。在创建嵌套表时，首先必须为将要创建的表指定一个表名，这个表将用来存放嵌套表的数据。

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;

然后往表中插入一些数列，这些数列分别为从1到5的数字，以及一些奇数数字和偶数数字。然后对其进行查询：

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 可以实现查询中查询出不包含于另一个嵌套表的所有的值。例如在下面这个例子中，查询语句可以查出在嵌套表中除了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 相反，它可以返回在两个嵌套表中相同的值。那么，如果使用一个嵌套表，表中只有一个数字“1”，那么如果在原来的嵌套表中没有“1”则返回的结果为一个空集，但是如原来的嵌套表中确实存在“1”，那么返回的结果就是“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 返回的是这两个嵌套表中所有的值，并将这些值结合到一个嵌套表中。如下例：

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 运算符后，在最后的结果中有两项出现了重复的值。如果你想只保留嵌套表中不重复的值，那么你只需要向下面这个例子中一样，在语句中加上关键字DISTINCT 就可以除去重复的值。

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)

对于每个嵌套表的多重集运算符，都只会从每个集合中选择一个值来进行合并或者排除。也就是说在两个嵌套表中如果出现重复的值，那么对于重复的值只有一个会被除去。例如，num_tab_typ(1,1,2,3) MULTISET EXCEPT num_tab_typ(1,2,3) 的运行结果将会是num_tab_typ(1).

在 Oracle 10g 中，你可以比较两个嵌套表是否相等。只要这两个表中的元素个数相等并且值也相等，那么这两个嵌套表就是相等的，而与这两个表中元素的顺序无关。

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 函数来得到某个嵌套表中元素的个数。

select cardinality(num) from num_tab;
CARDINALITY(NUM)
---------------
5
3
3

数据库认为不存在重复值的嵌套表是一个SET。事实上，存在函数SET ，它可以通过删除嵌套表中重复的数据从而将一个嵌套表转化成一个SET。同样还有一个新的 SQL 子句——IS A SET，在嵌套表中不存在重复值的时候返回真值。

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)

现在你也可以使用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)

最后，还有一个非常有趣的函数——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_tabwhere 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 函数的返回结果，最后的结果必须满足其包含元素的个数等于某个指定的值。还是以上面的那个例子为例，下面有一段代码，功能是返回所有只包含了两个元素的子集。

select cast(powermultiset_by_cardinality(num,2) 
as num_tab_tab_typ) numfrom num_tabwhere 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))

原文转自：http://www.ltesting.net

软件测试 > 测试开发技术 > 软件测试开发语言 > 数据库 >