Oracle Start With关键字分析

176次阅读

没有评论

共计 2933 个字符，预计需要花费 8 分钟才能阅读完成。

前言

旨在记录一些 Oracle 使用中遇到的各种各样的问题. 同时希望能帮到和我遇到同样问题的人.

Start With (树查询)

问题描述:

在数据库中, 有一种比较常见得设计模式, 层级结构设计模式, 具体到 Oracle table 中, 字段特点如下:

ID, DSC, PID;

三个字段, 分别表示当前标识的 ID(主键), DSC 当前标识的描述, PID 其父级 ID, 比较典型的例子是国家, 省, 市这种层级结构;

省份归属于国家, 因此 PID 为国家的 ID, 以此类推;

create table DEMO (ID varchar2(10) primary key,
    DSC varchar2(100),
    PID varchar2(10)
)
-- 插入几条数据 

Insert Into DEMO values ('00001', '中国', '-1');
Insert Into DEMO values ('00011', '陕西', '00001');
Insert Into DEMO values ('00012', '贵州', '00001');
Insert Into DEMO values ('00013', '河南', '00001');
Insert Into DEMO values ('00111', '西安', '00011');
Insert Into DEMO values ('00112', '咸阳', '00011');
Insert Into DEMO values ('00113', '延安', '00011');

这样子就成了一个简单的树级结构, 我一般将根节点的 PID 定为 -1;

Start With:

基本语法如下:

SELECT ... FROM    + 表名
WHERE              + 条件 3
START WITH         + 条件 1
CONNECT BY PRIOR   + 条件 2

-- 示例 
Select * From DEMO
Start With ID = '00001'
Connect By Prior ID = PID

条件 1: 表示从哪个节点开始查找, 也就是通过条件 1 查询到的数据, 作为后续查询的起始节点 (参数).

当然可以放宽限定条件，如 ID in (‘00001’, ‘00011’) 以取得多个根节点，也就是多棵树；在连接关系中，除了可以使用列明外，还允许使用列表达式。

如果省略 Start With

就默认把所有满足查询条件的 Tree 整个表中的数据从头到尾遍历一次, 每一个数据做一次根, 然后遍历树中其他节点信息.

条件 2: 是连接条件，其中用 PRIOR 表示上一条记录，例如 CONNECT BY PRIOR ID = PID，意思就是上一条记录的 ID 是本条记录的 PID，即本记录的父亲是上一条记录。CONNECT BY 子句说明每行数据将是按照层次顺序检索，并规定将表中的数据连入树形结构的关系中。

Prior 在父节点的一侧表示, 自底向上查, 在子节点的一侧表示自上向下查询;

条件 3: 不能用在 Connect By 后, 这里的条件判断, 等价于在最后查询出结果列表之后, 再进行条件筛选; 并非删除掉节点及子节点;

-- 自底向上 
Select * From DEMO
Start With ID = '00113'
Connect By  Prior PID = ID

-- 结果 
00113   延安  00011
00011   陕西  00001
00001   中国  -1

-- 自上向下 
Select * From DEMO
Start With ID = '00001'
-- 用 Start Wiht PID = '-1' 结果不变 
Connect By  Prior ID = PID

-- 结果 
00001   中国  -1
00011   陕西  00001
00111   西安  00011
00112   咸阳  00011
00113   延安  00011
00012   贵州  00001
00013   河南  00001

--Where 删除 
Select ID, PID, DSC
From DEMO
WHERE ID <> '00011'
Start With ID = '00001'
Connect By Prior ID =  PID

-- 结果 
00001   -1      中国
00111   00011   西安
00112   00011   咸阳
00113   00011   延安
00012   00001   贵州
00013   00001   河南

下面是几条关键字特殊点:

nocycle 关键字, 有时候数据本身不合理会导致出现循环的问题, 如将上述的 ID ‘00001’ 记录的 ‘PID’ 也改为 ‘00001’, 会出现循环的问题, 这是, 需要用到 nocycle 即可消除循环;

Connect By nocycle Prior ID = PID 即可.

connect_by_isleaf 表示当前节点是否是叶子节点

level 表示当前节点所处层级, 这里的层级指的是从 start with 查询到的节点开始往下算起, 当前属于第几层级

Select ID, PID, DSC,
connect_by_isleaf isLeaf,
LEVEL
From DEMO
Connect By nocycle Prior ID = PID
Start With ID = '00001';

-- 结果 
ID      PID     DSC  isLeaf     LEVEL
00001   00001   中国  0           0
00011   00001   陕西  0           1
00111   00011   西安  1           2
00112   00011   咸阳  1           2
00113   00011   延安  1           2
00012   00001   贵州  1           1
00013   00001   河南  1           1

这里需要注意的一个点, 如果采用的是自底向上的方式查询, 则 LEVEL 的层级同样是从底向上, 如 00113 LEVEL 1 00011 LEVEL 2 00001 LEVEL 3.

另外一点: 如果在查询语句中 Select ID, PID, DSC, connect_by_isleaf isLeaf, LEVEL – 1 LEVEL 这种查询方式的话, 在 WHERE 判断条件中, 只需要判断 LEVEL = 1, 就可以取出当前查询节点的子节点 (由于 LEVEL 也是伪列, 需要用子查询的方式);

SIBLINGS 关键字: 它会保护层次，并且在每个等级中按 expre 排序。

Select ID, PID, DSC,
connect_by_isleaf,
LEVEL
From DEMO
Start With ID = '00001'
Connect By nocycle Prior ID =  PID
ORDER By DSC

-- 结果, 仅贴出部分数据 (层级结构被破坏了)
00012   00001   贵州  1   2
00013   00001   河南  1   2
00011   00001   陕西  0   2
00111   00011   西安  1   3
00112   00011   咸阳  1   3
00113   00011   延安  1   3
00001   -1      中国  0   1

--ORDER SIBLINGS  By DSC
Select ID, PID, DSC,
connect_by_isleaf,
LEVEL
From DEMO
Start With ID = '00001'
Connect By nocycle Prior ID =  PID
ORDER SIBLINGS  By DSC

-- 结果 (Level 层级不变)
00001   -1      中国  0   1
00012   00001   贵州  1   2
00013   00001   河南  1   2
00011   00001   陕西  0   2
00111   00011   西安  1   3
00112   00011   咸阳  1   3
00113   00011   延安  1   3