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

智能推荐

Java编程思想 第三章:操作符

Java中的操作符和c/c++中的操作符基本一致,因为我之前学习过C语言和C++,所以本章的内容大部分都已熟知,下面只做简单的介绍。 Java操作符及优先级 Java中的操作符包括算术操作符,关系操作符,逻辑操作符,位运算符、自操作运算符、移位运算符、赋值运算符和其他运算符。 算术操作符:包括加减乘除和取余(%),优先级乘除取余高于加减,都是双元运算符,其中加法(+)可以用来连接两个字符串,比如:...

JetBrains 系列开发工具,如何配置 `SCSS` `File Watcher` ,相关输出配置参数详解:webStorm phpStorm IDEA

JetBrains 系列开发工具,如何配置 SCSS File Watcher ,相关输出配置参数详解:webStorm phpStorm IDEA 前言 你目前已经了解了如何使用 SCSS 进行开发,了解了该文章的内容:『 SCSS 日常用法 』 在 JetBrains 系列开发工具中通过 FileWatcher 进行编译的 SCSS 文件都是通过 sass 这个程序进行的。『 如何添加 Fil...

C语言小函数—二进制与十六进制

测试如下 “` int main() { long int num = 15; } “`...

仿微博或微信的文章多图显示(自定义MultiImageView)

按照一般的规矩,先上张图来供大伙看看 如果大致是大伙们需要实现的功能,不烦一观 自定义MultiImageView 工具类 具体使用 app.gradle中添加依赖 implementation 'com.github.bumptech.glide:glide:4.8.0' AndroidManifest.xml中配置联网权限 <uses-permission android:name=&q...

经典进程同步和互斥问题

经典进程同步与互斥问题 前言 一、生产者-消费者问题 1.问题描述 2.问题分析 3.代码 二、读者-写者问题 1.问题描述&&分析 2.代码 三、哲学家进餐问题 1.问题描述&&分析 2.代码 四、理发师问题 1.问题描述&&分析 2.代码 前言 在多道程序设计环境中,进程同步是一个非常重要的问题,下面讨论几个经典的进程同步问题。 一、生产者-消费...

猜你喜欢

java设计模式——ThreadLocal线程单例

1、定义一个ThreadLocal线程单例,代码如下: 2、定义一个多线程类,代码如下: 3、定义一个测试类,代码如下: 4、输出结果,如下图:...

【tensorflow】线性模型实战

线性模型:y = 1.477 * x + 0.089   1. 采样数据 采样噪声eps在均值0,方差0.01的高斯分布中,而后在均匀分布U(0,1)中,区间[-10,10]进行n=100次随机采样:   2. 计算误差 循环计算每个点的预测值与真是值之间差的平方并累加,从而获得训练集上的均芳误差损失值。   3. 计算梯度   4. 梯度更新 对权重w和偏...

常见损失函数和评价指标总结(附公式&代码)

网上看到一篇很实用的帖子关于常见损失函数和评价指标,收藏下来 本文转载于https://zhuanlan.zhihu.com/p/91511706 ------------------------------------------------------------------------------------------------------------------------------...

为什么 4G/5G 的直播延时依然很高

通信技术的发展促进了视频点播和直播业务的兴起,4G 和 5G 网络技术的进步也使得流媒体技术变得越来越重要,但是网络技术并不能解决流媒体直播的高延迟问题。 本文不会介绍网络对直播业务的影响,而是会分析直播中常见的现象 — 主播和观众之间能够感觉到的明显网络延迟。除了业务上要求的延迟直播之外,有哪些因素会导致视频直播的延迟这么高呢? live-streaming  图 1 - ...

springboot 过滤器Filter vs 拦截器Interceptor 详解

1 前言       最近接触到了过滤器和拦截器,网上查了查资料,这里记录一下,这篇文章就来仔细剖析下过滤器和拦截器的区别与联系。 2 拦截器与过滤器之间的区别 从上面对拦截器与过滤器的描述来看,它俩是非常相似的,都能对客户端发来的请求进行处理,它们的区别如下: 作用域不同 过滤器依赖于servlet容器,只能在 servlet容器,web环境下使用 拦截器依赖于sp...