(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
上面这个表情表示笔者本来打算把递归子查询也一起写了的,可是写到这里发现篇幅太长了所以今天不起飞了,预定明天的推文内容:递归子查询。没有办法,不氪金的公众号,飞机文章只能一天推一次。
今天的分享就到这里了,第二次发推没有经验,不会排版,行文也没有条理,以后估计也不会有什么长进,感谢朋友们的鼓励与支持,以后我会坚持下去,求求你们不要取关