没看懂 connect by 是怎么循环的

Question

看到别人写的 connect by 的一个例子，但是没有看明白是怎么循环的，还请各位大虾指点一下，例子如下：
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 <= 2

结果：
level chr other
1 aa aa
2 aa ---- aa
2 bb ---- bb
1 bb bb
2 aa ---- aa
2 bb ---- bb

Answer 1

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）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。
因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况
--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.

SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 0 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn

13 rows selected.

--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id;

LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn

--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 47000

8 rows selected.

Answer 2

在实际开发中 connect by可以替代plsql或java中的循环间化开发代码，提高开发效率。如下是我在工作中遇到一个实际解决的问题
具体场景：
原系统是一个管理宿舍信息的系统,dorm_room用于存放宿舍的房间信息具体表结构是
create table dorm_room(bno varchar2(2),fno varchar2(2),rno varchar2(2),bednum varchar2(2));
这里存放了楼栋号，层号，房间号，及房间中床位总数
具体数据如下
insert into dorm_room values(1,1,1,4);
insert into dorm_room values(1,1,2,4);
insert into dorm_room values(1,1,3,7);
也就是说有三个房间，前两个房间床位数都是4，最后一个房间的床位数是7
目前的需求是根据床位数显示出每个房间中所有床位的数据。期望结果是
1 1 1 1
1 1 1 2
1 1 1 3
1 1 1 4
1 1 2 1
1 1 2 2
1 1 2 3
1 1 2 4
1 1 3 1
1 1 3 2
1 1 3 3
1 1 3 4
1 1 3 5
1 1 3 6
1 1 3 7
如果用pl/sql根据每行的床位数判断循环次数，显示出每个房间的所有床位数，编写起来必然要书写较多行代码，因此选用
sql中的connect by的方式生成出所有的房间的床位数，具体sql为
select distinct bno,fno,rno,t.l
from (
select row_number()over(partition by bno,fno,rno order by level) rn,
bno,fno,rno,level l
from dorm_room
connect by level<=bedno
) t
order by bno,fno,rno
显示结果
BN FN RN L

1 1 1 1
1 1 1 2
1 1 1 3
1 1 1 4
1 1 2 1
1 1 2 2
1 1 2 3
1 1 2 4
1 1 3 1
1 1 3 2
1 1 3 3
BN FN RN L

1 1 3 4
1 1 3 5
1 1 3 6
1 1 3 7
.

Answer 3

SELECT r1.region_id,parent_id
FROM cnl_region r1
WHERE r1.region_id =1 START WITH r1.region_id = 1
CONNECT BY PRIOR r1.region_id = r1.parent_id
在运行上面的SQL语句时它会报ORA-01436的错误，报这个错误原因是因为产生了循环。后来查找我的数据发现，数据region_id为1的parent_id为24684,而region_id为24684的parent_id为1，这样就产生了循环后来我把数据改过来就运行正常了。
不改变数据将下面的SQL语句改为让它不产生循环
SELECT r1.region_id,parent_id
FROM cnl_region r1
WHERE r1.region_id =1 START WITH r1.region_id = 1
CONNECT BY NOCYCLE PRIOR r1.region_id = r1.parent_id
SQL code
--start with ... connect by 层次查询的用法
--建表语句
create table automobiles(
part_id number(5)
constraint pk_auto_part_id primary key,
parent_id number(5)
constraint fk_auto_ppid_ references automobiles(part_id),
part_cname varchar2(30) not null,
part_ename varchar2(30) not null,
mp_cost number(9,2),
desribe varchar2(20)
);