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

结果如下：

10DB2/DB220DB2/DB2/DB2 文章131DB2/DB2/DB2 文章1/DB2 文章1 的评论131DB2/DB2/DB2 文章1/DB2 文章1 的评论221DB2/DB2/DB2 文章2

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

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

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

SELECT-- CONNECT_BY_ISCYCLE 是一个伪列，1 表示存在循环引用，否则为 0CONNECT_BY_ISCYCLE，NAMEFROM 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 = 1START WITH R = 1CONNECT 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