详解 Oracle SQL 中的层次化查询（Hierarchical Query）

        Oracle 的 SQL 除了支持一些标准的 SQL 以外，还支持一些额外的特性，其中层次化查询就是其中之一。

 

 

ID                     DATA                 PARENT_ID                               

---------------------- -------------------- ----------------------------------------

1                      a                    (NULL)                                  

2                      b                    (NULL)                                  

3                      c                    2                                        

4                      d                    2                                       

5                      e                    4                                       

6                      f                    1                                        

表 1  表 test_tab 中的数据

 

 

        Oracle SQL 中的层次化查询需要用到 start with 和 connect by 两个子句。如表 1 ，假设 parent_id 记录了其父节点的 id ，则 SQL ：

 

Select id, data, nvl(to_char(parent_id), '(NULL)') parent_id from test_tab start with parent_id is null connect by prior id=parent_id;

 

        其结果如下：

 

ID                     DATA                 PARENT_ID                               

---------------------- -------------------- ----------------------------------------

1                      a                    (NULL)                                   

6                      f                    1                                       

2                      b                    (NULL)                                  

3                      c                    2                                        

4                      d                    2                                       

5                      e                    4                                       

 

        Oracle SQL 中的层次化查询的大概过程为从某个节点出发（作为根节点），顺着一些线索不断地向下一层进行遍历。而 connect by 子句就是用来描述这种线索的， connect by prior 字段 1 = 字段 2  表示父节点的字段 1 与本节点的字段 2 相等，如果 prior 放在字段 2 前面则意义相反。

 

        start with 条件表达式 这个子句是用来过滤叶子节点的，只有满足 条件表达式 节点才会被作为叶子节点开始遍历。下面的 SQL 实现的就是从根开始遍历。这里的 start with parent_id is null 表示所有的根节点都满足 parent_id is null 的条件。

 

        虽然 Oracle SQL 中的层次化查询的大概过程为从根节点出发进行遍历的，但是否能实现从非根节点开始向根节点遍历呢 ? 答案是肯定的，只要稍加变通就可实现。例如从节点 d （即： data=d ）开始一直遍历到根可以通过以下 SQL 实现。

 

Select id, data, nvl(to_char(parent_id), '(NULL)') parent_id from test_tab start with data=’d’ connect by prior parent_id = id;

 

        其结果如下：

ID                     DATA                 PARENT_ID                               

---------------------- -------------------- ----------------------------------------

4                      d                     2                                       

2                      b                    (NULL)                                  

 

 

        这里通过 start with data=’d’ 子句将节点 d 过滤出来，然后在 connect by prior parent_id = id 子句里将 prior 从 id 前调到了 parent_id 前面，相当于改变了遍历的方向。

 

        Oracle SQL 中的层次化查询会检测数据中是否存在回环，如果存在回环，则会抛出 ORA-01436: CONNECT BY loop in user data . 的错误。如果在 connect by 后面加上 nocycle 则 产生回环的最后一层的节点会被删除。

 

        Oracle 数据库还为层次化查询提供了一些伪列（ Pseudo Column ）。伪列 CONNECT_BY_ISCYCLE 当这一行有一个子节点同时也是它的祖先节点时返回 1 ，否则返回 0 。伪列 CONNECT_BY_ISLEAF 当这一行是叶节点时返回 1 ，否则返回 0 。伪列 LEVEL 返回这一行在树中的层次，根为第一层。

 

        CONNECT_BY_ROOT 查询操作符可以加在 connect by 之后的某个字段之前，表示获得这一行的根节点的该字段的值。

 

        层次化查询还支持一个特殊的函数 SYS_CONNECT_BY_PATH ， SYS_CONNECT_BY_PATH （ exp ， char ），这个函数返回从根节点到这一行计算其中每个 exp 表达式的值，并把它们连接成字符串，每个节点之间用 char 字符来分割。下面是一个例子。

 

Select SYS_CONNECT_BY_PATH (data, ‘/’) from test_tab start with parent_id is null connect by prior id=parent_id;

 

        其结果如下：

 

SYS_CONNECT_BY_PATH(DATA,'/')

------------------------------------------------------------------------------------------------------------

/a

/a/f

/b

/b/c

/b/d

/b/d/e