PLSQL - 递归查询 Hierarchical Query

如果一张表中的数据行与行之间存在层级关系（hierarchical data），我们则可以使用递归查询语法来展现这种层级关系。

一、语法

START WITH子句用以在查询中指定开始检索的根节点（可以是多行）；

CONNECT BY子句用以指示层级关系的内在关联。

如轨道图所示，START WITH子句和CONNECT BY子句孰前孰后都是可以的。

递归条件（CONNECT BY condition）中，必须使用PRIOR运算符来标记父行的联结列。即便递归条件是由多个子条件复合而成的，也只须其中一条使用PRIOR标记即可。

PRIOR是一元运算符，优先级等同于算术运算符中的+或-，它只作用于紧跟的表达式。例如：CONNECT BY PRIOR empno = mgr表示在每一对父子行之间，父行的empno列值等于子行的mgr列值。

事实上PRIOR放在算式的哪一侧都是可行的，所以CONNECT BY mgr = PRIOR empno与上例是完全一致的，但是CONNECT BY empno = PRIOR mgr则表意父行的mgr列与子行的empno列组合为联结键。

虽然理论上除了等式（=）以外，在表述联结关系时也可能使用到其它形式的计算，但是运行这些类型的联结时有可能会发生无限循环，当Oracle检测到这样的循环就会抛出报错。

CONNECT BY条件和PRIOR表达式中都允许使用不相关子查询。

另外，PRIOR操作禁用序列。

二、执行顺序

在SQL语句结构中，表联结（如果有的话）最先执行，接下来按照CONNECT BY子句声明的条件进行递归检索，WHERE条件中其它筛选性的谓语最后执行。

Oracle按照如下的步骤实现递归查询： 

1. 定位到START WITH指示的根节点，如果没有START WITH子句，Oracle会将每一行依次作为根节点递归检索各自的层次树（层次树也可以只有一个节点，该节点既是叶子节点也是根节点）；
2. 依据CONNECT BY指明的关系先找到根节点下一级的子行；
3. 每找到一层子行，便再向下检索一层，如此递进，直至所有叶子节点（没有下层的行）被找到；
4. 如果WHERE条件中还有筛选性的谓语，此时Oracle将独立地检查上述步骤得出的结果集中的每一行，将不符合筛选条件的行舍弃；
5. 最后Oracle按照从根节点到叶子节点的顺序返回结果集，子行将排列在其父行的下面；
6. 按照语句中规定的结果集输出的排序（如果有的话）加工结果集。

三、简单示例

例如在EMP表中就存在着层级关系：每一行数据MGR列中的值可以追溯其他行的EMPNO列，这种数据行间的层级还原了现实世界中的职位上下隶属。

上图所示的关系可以用下面的查询展示：

SELECT e.ename, e.empno, e.mgr
  FROM emp e
 START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno = e.mgr;

 

四、递归查询中的伪列和函数

4.1 LEVEL伪列

LEVEL表示递归查询中层级的深度，根节点上LEVEL为1，如前所述递归查询的检索顺序，每增加一层，LEVEL值就加一。从而LEVEL值相同的行表示位于同一层级。

LEVEL值最大的行一定是叶子节点，但叶子节点的LEVEL不一定都是最大的。 

SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename, LEVEL
  FROM emp e
 START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno = e.mgr;

4.2 SYS_CONNECT_BY_PATH函数

SYS_CONNECT_BY_PATH函数用以返回组成层级的直到当前行的值，它将这一路径（Path）上的各个值用分隔符拼接成一个VARCHAR2类型的字符串。

SYS_CONNECT_BY_PATH(column, char)

注意：指定的列和分隔符都必须是字符串类型或可以隐式转型为字符串的值，而且分隔符不得是任一个列值的子串（ORA-30004）。

SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename
      ,sys_connect_by_path(e.ename, '/') cpath
  FROM emp e
 START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno = e.mgr;

4.3 CONNECT_BY_ROOT运算符

CONNECT_BY_ROOT用以返回当前行的根节点上指定列的值。

SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename
      ,connect_by_root e.ename root
  FROM emp e
 START WITH e.mgr = 7839
CONNECT BY PRIOR e.empno = e.mgr;

4.4 CONNECT_BY_ISLEAF伪列

CONNECT_BY_ISLEAF伪列用以在层级数据中识别出叶子节点：如果当前行是没有子节点的叶子节点，则返回1，否则返回0。

SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename
      ,connect_by_isleaf isleaf
  FROM emp e
 START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno = e.mgr;

4.5 CONNECT_BY_ISCYCLE伪列和NOCYCLE参数

层级结构常被称为分层树形结构，这是很贴切形象的：正如一棵树的任何一个枝杈或树叶不能生长到其树根里一样，递归查询不允许表数据的层级关系发生循环，因为这会带来无限循环的检索。当Oracle在递归检索中发现这种循环，则会停止检索并抛出异常ORA-01436: CONNECT BY loop in user data。

当表数据出现循环层级关系，可以在递归查询SQL语句中声明NOCYCLE参数，此时当Oracle在检索时发现递归循环，则会跳过这个循环继续接下来的检索而不报错。

CONNECT_BY_ISCYCLE伪列可以搭配NOCYCLE使用，以标记结果集中发生层级循环的行：如果当前行拥有本来是自身上级节点的子节点，则返回1，否则返回0。

下例中，首先将KING的MGR更新为了FORD，如此FORD拥有了一个子节点KING，然而从KING向下层检索又将能够回到FORD自己，这便形成了一个递归循环（CONNECT BY LOOP）。

UPDATE emp SET mgr = 7902 WHERE empno = 7839;

SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename
      ,connect_by_iscycle iscycle
  FROM emp e
 START WITH e.empno = 7839
CONNECT BY nocycle PRIOR e.empno = e.mgr;

 

值得注意的是，递归循环异常是一个运行时错误，换句话说，只有当CONNECT BY中的某个子节点向下N层会返回到本SQL查询的根节点时，才会报错。

SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename
      ,connect_by_iscycle iscycle
  FROM emp e
 START WITH e.empno = 7698 --BLAKE
CONNECT BY nocycle PRIOR e.empno = e.mgr;

 

五、递归子查询

在Oracle 11.2中新出现了递归子查询因子化（RSF），换言之，WITH语句中的子查询可以自己引用自己，从而实现递归查询。

递归的WITH子句需要两个查询块：定位点成员和递归成员。这两个子查询块必须通过UNION ALL结合到一起，定位点成员在前，递归成员在后。

WITH empc(empno, ename, mgr, clevel) AS
 (SELECT e.empno, e.ename, e.mgr, 1 clevel
    FROM emp e
   WHERE e.mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, c.clevel + 1
    FROM emp e, empc c
   WHERE e.mgr = c.empno) 
SEARCH depth FIRST BY empno SET corder
SELECT lpad('-- ', c.clevel * 2 + 1, ' |') || c.ename ename, c.clevel
  FROM empc c;

个人认为RSF功能比较鸡肋，故在此不做赘述。值得一提的是，从例程中模拟的LEVEL伪列——CLEVEL可以看出，递归子查询的实现过程和CONNECT BY的检索顺序是一致的（无论指定DEPTH FIRST或是BREADTH FIRST）。