|
下面给大家详细介绍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写法:
yICn[h | 
转播
