PLSQL - 递归查询 Hierarchical Query

标签: Oracle学习笔记  oracle  plsql

如果一张表中的数据行与行之间存在层级关系(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)。 

 

 

版权声明:本文为liouzch原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/liouzch/article/details/104981071

智能推荐

【Sublime】使用 Sublime 工具时运行python文件

使用 Sublime 工具时报Decode error - output not utf-8解决办法   在菜单中tools中第四项编译系统 内最后一项增添新的编译系统 自动新建 Python.sublime-build文件,并添加"encoding":"cp936"这一行,保存即可 使用python2 则注释encoding改为utf-8 ctr...

java乐观锁和悲观锁最底层的实现

1. CAS实现的乐观锁 CAS(Compare And Swap 比较并且替换)是乐观锁的一种实现方式,是一种轻量级锁,JUC 中很多工具类的实现就是基于 CAS 的,也可以理解为自旋锁 JUC是指import java.util.concurrent下面的包, 比如:import java.util.concurrent.atomic.AtomicInteger; 最终实现是汇编指令:lock...

Python 中各种imread函数的区别与联系

  原博客:https://blog.csdn.net/renelian1572/article/details/78761278 最近一直在用python做图像处理相关的东西,被各种imread函数搞得很头疼,因此今天决定将这些imread总结一下,以免以后因此犯些愚蠢的错误。如果你正好也对此感到困惑可以看下这篇总结。当然,要了解具体的细节,还是应该 read the fuc...

用栈判断一个字符串是否平衡

注: (1)本文定义:左符号:‘(’、‘[’、‘{’…… 右符号:‘)’、‘]’、‘}’……. (2)所谓的字符串的符号平衡,是指字符串中的左符号与右符号对应且相等,如字符串中的如‘(&r...

JAVA环境变量配置

位置 计算机->属性->高级系统设置->环境变量 方式一 用户变量新建path 系统变量新建classpath 方式二 系统变量 新建JAVA_HOME,值为JDK路径 编辑path,前加 方式三 用户变量新建JAVA_HOME 此路径含lib、bin、jre等文件夹。后运行tomcat,eclipse等需此变量,故最好设。 用户变量编辑Path,前加 系统可在任何路径识别jav...

猜你喜欢

常用的伪类选择器

CSS选择器众多 CSS选择器及权重计算 最常用的莫过于类选择器,其它的相对用的就不会那么多了,当然属性选择器和为类选择器用的也会比较多,这里我们就常用的伪类选择器来讲一讲。 什么是伪类选择器? CSS伪类是用来添加一些选择器的特殊效果。 常用的为类选择器 状态伪类 我们中最常见的为类选择器就是a标签(链接)上的为类选择器。 当我们使用它们的时候,需要遵循一定的顺序问题,否则将可能出现bug 注意...

ButterKnife的使用介绍及原理探究(六)

前面分析了ButterKnife的源码,了解其实现原理,那么就将原理运用于实践吧。 github地址:       点击打开链接 一、自定义注解 这里为了便于理解,只提供BindView注解。 二、添加注解处理器 添加ViewInjectProcessor注解处理器,看代码, 这里分别实现了init、getSupportedAnnotationTypes、g...

1.写一个程序,提示输入两个字符串,然后进行比较,输出较小的字符串。考试复习题库1|要求:只能使用单字符比较操作。

1.写一个程序,提示输入两个字符串,然后进行比较,输出较小的字符串。 要求只能使用单字符比较操作。 参考代码: 实验结果截图:...

小demo:slideDown()实现二级菜单栏下拉效果

效果如下,鼠标经过显示隐藏的二级菜单栏 但是这样的时候会存在一个问题,就是鼠标快速不停移入移出会导致二级菜单栏闪屏现象,一般需要使用stop()来清除事件  ...

基于docker环境的mysql主从复制

1、安装docker 可以参考之前的博客,之前写过了~ 2、拉取mysql镜像 3、创建mysql01和mysql02实例 主: 从: 4、进入容器修改配置 1)修改主数据库配置 进入主数据库容器 切换到 etc/mysql/目录下 查看可以看到my.cnf文件,使用vim编辑器打开,但是需要提前安装 安装vim命令: 安装成功后,修改my.cnf文件 新增配置后的my.cnf: binlog 日...