Oracle 11GR2的递归WITH子查询方法
|
下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法，具体内容如下所示： with emp_data(ename,empno,mgr,l) as (select ename,1 lvl from emp where mgr is null union all select emp.ename,emp.empno,emp.mgr,ed.l+1 from emp,emp_data ed where emp.mgr = ed.empno ) SEARCH DEPTH FIRST BY ename SET order_by select l,lpad('*',2*l,'*')||ename nm from emp_data order by order_by / L NM 14 rows selected. 不知道真用起来怎么样，按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样，比如按路径累加，更灵活的剪枝条件， WITH子查询也称为CTE (Common Table Expression)，是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。 WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面，只能够引用一次；而WITH子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。 从版本11GR2开始，ORACLE支持递归的WITH,即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事，其他数据库如DB2,Firebird,Microsoft SQL Server,PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段（主要是CONNECT BY层次查询）作比较。 我们先来看看这个递归WITH子查询的语法： WITH ① query_name ([c_alias [,c_alias]...]) ①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。 subquery部分由两个成员组成：anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来，anchor member 必须写在recursive member前面。 递归成员也是一个SELECT语句，用于定义上下级的关系，它必须包含自身名称(即query_name)，而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接，连接条件表明了上下级的关系。必须注意，在这个query_name中，并不是截止目前为止的所有数据都是可见的，可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级，遍历就会停止；如果你还有其他的递归出口条件，也可以一起写在WHERE中，当WHERE不满足时，遍历就会停止，这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。 这个递归成员就是程序员发挥创造力的地方，以往在CONNECT BY中做不到的事情，比如沿路径求和、求积等运算，现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'来实现。 搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。 下面我们就来看看递归WITH子查询的用法实例。 例1: 先来一个简单例子，从scott/tiger的emp表来查找上下级关系： 传统的CONNECT BY写法： 新的递归WITH写法: 查询结果： 14 rows selected. 从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。 例2: 构造等差数列： CONNECT BY写法： 这是一个非常特殊的用法，因为没有上下级关系，只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行（比如FROM DUAL,比如从一个聚合后的子查询），在多行的集合上运行比较难以控制，头脑必须很清醒。 （以下ROWNUM全部可以改成 LEVEL，效果一样）： 结果： N N2 DT MON 10 rows selected. 这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站()发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取ROWNUM的方法： 下面尝试用递归WITH的写法： 一切都按规矩来，竟然还是出错了： 改为字符串型看看： 我很惊奇地看到这个结果： N N2 DT MON 10 rows selected. 这是ORACEL 11.2.0.1.0版本的BUG，后续版本应该会改正。 没办法，只好想其他招数绕过去: 这下子对了： N N2 DT MON 10 rows selected. 看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。 从一个单行结果集CONNECT BY的例子: 结果： RN CNT 14 rows selected. 递归WITH写法： 结果同上（略）。 例3: 独立事件的排列组合：一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次，每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少？ 传统的CONNECT BY写法： 结果： PROB （编辑：鄂州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
