在处理树形结构数据时,需要使用到递归查询。Oracle提供了一个比较方便的函数,那就是Connect by。根据具体实践,这里主要将该函数的使用心得记录如下。
一、语法
connect by用法 | Connect by用于表示树形结构或者递归关系,数据行之间存在父子关系。数据结构中,一个字段与另一个字段之间互为上下级关系(或称为父与子关系)。比如,一个字段为ID,另一个字段为Fid,Fid表示为Id的上级。 |
---|---|
START WITH condition1 | 指定起始节点条件。主要取值于ID列或Fid列 ,可以是单个值,也可以j该列数据的集合,也可以是条件或表达式,决定了结果树的大小和分支。START WITH子句也可能省略。省略时,会把表数据遍历一遍,相当于每一个数据做一次根结点,然后遍历树中其他节点,此时其作用相当于START WITH IN( ID或FID的去除重复行后的结果集) 举例:start with fid in( );start with fid is null; start with fid=0。 |
CONNECT BY PRIOR | 指定父子行的条件关系,在递归查询中是不可缺的必选项目。其中,Prior关键字是查询父行的限定行。Prior在字段的哪边,则哪边字段为当前行,另一字段为下一行匹配的字段名。Start WITH和Connect by位置可互换。 |
ORDEY SIBLINGS BY | 保护层次结构,并在每个层级不按EXPRESS排序。 |
LEVEL | Oracle 数据库中的伪列,用于表示当前数据层级关系,必须结合Connect by使用。需要说明的是,Level为相对层级。不论start with的起始节点从哪里开始,该起始节点的层号始终为1,起始节点即为根节点。 具体使用时,可结合lpad函数,对相关数据项进行格式化,以展示树形结构。如: lpad(’ ',(level-1)*4) |
Connect_by_root | 取当前行顶层根节点,使当前行与其顶层根节点逐一对应。 |
Connect_by_isleaf | 用于表示当前行是否是叶子节点,0表示否,即不是叶子节点而是分支;1表示是,即该节点是叶子节点。 |
Nocycle | 若数据表中存在循环节,不添加nocyle关键字会报错。添加该关键字后,便不会报错,但循环的两行只会显示其中的第一条循环行。 添加时,需添加在connect by子句:如 |
Connect_by_iscycle | 在使用了Nocycle之后,可以在select子句中使用connect_by_iscycle关键字,且于标记当前行是否为循环行。0表示否,1表示 是。 |
二、常见用法例举
在学习笔记1中,我们使用了两种不同用法。一种是展示树形结构;一种是作了遍历,每个数据都做了一次根节点,主要是方便逐层向上统计数据。
(一)展示树形层级结构
SELECT T.SWJG_ID ID,
LPAD(' ', (LEVEL - 1) * 4) || T.SWJG_NAME SWJGMC, --格式化显示结果。
T.SJSWJG_ID FID,
LEVEL LEVEL_ID, --层级代码。
CONNECT_BY_ISLEAF ISLEAF, --是否叶子节点。
ROWNUM RN--增加序号。
FROM TAB4_SWJG T
START WITH T.SWJG_ID