Oracle connect by..start with/level

最新推荐文章于 2024-08-14 17:23:48 发布

aaa_dai

最新推荐文章于 2024-08-14 17:23:48 发布

阅读量614

点赞数

分类专栏： Database DataBase 文章标签： Oracle connect by..start wi level

本文链接：https://blog.csdn.net/sgs595595/article/details/50924347

版权

Database 同时被 2 个专栏收录

48 篇文章 0 订阅

订阅专栏

DataBase

47 篇文章 0 订阅

订阅专栏

--connect by..level

--表行数 r,查询到层数 l,则查询结果集行数 R = r^l+r^(l-1)+...r^2+r.

--特别的行数为2时, 结果集行数 R = 2^(l+1) - 2;

-- level  表示  目前递归的 层次。
with x As (select 'aa' Chr from dual union All select 'bb' chr from dual)
select level, chr, lpad('*', (level - 1) * 5, '-') || chr  other from x connect by level <= 3;
     

1   1  aa  aa
2   2  aa  ----*aa
3   3  aa  ---------*aa
4   3  bb  ---------*bb
5   2  bb  ----*bb
6	3	aa	---------*aa
7	3	bb	---------*bb
8	1	bb	bb
9	2	aa	----*aa
10	3	aa	---------*aa
11	3	bb	---------*bb
12	2	bb	----*bb
13	3	aa	---------*aa
14	3	bb	---------*bb

--connect by..start with
--以M为根节点的所有树形结构
with t as  (
select 'A' parent1, 'B' child1 from  dual union all
select 'B' , 'C' from  dual union all
select 'B' , 'D' from  dual union all
select 'D' , 'E' from  dual union all
select 'C' , 'F' from  dual union all
select 'M' , 'B' from  dual union all
select 'M' , 'L' from  dual union all
select 'M' , 'N' from  dual union all
select 'L' , 'Z' from  dual union all
select 'Z' , 'N' from  dual union all
select 'N' , 'S' from  dual 
)
select t.*,level,connect_by_isleaf ,connect_by_rootparent1  from  t
start with t.parent1='M'
connect by prior t.child1=t.parent1
1	M	B	1	0	M
2	B	C	2	0	M
3	C	F	3	1	M
4	B	D	2	0	M
5	D	E	3	1	M
6	M	L	1	0	M
7	L	Z	2	0	M
8	Z	N	3	0	M
9	N	S	4	1	M
10	M	N	1	0	M
11	N	S	2	1	M

Oracle中connect by...start with...的使用

一、语法 

大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3]; 

其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。 

[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同： 

[where 条件1]是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构； 

[条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉； 

[条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件； 

示例： 

假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。 

select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66; 

对prior的说明： 

    prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。 要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一 种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。 

二、执行原理 

connect by...start with...的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明： 

/* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条， 

如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点， 

如此循环直到遍历完整个表的所有记录 。*/ 

for rec in (select * from some_table) loop 

if FULLFILLS_START_WITH_CONDITION(rec) then 

    RECURSE(rec, rec.child); 

end if; 

end loop; 

/* 寻找子节点的存储过程*/ 

procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is 

begin 

APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/ 

/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条， 

如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点， 

如此循环直到找至叶子节点。*/ 

for rec_recurse in (select * from some_table) loop 

    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then 

      RECURSE(rec_recurse,rec_recurse.child); 

    end if; 

end loop; 

end procedure RECURSE; 

三、使用探讨 

    从上面的执行原理可以看到“connect by...start with...”构造树的方式是：（1）如果是自上而下方式，则把表中的每一 条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分 支，所以表中有多少条记录就会生成多少条分支。 

    因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。