加载中…Oracle 11GR2的递归WITH子查询
(2012-08-26 17:39:40)
标签：
oracle11gwith查询it |
分类： 数据库 |
http://www.oracle.com/technology
... -nov/o69asktom.html
Code Listing
3: New recursive subquery
SQL> with
emp_data(ename,empno,mgr,l)
10
select l,
11
lpad('*' ,2*l, '*')||ename nm
12
from emp_data
13
order by order_by
14
/
----
---------------
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
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 (
---- 必须把结构写出来
) ----
WITH定义结束
SELECT *
FROM T
;
查询结果：
EMPNO ENAME
JOB
MGR DEPTNO
THE_LEVEL PATH
TOP_MANAGE
------
---------- --------- ------ ------- ----------
-------------------------- ----------
14 rows
selected.
从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。
例2:
构造等差数列：
CONNECT
BY写法：
这是一个非常特殊的用法，因为没有上下级关系，只有遍历的终止条件。像这类CONNECT
BY我强烈推荐在只有一行的结果集上运行（比如FROM DUAL,
比如从一个聚合后的子查询），在多行的集合上运行比较难以控制，头脑必须很清醒。
（以下ROWNUM全部可以改成
LEVEL，效果一样）：
SELECT
ROWNUM n
CONNECT BY
ROWNUM<=10;
结果：
----------
---------- ----------- -----------
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
WHERE
ROWNUM<=10;
下面尝试用递归WITH的写法：
WITH
t(n,n2,dt,mon) AS (
SELECT *
FROM T;
一切都按规矩来，竟然还是出错了：
ERROR at
line 6:
ORA-01790:
expression must have same datatype as corresponding
expression
改为字符串型看看：
WITH
t(n,n2,dt,mon) AS (
SELECT *
FROM T;
我很惊奇地看到这个结果：
----------
---------- ---------- ----------
10 rows
selected.
这是ORACEL
11.2.0.1.0版本的BUG，后续版本应该会改正。
没办法，只好想其他招数绕过去:
WITH t(n) AS
(
SELECT
n
这下子对了：
----------
---------- ----------- -----------
10 rows
selected.
看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。
从一个单行结果集CONNECT
BY的例子:
SELECT
ROWNUM rn,cnt
FROM (SELECT
COUNT(*) cnt FROM emp) ----
经过聚合的只有一行的结果集
CONNECT BY
ROWNUM<=cnt;
结果：
----------
----------
14 rows
selected.
递归WITH写法：
WITH
t(n,cnt) AS (
SELECT *
FROM t;
结果同上（略）。
例3:
独立事件的排列组合：一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次，每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少？
传统的CONNECT
BY写法：
WITH t AS
(
SELECT
ROWNUM rn --
先构造一个1,2,3,4的结果集，每个rn表示一种颜色
CONNECT BY
ROWNUM<=4
)
,t2 AS (
---- 集合t2模拟独立取四次的动作，最终结果会有4*4*4*4=256行
SELECT
ROWNUM id ---- 构造唯一ID供下面拆分用
WHERE
LEVEL=4
---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合
CONNECT BY
LEVEL<=4 ----
没有任何条件，前后都是独立的
)
,t3 AS (
---- 集合t3把t2中的PATH包含的颜色组合拆开为四行
SELECT
id,cnt,SUBSTR(PATH,rn,1) color
SELECT
COUNT(COUNT(*))/MAX(cnt) AS prob
GROUP BY
id,cnt
HAVING
COUNT(DISTINCT color)=3 ---
每一个id中包含三种颜色
;
结果：
----------
这个例子展示了CONNECT
BY来模拟排列组合的技巧。每一层遍历表示一次抽取的动作，因为每次都是完全独立的，在CONNECT BY
里面仅仅限制了抽取次数（遍历层数）而没有其他条件。SYS_CONNECT_BY_PATH可以把截至当前为止所访问到的各层次的数据串起来，在LEVEL=N就包含了前N层的排列组合情况。你可以用这个查询来看看中间生成的结果集t2：
WITH t AS
(
SELECT
ROWNUM rn --
先构造一个1,2,3,4的结果集，每个rn表示一种颜色
CONNECT BY
ROWNUM<=4
)
,t2 AS (
---- 集合t2模拟独立取四次的动作，最终结果会有4*4*4*4=256行
SELECT
ROWNUM id ---- 构造唯一ID供下面拆分用
WHERE
LEVEL=4
---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合
CONNECT BY
LEVEL<=4 ----
没有任何条件，前后都是独立的
)
SELECT *
FROM t2;
----------
---------- ----------
......(其余结果略)
256 rows
selected.
由此看到PATH列已经包含了四次抽取的所有可能结果，每个结果都被赋予一个唯一的编号ID。
如果你好奇的话可以看看下一步的结果集t3:
WITH t AS
(
SELECT
ROWNUM rn --
先构造一个1,2,3,4的结果集，每个rn表示一种颜色
CONNECT BY
ROWNUM<=4
)
,t2 AS (
---- 集合t2模拟独立取四次的动作，最终结果会有4*4*4*4=256行
SELECT
ROWNUM id ---- 构造唯一ID供下面拆分用
WHERE
LEVEL=4
---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合
CONNECT BY
LEVEL<=4 ----
没有任何条件，前后都是独立的
)
,t3 AS (
---- 集合t3把t2中的PATH包含的颜色组合拆开为四行
SELECT
id,cnt,SUBSTR(PATH,rn,1) color
SELECT *
FROM t3;
----------
---------- ----
......(其余结果略)
1024 rows
selected.
可以看到t2集合中的每一行都被拆成了四行，这是为了后面的聚合运算。
最后看看算概率的主查询：
SELECT
COUNT(COUNT(*))/MAX(cnt) AS prob
GROUP BY
id,cnt
HAVING
COUNT(DISTINCT color)=3;
COUNT(DISTINCT
color)可以算出每个ID中包含不重复的颜色数目，放在HAVING中过滤了数目不为3的那些ID。
GROUP BY
id,cnt
表示按照id来分组。因为所有行的cnt都是一样的(都等于256)，我们在分组加入它并不会改变分组的结果，加入cnt的目的是为了在查询中引用。
最后的连续两层COUNT函数的意思是要把分组结果再聚合为一行，算出满足条件的id的行数。除以cnt就得到了我们要的概率。
本例是一个在多行的结果集上进行无条件遍历的例子，前面说过了要特别小心，因为没有上下级关系，随着层数递增，数据量的增长十分可观。
递归WITH写法：
WITH T AS
(
SELECT
ROWNUM rn -- 还是先构造一个1,2,3,4的结果集
CONNECT BY
ROWNUM<=4
)
,t2(distinct_colors,lvl) AS (
--- 两个列：所有不重复颜色，层次
)
SELECT
COUNT(CASE WHEN LENGTH(distinct_colors) -
LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) ---
出现三个斜杠
FROM
t2
WHERE lvl=4
---- 同CONNECT
BY类似，我们只需观察最后一层的数据，在这里面已经包含了所有层次的颜色
;
在递归WITH子查询t2中，我们看到它用了一个CASE表达式把以前没出现过的颜色拼接到distinct_colors中。这个CASE是递归WITH的妙处，用SYS_CONNECT_BY_PATH没办法做到有条件的拼接。
而最后在计算颜色数的时候用了一个技巧，把颜色数转换为斜杠的个数，因为我们构造数据的时候每种颜色前面都带一个斜杠。为了求出字符串中某字符出现的次数，我们用了这样的办法：
先求出字符串的总长度；
用REPLACE函数从串中去除这个字符，然后再求一次长度；
两个长度之差就是被去除的字符个数。
CASE函数把出现满足条件的标记置为1,不满足则为NULL,
那么再套一个COUNT函数就能算出满足条件的行数，因为NULL是不被COUNT计入的。
COUNT和CASE的嵌套使用，也是在聚合运算中常用的技巧。
这个颜色数的计算，我们也可以在递归的过程中进行有条件累加，这样最后就可以直接使用：
WITH T AS
(
SELECT
ROWNUM rn -- 还是先构造一个1,2,3,4的结果集
CONNECT BY
ROWNUM<=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt)
AS ( --- 两个列：所有不重复颜色，层次，不重复的颜色数
)
SELECT
COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) ---
出现三个斜杠
FROM
t2
WHERE lvl=4
---- 同CONNECT
BY类似，我们只需观察最后一层的数据，在这里面已经包含了所有层次的颜色
;
例4:
构造一个二阶等差数列：这个数列的各项之差是一个等差数列
比如：1,3,6,10,15,21,...
用CONNECT
BY:
SELECT
LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n
CONNECT BY
LEVEL<=10;
结果：
----------
----------
10 rows
selected.
因为只有一条路径，所以用分析函数SUM很轻易做到了。
递归WITH写法：
WITH
t(lvl,n) AS (
SELECT *
FROM T;
结果：
----------
----------
10 rows
selected.
例5:
构造斐波那契数列:
指的是这样一个数列, 从第三项开始，每一项都等于前两项之和。
1,1,2,3,5,8,13,21,......
传统的CONNECT
BY方法做不出来，但是用10G以上所支持的MODEL可以轻松构造：
SELECT
rn,n
FROM (SELECT
ROWNUM rn FROM DUAL CONNECT BY
ROWNUM<=10)
MODEL RETURN
UPDATED ROWS
/
----------
----------
10 rows
selected.
用递归WITH的写法：
WITH
t(n,last_n,cnt) AS (
SELECT n
FROM T;
----------
10 rows
selected.
例6:
排列组合:
从5个数中取3个的所有组合C(3,5):
CONNECT
BY写法：
SELECT
SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT
ROWNUM RN FROM DUAL CONNECT BY
LEVEL<6)
WHERE
LEVEL=3
CONNECT BY
rn<PRIOR rn AND LEVEL<=3
----
强行按降序排序，这样就排除了其他相同的、只是顺序不同的组合
;
XMLPATH
--------------
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1
递归WITH写法：
WITH t AS
(
,t2(rn,xmlpath,lvl) AS (
---- 三个列：当前节点值，路径，层数
SELECT
rn,','||rn,1 FROM t ---- 先构造锚点成员的基础数据，就是上面生成的6行数据的集合
UNION
ALL
SELECT
t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 ---
把当前节点拼接入路径，层数则递增
)
SELECT
xmlpath FROM t2 WHERE lvl=3;
XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5
10 rows
selected.
如果要的不是组合而是排列，比如P(3,5)可以这么写：
SELECT
SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT
ROWNUM rn FROM DUAL CONNECT BY
LEVEL<6)
WHERE
LEVEL=3
CONNECT BY
NOCYCLE rn<>PRIOR rn AND
LEVEL<=3;
XMLPATH
----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
......(其余结果略)
60 rows
selected.
和刚才的组合写法相比，rn<PRIOR
rn变成了NOCYCLE rn<>PRIOR rn,
这表示只要rn没出现过就行，我们要的是所有的排列顺序而不仅仅是降序。注意这里面的NOCYCLE,
这个是10G上才有的。
如果不写这个NOCYCLE会怎么样？
SELECT
SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT
ROWNUM rn FROM DUAL CONNECT BY
LEVEL<6)
WHERE
LEVEL=3
CONNECT BY
rn<>PRIOR rn AND
LEVEL<=3;
ERROR:
ORA-01436:
CONNECT BY loop in user data
可以看到，这个NOCYCLE是很重要的，ORACLE不允许遍历顺序中出现循环。
在递归WITH中，NOCYCLE的写法：
WITH t AS
(
,T2(rn,xmlpath,lvl) AS (
---- 三个列：当前节点值，路径，层数
SELECT
rn,','||rn,1 FROM t ---- 先构造锚点成员的基础数据，就是上面生成的6行数据的集合
UNION
ALL
SELECT
t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 ---
把当前节点拼接入路径，层数则递增
)
CYCLE rn SET
cycle_flag TO 'Y' DEFAULT 'N' ----
这个cycle_flag是自己定义的伪列名和值，可以起到CONNECT_BY_ISCYCLE同样的作用
SELECT
xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';
结果：
XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
,2,1,5
......(其余结果略)
60 rows
selected.
在这里我们看到了前面例子中没出现过的循环子句（CYCLE
CLAUSE）。循环子句的语法如下：
① CYCLE
c_alias [, c_alias]... ----
用哪些列来判断是否发生CYCLE。一般来说就是要遍历的集合能够唯一标识出每行的那些列，例如主键。
②
SET cycle_mark_c_alias TO cycle_value
---- cycle_mark_c_alias如果发生CYCLE了要设置什么值，一般就是Y/N,
1/0 来表示是否CYCLE
③
DEFAULT no_cycle_value
---- 没有CYCLE要设什么值
①
用哪些列来判断是否发生CYCLE。一般来说就是要遍历的集合能够唯一标识出每行的那些列，例如主键。
②
cycle_mark_c_alias是用来存放CYCLE标记的列名。在本例中我们为它取名cycle_flag。
③
在没有循环的情况下要赋予cycle_mark_c_alia什么值。在本例中用的是'N'。
定义了循环子句之后，ORACLE碰到循环就会自动停止并为你指定的cycle_mark_c_alias列赋予一个标记。在随后的SELECT中可以用这个标记来过滤数据。
如果不写这个循环子句会怎么样？
WITH t AS
(
,T2(rn,xmlpath,lvl) AS (
---- 三个列：当前节点值，路径，层数
SELECT
rn,','||rn,1 FROM t ---- 先构造锚点成员的基础数据，就是上面生成的6行数据的集合
UNION
ALL
SELECT
t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 ---
把当前节点拼接入路径，层数则递增
)
SELECT
xmlpath FROM t2 WHERE lvl=3;
WITH t AS
(
*
ERROR at
line 1:
ORA-32044:
cycle detected while executing recursive WITH query
假如在上述SQL的WHERE中加上层数限制，则错误不会出现，但是数据中有重复节点：
WITH t AS
(
,T2(rn,xmlpath,lvl) AS (
---- 三个列：当前节点值，路径，层数
SELECT
rn,','||rn,1 FROM t ---- 先构造锚点成员的基础数据，就是上面生成的6行数据的集合
UNION
ALL
SELECT
t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 ---
把当前节点拼接入路径，层数则递增
)
SELECT
xmlpath FROM t2 WHERE lvl=3;
结果：
XMLPATH
------------
,1,2,1
------ 可以看到1被访问了两次
,1,2,3
,1,2,4
,1,2,5
,1,3,1
,1,3,2
,1,3,4
,1,3,5
,1,4,1
,1,4,2
,1,4,3
,1,4,5
,1,5,1
,1,5,2
......(其余结果略)
80 rows
selected.
那么这个问题不用循环子句能否解决呢？其实我们在前面的例3中已经用到了一个技巧，就?
窃诘莨榈牟糠旨由弦桓鎏跫龅备媒诘阄闯鱿止辈偶尤耄?WITH
t AS ( SELECT
ROWNUM RN
FROM DUAL CONNECT BY LEVEL<6 ) ,T2(rn,xmlpath,lvl)
AS ( ----
三个列：当前节点值，路径，层数 SELECT
rn,','||rn,1 FROM t ----
先构造锚点成员的基础数据，就是上面生成的6行数据的集合
UNION ALL SELECT
t.rn,t2.xmlpath||','||t.rn,t2.lvl+1
--- 把当前节点拼接入路径，层数则递增
FROM
t2, t WHERE
t2.rn<>t.rn AND
t2.lvl<3 AND INSTR(t2.xmlpath,t.rn)=0
-------
新加入的节点必须是未出现过的 ) SELECT
xmlpath FROM t2 WHERE lvl=3;
例7:
求遍历树中的叶子节点。
在例1中我们看到一个求出所有员工的上级关系的层次查询。现在假设要把最下级的员工求出来：
CONNECT
BY写法：
SELECT
empno, ename, job, deptno,LEVEL, SYS_CONNECT_BY_PATH(ename,'\') as
path
WHERE
CONNECT_BY_ISLEAF=1 ----- 叶子节点
START WITH
mgr IS NULL -- mgr列为空，表示没有上级，该员工已经是最高级别。这是层次查询的起点
CONNECT BY
PRIOR empno= mgr;
结果：
EMPNO ENAME
JOB
DEPTNO
LEVEL PATH
-----------------------------------------------------------------
7876
ADAMS
CLERK 20
4
\KING\JONES\SCOTT\ADAMS
7369
SMITH
CLERK 20
4
\KING\JONES\FORD\SMITH
7499
ALLEN
SALESMAN 30
3
\KING\BLAKE\ALLEN
7521
WARD
SALESMAN 30
3
\KING\BLAKE\WARD
7654
MARTIN
SALESMAN 30
3
\KING\BLAKE\MARTIN
7844
TURNER
SALESMAN 30
3
\KING\BLAKE\TURNER
7900
JAMES
CLERK 30
3
\KING\BLAKE\JAMES
7934
MILLER
CLERK 10
3
\KING\CLARK\MILLER
CONNECT_BY_ISLEAF是10G以上提供的，它是一个伪列，当CONNECT_BY_ISLEAF=1时表示该行为叶子节点。
在9i中没有CONNECT_BY_ISLEAF这个伪列，我们可以根据叶子节点的定义写一个相关子查询：
SELECT
empno, ename, job, deptno,LEVEL, SYS_CONNECT_BY_PATH(ename,'\') as
path
WHERE NOT
EXISTS (SELECT NULL FROM emp WHERE mgr = e.empno)
START WITH
mgr IS NULL
CONNECT BY
PRIOR empno= mgr;
在这里，NOT
EXISTS相关子查询的意思即不存在下一级数据，这正是叶子节点的定义。
在递归WITH子查询中实现CONNECT_BY_ISLEAF的方法:
WITH
T(empno, ename, mgr, the_level, path) AS ( ----
必须把结构写出来
)
SEARCH DEPTH
FIRST BY mgr SET seq --- 指定深度优先,
按顺序生成序号seq
SELECT *
FROM (
SELECT
T.*
WHERE
is_leaf=1
;
EMPNO ENAME
MGR
THE_LEVEL PATH
SEQ
IS_LEAF
------
---------- ------- ---------- ----------------------------
---------- ----------
8 rows
selected.
seq
是整个结果集按照深度优先遍历顺序排序后的序号。按照深度优先的定义，每个节点都要比它的上一节点更深一层，除非已经到底（也就是叶子节点）。因此如果层数没有增加，则我们知道碰到了叶子节点，这就是最后一个CASE表达式的依据。
这个例子中出现了前面没有的搜索子句(SEARCH
CLAUSE)。
SEARCH子句的语法：
SEARCH
①
{ DEPTH FIRST BY c_alias [,
c_alias]...
②
| BREADTH FIRST BY c_alias [,
c_alias]...
③
SET
ordering_column
①DEPTH
FIRST表示按深度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在深度优先的前提下，同一层次的数据的排序情况。这和原来CONNECT
BY查询中的ORDER SIBLING BY子句是一样的。
②BREADTH
FIRST表示按广度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在广度优先的前提下，同一层次的数据的排序情况。
③列名ordering_column用于存放排序后的序号，是一个从1开始的连续正整数。后续的查询中可以利用这个列得知某行数据在整个结果集中的位置。
那么这个搜索子句是不是可以指定ORACLE的搜索顺序呢？我们从递归WITH的写法来看，数据是一层一层叠加上去的，也就是广度优先算法。
为了证明这一点，我们可以写一个自定义函数，根据输出的顺序来判断ORACLE的搜索顺序：
CREATE OR
REPLACE FUNCTION f_get_mgr (
RETURN
NUMBER
AS
BEGIN
END
f_get_mgr;
/
把查询修改一下：
WITH
T(empno, ename, mgr, the_level, path) AS (
)
SEARCH DEPTH
FIRST BY mgr SET seq ---- 强行指定深度优先顺序
SELECT *
FROM T
;
输出：
--------
---------- ------ ---------- --------------------------
----------
14 rows
selected.
level=2
empno=7566
level=2
empno=7698
level=2
empno=7782
level=3
empno=7788
level=3
empno=7902
level=3
empno=7499
level=3
empno=7521
level=3
empno=7654
level=3
empno=7844
level=3
empno=7900
level=3
empno=7934
level=4
empno=7876
level=4
empno=7369
尽管前半部分输出已经按照深度优先顺序排列，但从后半部分的输出来看，节点是逐层被加入的，可见遍历还是按广度优先顺序来的。
如果我们把查询改为广度优先，就和函数调用顺序一致了：
WITH
T(empno, ename, mgr, the_level, path) AS (
)
SEARCH
BREADTH FIRST BY mgr SET seq ----
强行指定广度优先顺序
SELECT *
FROM T
;
输出：
-------
---------- ------ ---------- --------------------------
------
14 rows
selected.
level=2
empno=7566
level=2
empno=7698
level=2
empno=7782
level=3
empno=7788
level=3
empno=7902
level=3
empno=7499
level=3
empno=7521
level=3
empno=7654
level=3
empno=7844
level=3
empno=7900
level=3
empno=7934
level=4
empno=7876
level=4
empno=7369
以往也有人在9i中用这个方法制造is_leaf标记，但是该方法依赖于CONNECT
BY的遍历顺序生成的ROWNUM，而ORACLE并没有保障ROWNUM一定会按深度优先的顺序生成。现在递归WITH子句支持遍历输出顺序的显式指定，就可以放心使用了。
--------------------------------------------------------
例8:
下面的例子来自我的一个老贴：
http://www.itpub.net/thread-1037629-1-1.html
机票路线问题：假设有一张表存储了起飞、到达机场以及这段路线的票价，现在要求出从城市1到城市2的所有飞行线路及其总价格。
测试环境设置：
CREATE TABLE
fares ---- 票价
(
);
测试数据：
INSERT INTO
FARES VALUES('BJ','SH',500);
INSERT INTO
FARES VALUES('SH','GZ',1500);
INSERT INTO
FARES VALUES('BJ','GZ',1800);
INSERT INTO
FARES VALUES('GZ','BJ',1600);
insert into
fares values('GZ','SH',1300);
INSERT INTO
FARES VALUES('BJ','SZ',100);
INSERT INTO
FARES VALUES('SZ','GZ',110);
COMMIT;
用CONNECT
BY求出所有北京到上海的线路，转机不超过10次：
WITH f1 AS
(
SELECT LEVEL
lvl
WHERE arrive
= 'SH' ----
目的地是上海
START WITH
depart = 'BJ' ---- 从北京出发
CONNECT BY
NOCYCLE depart=prior arrive ----
每段路线首位衔接
)
SELECT *
FROM f1;
输出：
----------
------------------------------------------
可以看到有三条飞行路线，LEVEL表示了路线分为几段（转机次数）。
现在路线有了，接下来就得算总价格。这就意味这必须和fares做一个连接，路线的每一段都必须拆成起飞、到达机场这样的格式。
为了把一行拆成多行，我们必须构造一个自然数集(从1到最大LEVEL)连接上去；而每段路线的起始点可以用字符串函数INSTR,
SUBSTR函数解析出来。
WITH f1 AS
(
SELECT LEVEL
lvl
WHERE arrive
= 'SH' ----
目的地是上海
START WITH
depart = 'BJ' ---- 从北京出发
CONNECT BY
NOCYCLE depart=prior arrive ----
每段路线首位衔接
)
,f2 AS
(
SELECT
path
FROM
f1
WHERE
r.n<=lvl ------ 每一行包含的路线段数为lvl,
相对于这一行我们需要拆成行数为lvl，即从集合r中取出1~lvl行来进行连接。
)
SELECT *
FROM f2
ORDER BY
1;
输出：
PATH
DEPART
ARRIVE
--------------------------
---------- ----------
/BJ/GZ/SH/
GZ
SH
/BJ/GZ/SH/
BJ
GZ
/BJ/SH/
BJ
SH
/BJ/SZ/GZ/SH/
SZ
GZ
/BJ/SZ/GZ/SH/
GZ
SH
/BJ/SZ/GZ/SH/
BJ
SZ
6 rows
selected.
至此我们不仅拥有了所有北京到上海的飞行线路，而且这条线路的组成也都分解出来了。接下来的事情就是和fares表做一个连接求出票价，然后按PATH来做分组聚合求出线路总成本：
WITH f1 AS
(
SELECT LEVEL
lvl
WHERE arrive
= 'SH' ----
目的地是上海
START WITH
depart = 'BJ' ---- 从北京出发
CONNECT BY
NOCYCLE depart=prior arrive ----
每段路线首位衔接
)
,f2 AS
(
SELECT
path
FROM
f1
WHERE
r.n<=lvl ------ 每一行包含的路线段数为lvl,
相对于这一行我们需要拆成行数为lvl，即从集合r中取出1~lvl行来进行连接。
)
SELECT
SUM(price) AS cost,path
WHERE
f2.depart=fares.depart AND f2.arrive = fares.arrive
GROUP BY
path
ORDER BY
cost;
结果：
----------
------------------------
另外也有一个稍微简化的写法。在拼接路径的时候，我们可以把价格也拼起来，最后只要分解开再求和就可以了，无需和fares表再做连接：
WITH f1 AS
(
SELECT LEVEL
lvl
WHERE arrive
= 'SH' ----
目的地是上海
START WITH
depart = 'BJ' ---- 从北京出发
CONNECT BY
NOCYCLE depart=prior arrive ----
每段路线首位衔接
)
,f2 AS
(
SELECT
path
FROM
f1
WHERE
r.n<=lvl ------ 每一行包含的路线段数为lvl,
相对于这一行我们需要拆成行数为lvl，即从集合r中取出1~lvl行来进行连接。
)
SELECT
SUM(price) AS cost,path
GROUP BY
path
ORDER BY
cost;
上述用CONNECT
BY求出总票价的方法十分繁琐，因为SYS_CONNECT_BY_PATH必须先分开再聚合。
递归WITH的方法：沿路径求和的实现非常方便。
WITH T
(depart,arrive,path,cost,lvl) AS (
SELECT
depart ----
构造第一层数据：从起点城市出发
UNION ALL
------- 递归部分：把衔接的新一段路程拼接进去
SELECT
f.depart
WHERE
f.depart=t.arrive
-----
递归条件：起飞机场是上一段的到达机场
)
SELECT
t.path||'/'||t.arrive path ----
在右边拼上最后一段旅程的到达机场，构成完整的路径。
FROM T WHERE
arrive='SH';
PATH
COST
-------------------
----------
/BJ/SH
500
/BJ/GZ/SH
3100
/BJ/SZ/GZ/SH
1510
例8:
沿路径求乘积应用例子:
BOM
BOM（Bill Of
Material）是一个ERP的相关术语，指的是物料清单，又称材料表或配方料表，它反映生产产品与其组件的数量和从属关系。
复杂的版本见：
http://www.itpub.net/thread-1233081-3-1.html
这里是一个简化版。
测试环境设置：
CREATE TABLE
BOM (
测试数据：
INSERT INTO
BOM VALUES ('A','B',3);
INSERT INTO
BOM VALUES ('A','C',2);
INSERT INTO
BOM VALUES ('A','D',4);
INSERT INTO
BOM VALUES ('B','E',2);
INSERT INTO
BOM VALUES ('B','F',3);
INSERT INTO
BOM VALUES ('D','G',6);
INSERT INTO
BOM VALUES ('D','H',5);
INSERT INTO
BOM VALUES ('E','I',3);
COMMIT;
例子数据中部件A由3个B,
2个C和4个D组成，一个B由2个E和3个F组成，等等。
现在要求组成A的所有零部件及其数量。
用CONNECT
BY的办法十分困难。和上例类似，首先要求出树的遍历路径，然后再把路径拆开为多行，求出每段的数量构成；最后再做聚合运算。因为没有现成的聚合求积函数，必须转换为对数，求出对数之和，再取反对数。
WITH t AS
(
SELECT
ROWNUM id ----- 为每行分配一个唯一的id, 用于随后的记录拆分和聚合
START WITH
item_no='A'
CONNECT BY
item_no = PRIOR part_no
)
,t2 AS
(
SELECT
id
WHERE rn
<= lvl ------ 每一行包含的段数为lvl,
相对于这一行我们需要拆成行数为lvl，即从集合r中取出1~lvl行来进行连接。
GROUP BY
id,part_no
)
SELECT
part_no,SUM(qty) from t2 GROUP BY part_no;
结果：
PART_NO
SUM(QTY)
----------
----------
H
20
I
18
D
4
B
3
C
2
F
9
E
6
G
24
8 rows
selected.
用递归WITH的办法可谓赏心悦目：
WITH
t(part_no,qty) AS (
SELECT
part_no ---- 取出第一层数据，即需要计算的部件A
WHERE
item_no='A'
UNION
ALL
SELECT
b.part_no
WHERE
b.item_no = t.part_no
)
SELECT
part_no,SUM(qty) from t GROUP BY part_no;
结果：
PART_NO
SUM(QTY)
----------
----------
H
20
I
18
D
4
B
3
C
2
E
6
F
9
G
24
8 rows
selected.
喜欢
0
赠金笔
前一篇：Oracle执行计划详解[转]