Oracle 递归查询

-- Start

递归查询的一个典型的例子是对树状结构的表进行查询，考虑如下的情况：

论坛首页
--数据库开发
----DB2
------DB2 文章1
--------DB2 文章1 的评论1
--------DB2 文章1 的评论2
------DB2 文章2
----Oracle
--Java 技术

以上是一个论坛的典型例子，下面我们新建一个表来存储以上信息。

CREATE TABLE BBS
(
PARENT_ID   NUMBER(9, 0)  NOT NULL,
ID          NUMBER(9, 0)  NOT NULL,
NAME        VARCHAR2(200) NOT NULL -- 板块、文章、评论等。
);


INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,0,'论坛首页');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,1,'数据库开发');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,11,'DB2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,111,'DB2 文章1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1111,'DB2 文章1 的评论1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1112,'DB2 文章1 的评论2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,112,'DB2 文章2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,12,'Oracle');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,2,'Java 技术');

现在万事兼备了，我们开始查询吧。假设现在让你查询一下‘DB2 文章1’的所有评论，有人说，这还不简单，如下这样就可以了。

SELECT * FROM BBS WHERE PARENT_ID=(SELECT ID FROM BBS WHERE NAME='DB2');

答案完全正确。那么，现在让你查询一下DB2 的所有文章及评论，怎么办？传统的方法就很难查询了，这时候递归查询就派上用场了，如下：

SELECT
-- LEVEL 是一个伪列，表示当前行所属层次，从 1 开始
LEVEL,
-- CONNECT_BY_ISLEAF 表示当前行是否是叶子节点。1 表示叶子节点，0 表示非叶子节点。
CONNECT_BY_ISLEAF,
-- CONNECT_BY_ROOT 是一个操作符，用来修饰列，表示该列的值是根节点的值。
CONNECT_BY_ROOT NAME,
-- SYS_CONNECT_BY_PATH 是一个函数，返回层次路径
SYS_CONNECT_BY_PATH(NAME, '/')
FROM 
BBS
-- START WITH 用来表示起始行
START WITH NAME = 'DB2'
-- CONNECT BY 用来指定父子连接条件
-- PRIOR 是一个操作符，用来修饰列，表示该列是父行中的列
CONNECT BY PRIOR ID = PARENT_ID
-- ORDER SIBLINGS BY 对同一层次中的行进行排序
ORDER SIBLINGS BY NAME

结果如下：

1	0	DB2	/DB2
2	0	DB2	/DB2/DB2 文章1
3	1	DB2	/DB2/DB2 文章1/DB2 文章1 的评论1
3	1	DB2	/DB2/DB2 文章1/DB2 文章1 的评论2
2	1	DB2	/DB2/DB2 文章2

上面的例子比较完美，假设由于bug，导致数据变成下面这样。

论坛首页
--数据库开发
----DB2
------DB2 文章1
--------DB2 文章1 的评论1
--------DB2 文章1 的评论2
----------DB2 文章1

此时，我们执行上面的语句，Oracle 会抛出错误。你肯定很想知道到底是哪个记录出错了，呵呵，该怎么办呢？试一试下面的语句吧。

SELECT
-- CONNECT_BY_ISCYCLE 是一个伪列，1 表示存在循环引用，否则为 0
CONNECT_BY_ISCYCLE，
NAME
FROM 
BBS
-- NOCYCLE 指示 Oracle 出错不要停
CONNECT BY NOCYCLE PRIOR ID = PARENT_ID

其实递归查询还有好多其他用途，如： 你想生成从 1 到 100 的数字，怎么办？看看下面的 SQL 吧。

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100;

再如：你想生成从 1 到 100 的数字，但是要求它们用逗号分割。

SELECT 
SYS_CONNECT_BY_PATH(R, ',') 
FROM 
(SELECT ROWNUM R FROM DUAL CONNECT BY ROWNUM <= 100)
WHERE 
CONNECT_BY_ISLEAF = 1
START WITH R = 1
CONNECT BY R = PRIOR R + 1

当然，对于上面这个例子来说，上面的写法有点复杂，下面的写法更简单。

SELECT 
LISTAGG(R, ',') WITHIN GROUP (ORDER BY R)
FROM 
(SELECT ROWNUM R FROM DUAL CONNECT BY ROWNUM <= 100)
GROUP BY 1

--更多参见：Oracle SQL 精萃

-- 声明：转载请注明出处

-- Last edited on 2015-06-16

-- Created by ShangBo on 2015-06-16

-- End