- 1【初阶数据结构】树（tree）的基本概念——C语言_c语言 tree
- 2【Python】Python性能分析工具之Memray库的简介、安装、使用方法、示例代码、注意事项等详细攻略_python memray
- 3软件质量模型（ISO）和软件质量模型保证（SQA）_iso六大特性
- 4群晖开启https ssl证书获取及安装_wndows11安裝群暉證書位置
- 5从Win11 系统SSLVPN无法登录故障浅谈达梦数据库安全性_ssl无法连接 win11系统重装
- 6第18章 安全架构设计理论与实践（2024）
- 7微信小程序在开发工具中请求接口正常，上线后请求接口总是失败？？_微信小程序本地调试正常 上线后提交不成功
- 8Docker远程接口未授权访问漏洞解决方案之 Docker Remote API TLS 认证_docker远程接口未授权访问漏洞怎么解决_docker remote api未授权访问漏洞
- 9最强AI生成PPT工具！支持思维导图等各种格式！（内附完整教程）完全免费！用这一个就够了！【AI助手】一键打造高逼格PPT：MotionGo使用指南与评测_motiongo 自动做ppt
- 103月面试华为被刷，准备半年，9月二战华为终于上岸，这我拿个27K应该也不过分吧？_华为od二战生开多少
Oracle的学习心得和知识总结（五）|Oracle数据库 Connect By 技术详解_oracle connect by
赞
踩
目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、Oracle Database Documentation 官方文档，点击前往
2、oracle connect by用法篇，点击前往
3、参考书籍：《涂抹Oracle 三思笔记之一步一步学Oracle》
4、参考书籍：《Oracle Database 11g数据库管理艺术》
5、ORACLE分层查询start with&connect by，点击前往
6、oracle 进阶 connect by 和level 的用法，点击前往
7、参考书籍：《Oracle Database 9i10g11g编程艺术深入数据库体系结构(第2版)》
8、树状数据库表：Oracle中start with…connect by prior子句用法，点击前往
9、Hierarchical Queries，点击前往
Oracle数据库 Connect By 技术
本人博客严正声明
是这样的，熟悉在下的小伙伴们都知道 我写博客主要目的就是分享和学习总结。至于CSDN的排名 排名什么的，我并不是很在意！
- 一来 很不喜欢标题党
- 二来 更反感灌些水文
- 三来 痛恨无下限抄袭
本人博客都是认认真真写的，结果在CSDN并没有什么的太大的名气 关注度什么的也不高！前些天 一位好心的粉丝私聊了在下，反而一名某平台的哥们儿 快把我的《PostgreSQL的学习心得和知识总结》都给照搬过去了，甚至一个字都不改(连同在下 都是只字不提 好歹稍微提一下呀)！！！
实在是太过分，后来经过(友好)协商，现已经全部删除了！
本人是做PostgreSQL内核开发的，深感当下学风不正 大家都很浮躁，一向踏踏实实深耕的并不是很多！因为写代码这件事情上，欺骗不了任何人！本本分分老老实实地写好代码做好学问十分不易，容不得掺沙子和造假！这里把我喜欢的一句话送给各位以共勉：
非淡泊无以明志，
非宁静无以致远！
文章快速说明索引
学习目标：
目的：因为接下来想在PostgreSQL数据库内核上实践实现一下 Connect By操作 ，但是就目前而言 PostgreSQL尚不支持此功能。PostgreSQL社区的爱好者和一些基于PostgreSQL的数据库公司也曾分享过一些 类似的 Connect By的简易实现，大家有兴趣也可以去看看！本文主要记录在Oracle数据库上面的 Connect By 功能的使用体验和基于开发者的设计思考，以期在PostgreSQL数据库上面支持此功能！这里主要是学习以及介绍Oracle数据库 Connect By 功能的注意事项等，基于PostgreSQL数据库的功能开发等日后开发完成之后 由新博客进行介绍和分享！
学习内容：（详见目录）
1、Oracle数据库的Connect By技术
学习时间：
2021年06月12日 16:36:04
学习产出：
1、Oracle数据库 Connect By 技术学习
2、CSDN 技术博客 1篇
3、PostgreSQL数据库 Connect By 功能实现设计思考
注：下面我们所有的学习环境是Centos7+PostgreSQL13.1+Oracle11g+MySQL5.7
postgres=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.06 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
Connect By的简介
在Oracle数据库中，如果表包含分层数据，则可以使用Hierarchy query子句按分层顺序选择行。其语法格式如下：
{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition
}
- 1
- 2
- 3
或
解释一下上图的逻辑(来自于Oracle的官方文档)：
- 以上两种SQL都是可以的
condition：条件 指定一个或多个表达式和逻辑（布尔）运算符的组合，并返回TRUE、FALSE或UNKNOWN值start with：指定起始节点的条件 或 指定层次结构的根行connect by：指定层次结构的父行和子行之间的条件关系prior：查询父行的限定符，格式:prior column1 = column2 or column1 = prior column2 and …(如下：)nocycle： 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条。(NOCYCLE参数指示Oracle数据库从查询返回行，即使数据中存在CONNECT BY循环。将此参数与CONNECT_BY_ISCYCLE伪列一起使用，以查看哪些行包含循环。有关更多信息，请参阅CONNECT_BY_ISCYCLE伪列。)- 循环行：该行只有一个子行，而且子行又是该行的祖先行
在分层查询中，条件中的一个表达式必须使用 PRIOR 运算符限定才能引用父行：
... PRIOR expr = expr
or
... expr = PRIOR expr
- 1
- 2
- 3
如果CONNECT BY condition条件是复合条件，则只有一个条件需要 PRIOR 运算符，尽管可以有多个 PRIOR 条件：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id ...
- 1
- 2
- 3
Connect By 是结构化查询中用到的，更亲切的语法如下：
select targetlist
from tablename [where condition0]
start with condition1
connect by condition2
- 1
- 2
- 3
- 4
详细解释一下：
将一个树状结构存储在一张表里，比如一个表中存在两个字段：
keyid,parent_keyid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构
用上述语法的查询可以取得这棵树的所有记录
1、condition1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树
2、condition2 是连接条件，其中用PRIOR表示上一条记录，比如CONNECT BY PRIOR keyid = parent_keyid就是说上一条记录的keyid 是本条记录的parent_keyid，即本记录的父亲是上一条记录
接下来，我们这里准备一下备用的数据如下：
-- 创建表
create table player(keyid int,parent_keyid int,name varchar(16),salary int,sex varchar(4));
-- 添加数据
insert into player values(1,0,'zhangsan','1000000','f');
insert into player values(2,1,'lisi','50500','m');
insert into player values(3,1,'wangwu','60000','m');
insert into player values(4,1,'houzi','65000','m');
insert into player values(5,2,'maliu','30000','f');
insert into player values(6,2,'liuqi','25000','m');
insert into player values(7,4,'gouba','23000','m');
insert into player values(8,4,'dujiu','21000','f');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
查询&树状展示如下：
SQL> select t.*, t.rowid from player t;
KEYID PARENT_KEYID NAME SALARY SEX ROWID
---------- ------------ ---------- ---------- ---- ------------------
1 0 zhangsan 1000000 f AAATSIAABAAAVRhAAA
2 1 lisi 50500 m AAATSIAABAAAVRhAAB
3 1 wangwu 60000 m AAATSIAABAAAVRhAAC
4 1 houzi 65000 m AAATSIAABAAAVRhAAD
5 2 maliu 30000 f AAATSIAABAAAVRhAAE
6 2 liuqi 25000 m AAATSIAABAAAVRhAAF
7 4 gouba 23000 m AAATSIAABAAAVRhAAG
8 4 dujiu 21000 f AAATSIAABAAAVRhAAH
8 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
Connect By的使用
下面来看一下，在Oracle数据库中的一些常见使用：
第一个：查询以
keyid为1开始的节点的所有子孙节点
SQL> select keyid,parent_keyid,name,prior name,salary
2 from player
3 start with keyid=1
4 connect by prior keyid = parent_keyid;
KEYID PARENT_KEYID NAME PRIORNAME SALARY
---------- ------------ ---------- ---------------- ----------
1 0 zhangsan 1000000
2 1 lisi zhangsan 50500
5 2 maliu lisi 30000
6 2 liuqi lisi 25000
3 1 wangwu zhangsan 60000
4 1 houzi zhangsan 65000
7 4 gouba houzi 23000
8 4 dujiu houzi 21000
8 rows selected.
# 该示例添加了一个 START WITH 子句来为层次结构指定一个根行，并添加一个使用 SIBLINGS 关键字的 ORDER BY 子句来保留层次结构内的排序：
SQL> select keyid,parent_keyid,name,prior name,salary,LEVEL le
2 from player
3 start with keyid=1
4 connect by prior keyid = parent_keyid
5 ORDER SIBLINGS BY name;
KEYID PARENT_KEYID NAME PRIORNAME SALARY LE
---------- ------------ ---------- ---------------- ---------- ----------
1 0 zhangsan 1000000 1
4 1 houzi zhangsan 65000 2
8 4 dujiu houzi 21000 3
7 4 gouba houzi 23000 3
2 1 lisi zhangsan 50500 2
6 2 liuqi lisi 25000 3
5 2 maliu lisi 30000 3
3 1 wangwu zhangsan 60000 2
8 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
实质上，这个等价于：
SQL> select keyid,parent_keyid,name,prior name,salary
2 from player
3 start with parent_keyid=0
4 connect by prior keyid = parent_keyid;
KEYID PARENT_KEYID NAME PRIORNAME SALARY
---------- ------------ ---------- ---------------- ----------
1 0 zhangsan 1000000
2 1 lisi zhangsan 50500
5 2 maliu lisi 30000
6 2 liuqi lisi 25000
3 1 wangwu zhangsan 60000
4 1 houzi zhangsan 65000
7 4 gouba houzi 23000
8 4 dujiu houzi 21000
8 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
注意：在分层查询中，不要指定 ORDER BY 或 GROUP BY，因为它们会覆盖 CONNECT BY 结果的分层顺序。 如果要对同一父级的同级行进行排序，请使用 ORDER SIBLINGS BY 子句。 请参阅 order_by_clause(如上所示)。
第二个：查询以
keyid为2开始的节点的所有子孙节点
SQL> select keyid,parent_keyid,name,prior name,salary
2 from player
3 start with keyid=2
4 connect by prior keyid = parent_keyid;
KEYID PARENT_KEYID NAME PRIORNAME SALARY
---------- ------------ ---------- ---------------- ----------
2 1 lisi 50500
5 2 maliu lisi 30000
6 2 liuqi lisi 25000
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
第三个：查询以
keyid为3开始的节点的所有子孙节点
SQL> select keyid,parent_keyid,name,prior name,salary
2 from player
3 start with keyid=3
4 connect by prior keyid = parent_keyid;
KEYID PARENT_KEYID NAME PRIORNAME SALARY
---------- ------------ ---------- ---------------- ----------
3 1 wangwu 60000
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
从上面三个例子可得：
- 在扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
- 第一步：从根节点(广义上的根)开始
- 第二步：访问该节点
- 第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步
- 第四步：若该节点为根节点，则访问完毕，否则执行第五步
- 第五步：返回到该节点的父节点，并执行第三步骤
我们大家细细地品一下，上述扫描整个树结构的过程就是一个先序遍历树的过程。这一部分内容可以参见本人博客：二叉树的四种遍历方式（递归与非递归实现），点击前往
完整概括一下(Oracle官方文档)：
Oracle对分层查询的处理如下：
- 如果存在join，则首先计算join，无论该join是在FROM子句中指定的还是使用WHERE子句谓词
- 计算CONNECT BY条件
- 计算其余的WHERE子句谓词
然后Oracle使用这些评估的信息来形成层次结构，步骤如下：
- Oracle选择层次结构的根行—那些满足START WITH条件的行
- Oracle选择每个根行的子行。每个子行必须满足一个根行的CONNECT BY条件
- Oracle选择连续的子行。Oracle首先选择步骤2中返回的行的子行，然后再选择这些子行的子行，以此类推。Oracle总是通过对当前父行计算CONNECT by条件来选择子行
- 如果查询包含一个没有连接的WHERE子句，那么Oracle将从层次结构中删除所有不满足WHERE子句条件的行。Oracle对每一行单独计算这个条件，而不是删除不满足条件的行的所有子行
- Oracle按照下图所示的顺序返回行。在图表中，孩子出现在父母的下方
- 为了找到父行的子行，Oracle计算父行CONNECT BY条件的PRIOR表达式和表中每一行的另一个表达式。其中条件为真的行是父行的子行。当然CONNECT BY条件可以包含其他条件，以进一步筛选查询所选择的行
- 如果CONNECT BY条件导致层次结构中出现循环，那么Oracle将返回一个错误。
循环：如果一行同时是另一行的父行(或祖父行或直系祖先行)和子行(或孙子行或直系后代行)，则会发生循环(这个后面详细案例介绍)。
第四个：查询以
keyid为6的所有祖先节点
SQL> select keyid,parent_keyid,name,salary
2 from player
3 start with keyid = 6
4 connect by prior parent_keyid = keyid;
KEYID PARENT_KEYID NAME SALARY
---------- ------------ ---------- ----------
6 2 liuqi 25000
2 1 lisi 50500
1 0 zhangsan 1000000
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
这里的这个就是上面三个的逆过程：
- 找到 keyid = 6 这一行，得到其 parent_keyid=2
- 由 connect by 可得，(从其他行中)继续找 keyid是2的行，找到了 重复1和2
第五个：查询以
keyid为6节点的叔叔伯父节点
SQL> select player.*,prior name,level le
2 from player
3 start with parent_keyid = 0
4 connect by parent_keyid=prior keyid;
KEYID PARENT_KEYID NAME SALARY SEX PRIORNAME LE
---------- ------------ ---------- ---------- ---- ---------------- ----------
1 0 zhangsan 1000000 f 1
2 1 lisi 50500 m zhangsan 2
5 2 maliu 30000 f lisi 3
6 2 liuqi 25000 m lisi 3
3 1 wangwu 60000 m zhangsan 2
4 1 houzi 65000 m zhangsan 2
7 4 gouba 23000 m houzi 3
8 4 dujiu 21000 f houzi 3
8 rows selected.
SQL>
SQL> with t as (
2 select player.*,prior name,level le
3 from player
4 start with parent_keyid = 0
5 connect by parent_keyid=prior keyid)
6 select t.*
7 from t
8 left join t tt on tt.keyid=6
9 where t.le = (tt.le-1) #找到上一层
10 and t.keyid not in (tt.parent_keyid);
KEYID PARENT_KEYID NAME SALARY SEX PRIORNAME LE
---------- ------------ ---------- ---------- ---- ---------------- ----------
4 1 houzi 65000 m zhangsan 2
3 1 wangwu 60000 m zhangsan 2
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
第六个：查询以
keyid为6节点的族兄节点
SQL> with t as (
2 select player.*,prior name,level le
3 from player
4 start with parent_keyid=0
5 connect by parent_keyid= prior keyid
6 )
7 select t.*
8 from t t
9 left join t tt on tt.keyid=6
10 where t.le=tt.le and t.keyid<>6; # 同一层
KEYID PARENT_KEYID NAME SALARY SEX PRIORNAME LE
---------- ------------ ---------- ---------- ---- ---------------- ----------
8 4 dujiu 21000 f houzi 3
7 4 gouba 23000 m houzi 3
5 2 maliu 30000 f lisi 3
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
第七个：
level伪列的使用，格式化层级
SQL> select player.*,prior name,level le
2 from player
3 start with parent_keyid = 0
4 connect by parent_keyid=prior keyid;
KEYID PARENT_KEYID NAME SALARY SEX PRIORNAME LE
---------- ------------ ---------- ---------- ---- ---------------- ----------
1 0 zhangsan 1000000 f 1
2 1 lisi 50500 m zhangsan 2
5 2 maliu 30000 f lisi 3
6 2 liuqi 25000 m lisi 3
3 1 wangwu 60000 m zhangsan 2
4 1 houzi 65000 m zhangsan 2
7 4 gouba 23000 m houzi 3
8 4 dujiu 21000 f houzi 3
8 rows selected.
SQL>
SQL> column name format a30
SQL> select lpad(' ',level*3,' ')||name as name,keyid,parent_keyid,level
2 from player
3 start with parent_keyid = 0
4 connect by prior keyid = parent_keyid;
NAME KEYID PARENT_KEYID LEVEL
------------------------------ ---------- ------------ ----------
zhangsan 1 0 1
lisi 2 1 2
maliu 5 2 3
liuqi 6 2 3
wangwu 3 1 2
houzi 4 1 2
gouba 7 4 3
dujiu 8 4 3
8 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
第八个：
CONNECT_BY_ROOT的使用
CONNECT_BY_ROOT操作的功能就是获取根节点记录的字段信息
SQL> select connect_by_root keyid as root_emp_id,connect_by_root name as root_emp_name, lpad(' ',level*3,' ')||name as name, keyid,parent_keyid,salary,level
2 from player
3 start with parent_keyid = 0
4 connect by prior keyid = parent_keyid;
ROOT_EMP_ID ROOT_EMP_NAME NAME KEYID PARENT_KEYID SALARY LEVEL
----------- ---------------- ------------------------------ ---------- ------------ ---------- ----------
1 zhangsan zhangsan 1 0 1000000 1
1 zhangsan lisi 2 1 50500 2
1 zhangsan maliu 5 2 30000 3
1 zhangsan liuqi 6 2 25000 3
1 zhangsan wangwu 3 1 60000 2
1 zhangsan houzi 4 1 65000 2
1 zhangsan gouba 7 4 23000 3
1 zhangsan dujiu 8 4 21000 3
8 rows selected.
SQL> select connect_by_root keyid as root_emp_id,connect_by_root name as root_emp_name, lpad(' ',level*3,' ')||name as name, keyid,parent_keyid,salary,level
2 from player where 1=1 and salary > 50000
3 start with parent_keyid = 0
4 connect by prior keyid = parent_keyid;
ROOT_EMP_ID ROOT_EMP_NAME NAME KEYID PARENT_KEYID SALARY LEVEL
----------- ---------------- ------------------------------ ---------- ------------ ---------- ----------
1 zhangsan zhangsan 1 0 1000000 1
1 zhangsan lisi 2 1 50500 2
1 zhangsan wangwu 3 1 60000 2
1 zhangsan houzi 4 1 65000 2
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
这里说的根仍旧是广义上的root，示例如下：
SQL> select connect_by_root keyid as root_emp_id,connect_by_root name as root_emp_name, lpad(' ',level*3,' ')||name as name, keyid,parent_keyid,salary,level
2 from player
3 start with keyid = 2
4 connect by prior keyid = parent_keyid;
ROOT_EMP_ID ROOT_EMP_NAME NAME KEYID PARENT_KEYID SALARY LEVEL
----------- ---------------- ------------------------------ ---------- ------------ ---------- ----------
2 lisi lisi 2 1 50500 1
2 lisi maliu 5 2 30000 2
2 lisi liuqi 6 2 25000 2
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
CONNECT BY条件和PRIOR表达式都可以采用不相关子查询的形式。但是CURRVAL和NEXTVAL不是有效的PRIOR表达式，因此PRIOR表达式不能引用序列。
通过使用CONNECT_BY_ROOT操作符来限定选择列表中的列，可以进一步细化层次查询。这个操作符扩展了层次结构查询的CONNECT BY [PRIOR]条件的功能，(通过)不仅返回层次结构中的直接父行，而且返回层次结构中的所有祖先行(的方式)。
SQL> column name format a20
SQL> select connect_by_root keyid as root_emp_id,connect_by_root name as root_emp_name, lpad(' ',level*3,' ')||name as name, keyid,parent_keyid,salary,level
2 from player
3 start with keyid = 6
4 connect by prior parent_keyid = keyid;
ROOT_EMP_ID ROOT_EMP_NAME NAME KEYID PARENT_KEYID SALARY LEVEL
----------- ---------------- -------------------- ---------- ------------ ---------- ----------
6 liuqi liuqi 6 2 25000 1
6 liuqi lisi 2 1 50500 2
6 liuqi zhangsan 1 0 1000000 3
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
第九个：
CONNECT_BY_ISLEAF是否是叶子节点
通过这个伪列，可以判断当前的记录是否是树的叶节点
SQL> select p.*,level,CONNECT_BY_ISLEAF
2 from player p
3 start with parent_keyid = 0
4 connect by prior keyid = parent_keyid;
KEYID PARENT_KEYID NAME SALARY SEX LEVEL CONNECT_BY_ISLEAF
---------- ------------ ------------------------------ ---------- ---- ---------- -----------------
1 0 zhangsan 1000000 f 1 0
2 1 lisi 50500 m 2 0
5 2 maliu 30000 f 3 1
6 2 liuqi 25000 m 3 1
3 1 wangwu 60000 m 2 1
4 1 houzi 65000 m 2 0
7 4 gouba 23000 m 3 1
8 4 dujiu 21000 f 3 1
8 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
注：CONNECT_BY_ISCYCLE 前置条件：在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是。(这里关于 树形成环 的问题下面详谈)
Connect By的总结
树形结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如player表中的keyid,parent_keyid。在表的每一行中都有一个表示父节点的parent_keyid（除根节点外），指向每个节点的父节点，就可以确定整个树结构。
SELECT语句中的CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在的一侧表示父节点，另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
START WITH： 不但可以指定一个根节点，还可以指定多个根节点。
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
- 当PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
connect by prior keyid = parent_keyid - 当PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
connect by prior parent_keyid = keyid
在这种方式中也应指定一个开始的节点。
PRIOR是一元运算符，与一元算术运算符+和-具有相同的优先级；它计算分层查询中当前行的父行的紧接表达式。
PRIOR在用相等操作符比较列值时最常用。(PRIOR关键字可以在操作符的任意一侧)
PRIOR会导致Oracle使用列中父行的值。在CONNECT BY子句中，除了等号(=)以外的操作符理论上是可能的。然而，由这些其他操作符创建的条件可能导致通过可能的组合形成一个无限循环。在这种情况下，Oracle在运行时检测到循环并返回一个错误。
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
像在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。
我们这里先构造一个内含循环的树形结果来探讨一下该问题，如下：
SQL> select * from player;
KEYID PARENT_KEYID NAME SALARY SEX
---------- ------------ ---------- ---------- ----
1 0 zhangsan 1000000 f
2 1 lisi 50500 m
3 1 wangwu 60000 m
4 1 houzi 65000 m
5 2 maliu 30000 f
6 2 liuqi 25000 m
7 4 gouba 23000 m
8 4 dujiu 21000 f
4 8 xunhuan 22222 m
9 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
示例如下：
SQL> select keyid,parent_keyid,name,prior name,salary,LEVEL le
2 from player
3 start with keyid=1
4 connect by prior keyid = parent_keyid;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
在这样的情况下，需要使用NOCYCLE 参数， CONNECT BY 条件中的 NOCYCLE 参数使 Oracle 不顾循环而返回行。CONNECT_BY_ISCYCLE 伪列显示哪些行包含循环：
SQL> select keyid,parent_keyid,name,prior name,salary,LEVEL le,CONNECT_BY_ISCYCLE "Cycle"
2 from player
3 start with keyid=1
4 connect by NOCYCLE prior keyid = parent_keyid;
KEYID PARENT_KEYID NAME PRIORNAME SALARY LE Cycle
---------- ------------ ---------- ---------------- ---------- ---------- ----------
1 0 zhangsan 1000000 1 0
2 1 lisi zhangsan 50500 2 0
5 2 maliu lisi 30000 3 0
6 2 liuqi lisi 25000 3 0
3 1 wangwu zhangsan 60000 2 0
4 1 houzi zhangsan 65000 2 0
7 4 gouba houzi 23000 3 0
8 4 dujiu houzi 21000 3 1
8 rows selected.
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
