科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道Oracle10g中新的多重集运算符解析

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

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

Oracle 10g 中针对 SQL Server 2003 新添加了一系列支持 Advanced Multiset 的运算符,他们可以用于对 Oracle 数据库中的嵌套表进行处理。这个SQL 新语法具有强大的功能,并弥补了 Oracle 8i中对于嵌套表的不足之处。

作者:Scott Stephens 来源:Scott Stephens  2007年9月11日

关键字: Oracle 10g 多重集 运算符 ORACLE

  • 评论
  • 分享微博
  • 分享邮件

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

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章