Oracle 11GR2的递归WITH子查询方法_oracle_

下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法，具体内容如下所示：

 SQL> with emp_data(ename,empno,mgr,l) as (select ename, empno, mgr, 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
----  ---------------
  1   **KING
  2   ****BLAKE
  3   ******ALLEN
  3   ******JAMES
  3   ******MARTIN
  3   ******TURNER
  3   ******WARD
  2   ****CLARK
  3   ******MILLER
  2   ****JONES
  3   ******FORD
  4   ********SMITH
  3   ******SCOTT
  4   ********ADAMS

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]...])
②  AS (subquery)
③  [search_clause]
④  [cycle_clause]
⑤  [,query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]... 

①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。
②AS后面的subquery就是查询语句，递归部分就写在这里。
③遍历顺序子句，可以指定深度优先或广度优先遍历顺序。
④循环子句，用于中止遍历中出现的死循环。
⑤如果还有其他递归子查询，定义同上。

subquery部分由两个成员组成：anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来，anchor member 必须写在recursive member前面。
anchor member用来定位递归的入口，锚点成员是一个SELECT语句，它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH，典型写法就是:
SELECT ... FROM 要遍历的表 WHERE ... (起始条件)

递归成员也是一个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写法：

 SELECT empno ,ename ,job ,mgr ,deptno ,level ,SYS_CONNECT_BY_PATH(ename,'\') AS path ,CONNECT_BY_ROOT(ename) AS top_manager FROM EMP START WITH mgr IS NULL -- mgr列为空，表示没有上级，该员工已经是最高级别。这是层次查询的起点 CONNECT BY PRIOR empno= mgr;

新的递归WITH写法:

 WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来 SELECT empno, ename, job, mgr, deptno ---- 先写锚点查询，用START WITH的条件 ,1 AS the_level ---- 递归起点，第一层 ,'\'||ename ---- 路径的第一截 ,ename AS top_manager ---- 原来的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL ---- 原来的START WITH条件 UNION ALL ---- 下面是递归部分 SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一层数据，来自要遍历的emp表 ,1 + t.the_level ---- 递归层次，在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列 ,t.path||'\'||e.ename ---- 把新的一截路径拼上去 ,t.top_manager ---- 直接继承原来的数据，因为每个路径的根节点只有一个 FROM t, emp e ---- 典型写法，把子查询本身和要遍历的表作一个连接 WHERE t.empno = e.mgr ---- 原来的CONNECT BY条件 ) ---- WITH定义结束 SELECT * FROM T ;

查询结果：

 EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE ------ ---------- --------- ------ ------- ---------- -------------------------- ---------- 7839 KING PRESIDENT 10 1 \KING KING 7566 JONES MANAGER 7839 20 2 \KING\JONES KING 7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING 7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING 7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING 7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING 7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING 7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING 7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING 7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING 7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING 7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING 7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING 7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING

14 rows selected.  

从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。

例2:

构造等差数列：

CONNECT BY写法：

这是一个非常特殊的用法，因为没有上下级关系，只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行（比如FROM DUAL, 比如从一个聚合后的子查询），在多行的集合上运行比较难以控制，头脑必须很清醒。

（以下ROWNUM全部可以改成 LEVEL，效果一样）：

 SELECT ROWNUM n ,ROWNUM*2 n2 ,DATE '2010-1-1'+ROWNUM-1 dt ,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon FROM DUAL CONNECT BY ROWNUM<=10;

结果：

         N         N2 DT          MON       
---------- ---------- ----------- -----------
         1          2 2010-01-01  2010-01-01
         2          4 2010-01-02  2010-02-01
         3          6 2010-01-03  2010-03-01
         4          8 2010-01-04  2010-04-01
         5         10 2010-01-05  2010-05-01
         6         12 2010-01-06  2010-06-01
         7         14 2010-01-07  2010-07-01
         8         16 2010-01-08  2010-08-01
         9         18 2010-01-09  2010-09-01
        10         20 2010-01-10  2010-10-01

10 rows selected.

这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站(http://asktom.oracle.com)发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取ROWNUM的方法：

 SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon FROM ALL_OBJECTS ---- ALL_OBJECTS是个很大的系统视图，它包含的行数足够满足一般的序列构造 WHERE ROWNUM<=10;

下面尝试用递归WITH的写法：

 WITH t(n,n2,dt,mon) AS ( SELECT 1, 2,TO_DATE('2010-1-1','YYYY-MM-DD'),TO_DATE('2010-1-1','YYYY-MM-DD') FROM DUAL --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 递增1 ,t.n2+2 ---- 递增2 ,dt+1 ---- 下一日 ,ADD_MONTHS(mon,1) ---- 下个月 FROM t ---- 没有任何连接，因为不需要，所有数据都可以从锚点成员中衍生出来 WHERE t.n<10 ) SELECT * FROM T;

一切都按规矩来，竟然还是出错了：

 ,ADD_MONTHS(mon,1) ---- 下个月 * ERROR at line 6: ORA-01790: expression must have same datatype as corresponding expression

改为字符串型看看：

 WITH t(n,n2,dt,mon) AS ( SELECT 1, 2,'2010-01-01','2010-01-01' FROM DUAL ---- 用字符串来表示日期 UNION ALL SELECT t.n+1 ---- 递增1 ,t.n2+2 ---- 递增2 ,TO_CHAR(TO_DATE(t.dt,'YYYY-MM-DD')+1,'YYYY-MM-DD') ---- 先转换为日期型，计算后换回字符串型 ,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 计算下个月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;

我很惊奇地看到这个结果：

         N         N2 DT         MON
---------- ---------- ---------- ----------
         1          2 2010-01-01 2010-01-01
         2          4 2009-12-31 2010-02-01  ----- DT竟然是递减的！
         3          6 2009-12-30 2010-03-01
         4          8 2009-12-29 2010-04-01
         5         10 2009-12-28 2010-05-01
         6         12 2009-12-27 2010-06-01
         7         14 2009-12-26 2010-07-01
         8         16 2009-12-25 2010-08-01
         9         18 2009-12-24 2010-09-01
        10         20 2009-12-23 2010-10-01

10 rows selected.

这是ORACEL 11.2.0.1.0版本的BUG，后续版本应该会改正。

没办法，只好想其他招数绕过去:

 WITH t(n) AS ( SELECT 1 FROM DUAL --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 仅仅是整数序列 FROM t WHERE t.n<10 ) SELECT n ,n*2 n2 ,DATE '2010-1-1'+n-1 dt ---- 在最终的查询中进行日期运算 ,ADD_MONTHS(DATE '2010-1-1', n-1) mon FROM T;

这下子对了：

         N         N2 DT          MON
---------- ---------- ----------- -----------
         1          2 2010-01-01  2010-01-01
         2          4 2010-01-02  2010-02-01
         3          6 2010-01-03  2010-03-01
         4          8 2010-01-04  2010-04-01
         5         10 2010-01-05  2010-05-01
         6         12 2010-01-06  2010-06-01
         7         14 2010-01-07  2010-07-01
         8         16 2010-01-08  2010-08-01
         9         18 2010-01-09  2010-09-01
        10         20 2010-01-10  2010-10-01

10 rows selected.

看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。

从一个单行结果集CONNECT BY的例子:

 SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 经过聚合的只有一行的结果集 CONNECT BY ROWNUM<=cnt;

结果：

        RN        CNT
---------- ----------
         1         14
         2         14
         3         14
         4         14
         5         14
         6         14
         7         14
         8         14
         9         14
        10         14
        11         14
        12         14
        13         14
        14         14

14 rows selected.

递归WITH写法：

 WITH t(n,cnt) AS ( SELECT 1,COUNT(*) cnt FROM EMP --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 递增1 ,t.cnt ---- 这个cnt列不做任何修改，从第一层得来 FROM t ---- 没有任何连接，因为不需要 WHERE t.n

结果同上（略）。

例3:

独立事件的排列组合：一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次，每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少？

传统的CONNECT BY写法：

 WITH t AS ( SELECT ROWNUM rn -- 先构造一个1,2,3,4的结果集，每个rn
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：oracle数据库密码到期怎么解决_oracle_
                                                                
下一篇：oracle 12c创建可插拔数据库（PDB）与用户详解_oracle_