oracle层级关系按列存储_关系（深度好文）

(CSDN博主：写代码也要符合基本法)

在现实场景中，应用数据库中不仅实体表之间有关联关系，有时同一个实体表内记录间也存在关联关系，这样的数据结构一般称为层级结构(Hierarchical Structure)。

为了方便展现表中数据的层级结构，Oracle为此量身定制了递归查询运算CONNECT。

首先我们观摩一下递归查询的语法，以下是Oracle文档中给出的语法轨道图

递归查询中最主要的两节子句START WITH和CONNECT BY

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

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

对于初学者来说较难上手的部分往往是如何在CONNECT BY子句中阐述表记录间的内在关联。

递归条件中，一定要使用PRIOR运算符来标记父行的联结列。也就是说要把PRIOR放在关联条件中代表父行的字段左边。

即便递归条件是由多个子条件复合而成的，也只须其中一条使用PRIOR标记即可。

PRIOR是一元运算符，优先级等同于算术运算符中的+或-，它只作用于紧跟的表达式。

例如：CONNECT BY PRIOR empno = mgr表示在每一对父子行之间，父行的empno列值等于子行的mgr列值。

带PRIOR的字段放在算式左边还是右边是无所谓的，关键在于PRIOR放在了谁的旁边。

例如：CONNECT BY empno = PRIOR mgr则表意父行的mgr列与子行的empno列组合为联结键，这与前例完全相反。

理论上除了等式以外，在表述联结关系时也可以使用其它形式的计算。

接下来我们简单看一下递归查询语句的执行顺序，真的很简单，聪明的人都能看懂。

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

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

• 定位到START WITH指示的根节点，如果没有START WITH子句，Oracle会将每一行依次作为根节点递归检索各自的层次树(层次树也可以只有一个节点，该节点既是叶子节点也是根节点)

• 依据CONNECT BY指明的关系先找到根节点下一级的子行

• 每找到一层子行，便再向下检索一层，如此递进，直至所有叶子节点(没有下层的行)被找到

• 如果WHERE条件中还有筛选性的谓语，此时Oracle将独立地检查上述步骤得出的结果集中的每一行，将不符合筛选条件的行舍弃

• 最后Oracle按照从根节点到叶子节点的顺序返回结果集，子行将排列在其父行的下面

• 按照语句中规定的结果集输出的排序(如果有的话)加工结果集

理论知识讲起来晦涩难懂，我们辅以栗子来让本就混乱的思绪雪上加霜。

笔者觉得学习递归查询最好用的表就是Oracle数据库的万世之表EMP，记录少，人物层次关系清晰。

我们来欣赏一下上图是如何用SQL语句展现出来的

SQL> SELECT e.ename, e.empno, e.mgr  2    FROM emp e  3   START WITH e.mgr IS NULL  4  CONNECT BY PRIOR e.empno = e.mgr;ENAME      EMPNO   MGR---------- ----- -----KING        7839 JONES       7566  7839SCOTT       7788  7566ADAMS       7876  7788FORD        7902  7566SMITH       7369  7902BLAKE       7698  7839ALLEN       7499  7698WARD        7521  7698MARTIN      7654  7698TURNER      7844  7698JAMES       7900  7698CLARK       7782  7839MILLER      7934  778214 rows selected

这里面START WITH mgr IS NULL告诉数据库要从mgr列为空的那一行开始查询，CONNECT BY PRIOR empno = mgr则解释了每个员工的mgr列存储着他上级的empno

正所谓牛郎配织女，瘸驴配破车(ju)，Oracle还为递归查询配备了齐全的伪列和专属函数。

• 伪列 LEVEL

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

从而LEVEL值相同的行表示位于同一层级。

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

SQL> SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename, LEVEL  2    FROM emp e  3   START WITH e.mgr IS NULL  4  CONNECT BY PRIOR e.empno = e.mgr;ENAME                LEVEL--------------- ------------ KING                  1 |-- JONES               2 | |-- SCOTT             3 | | |-- ADAMS           4 | |-- FORD              3 | | |-- SMITH           4 |-- BLAKE               2 | |-- ALLEN             3 | |-- WARD              3 | |-- MARTIN            3 | |-- TURNER            3 | |-- JAMES             3 |-- CLARK               2 | |-- MILLER            314 rows selected

• 函数 SYS_CONNECT_BY_PATH

为了方便老眼昏花的25岁程序员看清层次路径，SYS_CONNECT_BY_PATH函数用以返回组成层级的直到当前行的值，它将这一路径上的各个值用分隔符拼接成一个VARCHAR2类型的字符串。

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

SQL> SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename  2        ,sys_connect_by_path(e.ename, '/') cpath  3    FROM emp e  4   START WITH e.mgr IS NULL  5  CONNECT BY PRIOR e.empno = e.mgr;ENAME           CPATH--------------- ---------------------------- KING         /KING |-- JONES      /KING/JONES | |-- SCOTT    /KING/JONES/SCOTT | | |-- ADAMS  /KING/JONES/SCOTT/ADAMS | |-- FORD     /KING/JONES/FORD | | |-- SMITH  /KING/JONES/FORD/SMITH |-- BLAKE      /KING/BLAKE | |-- ALLEN    /KING/BLAKE/ALLEN | |-- WARD     /KING/BLAKE/WARD | |-- MARTIN   /KING/BLAKE/MARTIN | |-- TURNER   /KING/BLAKE/TURNER | |-- JAMES    /KING/BLAKE/JAMES |-- CLARK      /KING/CLARK | |-- MILLER   /KING/CLARK/MILLER14 rows selected

• 运算符 CONNECT_BY_ROOT

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

SQL> SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename  2        ,connect_by_root e.ename root  3    FROM emp e  4   START WITH e.mgr = 7839  5  CONNECT BY PRIOR e.empno = e.mgr;ENAME           ROOT--------------- ------------ JONES        JONES |-- SCOTT      JONES | |-- ADAMS    JONES |-- FORD       JONES | |-- SMITH    JONES-- BLAKE        BLAKE |-- ALLEN      BLAKE |-- WARD       BLAKE |-- MARTIN     BLAKE |-- TURNER     BLAKE |-- JAMES      BLAKE-- CLARK        CLARK |-- MILLER     CLARK13 rows selected

• 伪列 CONNECT_BY_ISLEAF

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

SQL> SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename  2        ,connect_by_isleaf isleaf  3    FROM emp e  4   START WITH e.mgr IS NULL  5  CONNECT BY PRIOR e.empno = e.mgr;ENAME               ISLEAF--------------- ------------ KING                  0 |-- JONES               0 | |-- SCOTT             0 | | |-- ADAMS           1 | |-- FORD              0 | | |-- SMITH           1 |-- BLAKE               0 | |-- ALLEN             1 | |-- WARD              1 | |-- MARTIN            1 | |-- TURNER            1 | |-- JAMES             1 |-- CLARK               0 | |-- MILLER            114 rows selected

• 伪列 CONNECT_BY_ISCYCLE和NOCYCLE

层级结构常被称为分层树形结构，最忌讳的就是循环。这一点不难理解，参考前文递归查询的机制就知道了，如果层级关系是可以循环的，语句运行起来就会循环往复，生生不息。

所以递归查询的层次树，是铁树开花，切忌叶落归根。

一旦在递归检索中发现循环，则会停止检索并抛出异常ORA-01436: CONNECT BY loop in user data

在递归查询SQL语句中声明NOCYCLE参数可以帮助我们找到层级在哪里出现循环，此时当Oracle在检索时发现递归循环，则会跳过这个循环继续接下来的检索而不报错

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

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

SQL> UPDATE emp SET mgr = 7902 WHERE empno = 7839;1 row updatedSQL> SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename  2        ,connect_by_iscycle iscycle  3    FROM emp e  4   START WITH e.empno = 7839  5  CONNECT BY nocycle PRIOR e.empno = e.mgr;ENAME              ISCYCLE--------------- ------------ KING                  0 |-- JONES               0 | |-- SCOTT             0 | | |-- ADAMS           0 | |-- FORD              1 | | |-- SMITH           0 |-- BLAKE               0 | |-- ALLEN             0 | |-- WARD              0 | |-- MARTIN            0 | |-- TURNER            0 | |-- JAMES             0 |-- CLARK               0 | |-- MILLER            014 rows selected

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

SQL> SELECT lpad('-- ', LEVEL * 2 + 1, ' |') || e.ename ename  2        ,connect_by_iscycle iscycle  3    FROM emp e  4   START WITH e.empno = 7698 --BLAKE  5  CONNECT BY nocycle PRIOR e.empno = e.mgr;ENAME              ISCYCLE--------------- ------------ BLAKE                 0 |-- ALLEN               0 |-- WARD                0 |-- MARTIN              0 |-- TURNER              0 |-- JAMES               06 rows selected

上面这个表情表示笔者本来打算把递归子查询也一起写了的，可是写到这里发现篇幅太长了所以今天不起飞了，预定明天的推文内容：递归子查询。没有办法，不氪金的公众号，飞机文章只能一天推一次。

---

今天的分享就到这里了，第二次发推没有经验，不会排版，行文也没有条理，以后估计也不会有什么长进，感谢朋友们的鼓励与支持，以后我会坚持下去，求求你们不要取关