没看懂 connect by 是怎么循环的

oracle SQL查询时提示 用户数据中的connect by 循环 报错是什么原因?~

一般是数据错误导致了死循环。
如数据为这样：
ID 父ID 值
1 2 10
2 1 20

如图，ID为1的父ID为2，而同时ID为2的父ID是1，这样的话，就会互相认对方的ID为父ID，就会造成一个死循环，这种错误，一般不用修改语句，需要正确检查数据的正确性。

connect by是层次查询子句，rownum是行数。整个语句就是用来生成1到10的序列
查询结果应该是：
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10

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.

在实际开发中 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
.

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)
);

---

舒兰市15565681735： 没看懂 connect by 是怎么循环的 - ？
恭庄韦安： Oracle中connect by...start with...的使用 一、语法 大致写法:select * from some_table [where 条件1] connect by [条件2] start with [条件3]; 其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果,[where 条件1]可以不需要. [where 条...

舒兰市15565681735： Oracle计算周数的几种方式 - ？
恭庄韦安： 比如星期一到星期天算一周 查2011年7月完整周数 select max(count1) - 1 from (SELECT count(1) count1 FROM DUAL WHERE TO_CHAR(ADD_MONTHS(LAST_DAY(to_date('201107', 'yyyyMM')), -1) + LEVEL, 'DAY') = '星期一' CONNECT BY LEVEL

舒兰市15565681735： 在PL/SQL中,Select Null From Dual Connect By Rownum 具体是什么意思,getstr和str怎么用 - ？
恭庄韦安： Connect By Rownum ???好像不能这样用的,CONNECT BY 后面需要跟一个条件,可以修改为 Select NULL From Dual Connect By Rownum 这句话的意思是查询出4条记录,而每条记录的结果都是NULL,至于说具体这样做的目的就需要看看具体的应用场景了,有时候,为了初始化多行记录,而对于来列的值允许为空的时候,就可以使用这种方式.另外,PL/SQL中没有看到getstr和str的函数,不知道楼主想用这个做什么?

舒兰市15565681735： oracle SQL查询时提示 用户数据中的connect by 循环 报错是什么原因? - ？
恭庄韦安： 估计是你数据譬如1的parent是2,然后2的parent又指向了1,造成死循环了.

舒兰市15565681735： 数据库ground by.感觉好复杂.不是不懂.而是感觉抓不住核心的地方.对于分组的前后顺序搞不懂 - ？
恭庄韦安： ground by就是对关系表中的元组,基于其后面的属性对应的属性值进行分组,具有相同属性值的记录放在一个组里,然后对应的select语句是对每个分组上对其它属性进行相关的统计,比如求和、平均、计数、最大值、最小值.查询后得到的结果是每个分组对应一个记录.如果需要对分组过滤的话,使用的是having子句.

舒兰市15565681735： start with,connect by prior原理详解 - ？
恭庄韦安： 你这个表可以用id 和parent_id这俩字段组出树状结构 start with id = 999 表示从节点999开始 connect by prior id=parent_id 表示999这个节点往上/前 查找树分支 http://jingyan.baidu.com/article/5d368d1e182bb93f60c05784.html

舒兰市15565681735： ORCALE中怎么实现替换递增? - ？
恭庄韦安： 很简单啊,可以测试下 select replace(sys_connect_by_path(level||字段,'/'),'/') as res fromtable start with...connect by ....

舒兰市15565681735： SQL连接查询 - ？
恭庄韦安： 建议你还是做一个函数吧,函数把prize传进去,逐个的获取对应的奖励名再输出.比如select name, sf_get_prizename(prize) from 表a sf_get_prizename函数里面,对于输入参数,从前往后定位';',遇到了,截取字符,到表B取名字.做一个循...

舒兰市15565681735： 子节点的编号是英文mysql怎么实现oracle start with connnect by - ？
恭庄韦安： 第一篇文章,从某个同行问我的问题开始.他的问题大概是这样的:自己已经用Oracle的Start with...Connect By实现了树的递归查询,但是现在要求变了,要用MYSQL也实现相同的递归查询树的功能.这个功能是我从以前从未使用到过的,于是...

舒兰市15565681735： 求生之路怎么联机啊 ,气死我了,到现在还不知道怎么联机,谁能告诉我,注意不是第二部是求生之路 - ？
恭庄韦安： 1.局域网联机 如果是在同一个网吧 先点屏幕左下方 开始 运行 输入 ipconfig 找到ip一项 记住后面的ip地址 接着进入游戏 按TAB上面的“~”键呼出控制台 然后打map+空格 上下选择地图 回车开始 让你的朋友也进入游戏 呼出控制台 输入connect ...