oracle 层次化查询(生成菜单树等)

1、简介:Oracle层次化查询是Oracle特有的功能实现,主要用于返回一个数据集,这个数据集存在树的关系(数据集中存在一个Pid记录着当前数据集某一条记录的Id)。

2、层次化查询主要包含两个子句,一个start with另一个是connect by。

start with:这个子句一般用于指定层次化查询的开始节点(也就是树的最顶级节点),找到最顶级节点,然后按照一定的规则开始查找其剩余的子节点

connect by:这个子句就是上面所说的规则,用于查找剩余子节点的规则

CREATE TABLE MENU
(    "ID" NUMBER, 
    "DATA" VARCHAR2(100), 
    "PID" NUMBER
) 
insert into MENU (id, data, pid)values (7, 'g', 3);
insert into MENU (id, data, pid)values (1, 'a', null);
insert into MENU (id, data, pid)values (2, 'b', null);
insert into MENU (id, data, pid)values (3, 'c', 2);
insert into MENU (id, data, pid)values (4, 'd', 2);
insert into MENU (id, data, pid)values (5, 'e', 4);
insert into MENU (id, data, pid)values (6, 'f', 1);

下面开始执行层次化查询,从PId为null的节点(该节点为根节点)开始递归查找,查找出所有的更节点下的子节点,构建出一个完整的树

select ID,DATA,nvl(TO_CHAR(PID),'NULL') from menu start with PID is NULL connect by prior ID=pid

代码解析:

(1)、start with PID is NULL  指定层次化查询的根节点,

红框内的两个节点为根节点,并开始遍历其余的节点。

(2)、connect by prior ID=pid  当前节点的PID等于上一层节点的ID,如果满足条件,就加入到树结果集中

指定遍历查找子节点的规则----->  这一过程是递归查找,会一层一层找下去,直到不符合这一规则,则查找停止。

3、实现上面结果集的另一种Sql实现

select ID,DATA,nvl(TO_CHAR(PID),'NULL') from menu start with (data='a' or data='b') connect by prior ID=PID

结论:根节点的定义比较灵活,但是(connect by)遍历子节点的规则,比较固定基本都是判断父节点和子节点的ID的,如果理解了这句话,层次化查询,差不多也就理解了!

4、  Oracle SQL 中的层次化查询会检测数据中是否存在回环(死循环),如果存在回环,则会抛出 ORA-01436: CONNECT BY loop in user data . 的错误。如果在 connect by 后面加上 nocycle 则 产生回环的最后一层的节点会被删除。

如果数据中出现这种情况,产生了回环,那么在connect by 后面 加nocycle,节能

select ID,DATA,nvl(TO_CHAR(PID),'NULL') from menu start with (data='a' or data='b') connect by nocycle prior ID=PID 

just没有报错,有点郁闷,并没有删除,不知道哪里出了问题,但是至少不抱错了!!!

5、Oracle 还为层次化查询提供了一些伪列( Pseudo Column )。

(1)、CONNECT_BY_ISCYCLE 当这一行有一个子节点同时也是它的祖先节点时返回 1 ,否则返回 0 。

(2)、CONNECT_BY_ISLEAF 当这一行是叶节点时返回 1 ,否则返回 0 。伪列 LEVEL 返回这一行在树中的层次,根为第一层。

(3)、CONNECT_BY_ROOT 查询操作符可以加在 connect by 之后的某个字段之前,表示获得这一行的根节点的该字段的值。

6、层次化查询还支持一个特殊的函数 SYS_CONNECT_BY_PATH , SYS_CONNECT_BY_PATH ( exp , char ),这个函数返回从根节点到这一行计算其中每个exp 表达式的值,并把它们连接成字符串,每个节点之间用 char 字符来分割。下面是一个例子。

这个函数很棒,可以考虑其他的数据库也实现这个方法,这样我们处理一个树结构就很方便了!!!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏分布式系统进阶

Librdkafka的操作处理队列

35920
来自专栏开发技术

flying-saucer + iText + Freemarker实现pdf的导出, 支持中文、css以及图片

      项目中有个需求,需要将合同内容导出成pdf。上网查阅到了 iText , iText 是一个生成PDF文档的开源Java库,能够动态的从XML或者数...

59410
来自专栏Bug生活2048

.net core下对于附件上传下载的实现

.net core通过IFormFile接收文件对象,再通过流的方式保存至指定的地方。

25530
来自专栏影子

SpringBoot中关于Mybatis使用的三个问题

11550
来自专栏海天一树

Neo4j学习(3):操作图数据库的语言--Cypher

match是匹配规则,(n)表示所有节点,语法要求加上小括号。 return n表示返回匹配到的所有节点

17820
来自专栏菩提树下的杨过

[转自JeffreyZhao]在LINQ to SQL中使用Translate方法以及修改查询用SQL

目前LINQ to SQL的资料不多——老赵的意思是,目前能找到的资料都难以摆脱“官方用法”的“阴影”。LINQ to SQL最权威的资料自然是MSDN,但是M...

22750
来自专栏瓜大三哥

Yaffs_guts(三)

1.垃圾回收 1.static int yaffs_InitialiseBlocks(yaffs_Device *dev,int nBlocks)//块初始化 ...

24750
来自专栏Java 技术分享

MVC 小案例 -- 信息管理

31150
来自专栏码农分享

4.1、苏宁百万级商品爬取 代码讲解 索引建立

Lucene是一款高性能的、可扩展的信息检索(IR)工具库。信息检索是指文档搜索、文档内信息搜索或者文档相关的元数据搜索等操作。

16830
来自专栏Android 开发学习

JsBridge 源码分析

21830

扫码关注云+社区

领取腾讯云代金券