ORA-01436 与 层次查询CONNECT BY

一、 ORA-01436报错

开发遇到一个报错 ORA-01436: CONNECT BY loop in user data （ORA-01436: 用户数据中的 CONNECT BY 循环）。

1. 报错原因

根据网上的资料，产生这个错误的原因是数据形成了循环。例如下面这个语句：

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;

如果数据在region_id为1的parent_id为24684，而region_id为24684的parent_id为1，这样就会产生了循环。

2. 解决办法

• 使用 connect by nocycle，不推荐，该方法只是可以不报错，但很可能得不到正确的结果。
• 修改基础数据

二、 层次查询CONNECT BY

connect by主要用于父子，祖孙，上下级等层级关系的查询。

1. 语法

有两种写法

CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
-- 或者
START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...

解释：

• start with：指定起始节点的条件
• connect by：指定父子行的条件关系
• prior：查询父行的限定符，格式: prior column1 = column2 或者 column1 = prior column2，这两种写法取得的数据刚好是相反的
• level：伪列，表示层级，值越小层级越高，level=1为层级最高节点
• nocycle：若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条。
• connect_by_iscycle：在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1表示是
• connect_by_isleaf：是否是叶子节点，0表示否，1表示是

以下是一个简单的例子

create table employee(
       emp_id number(18),
       lead_id number(18),
       emp_name varchar2(200),
       salary number(10,2),
       dept_no varchar2(8)
);

insert into employee values('1',0,'king','1000000.00','001');
insert into employee values('2',1,'jack','50500.00','002');
insert into employee values('3',1,'arise','60000.00','003');
insert into employee values('4',2,'scott','30000.00','002');
insert into employee values('5',2,'tiger','25000.00','002');
insert into employee values('6',3,'wudde','23000.00','003');
insert into employee values('7',3,'joker','21000.00','003');
commit;

2. connect by prior的用法

① connect by prior 子节点ID = 父节点ID，找出其所有子节点

记住这个结论：prior后是子节点id就找其所有子节点，是父节点id就找其所有祖先节点。

select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
from employee
start with  emp_id=1
connect by prior emp_id = lead_id
order by lead_id;

首先根据结论，因为是prior emp_id，所以该sql含义是找出emp_id=1职员的所有下属（子节点）

我们具体来看为什么：

prior指的是前一个值（最开始就是start with的条件查出的值），以上sql的执行步骤为：

• start with emp_id=1 -> 谁的 lead_id=1，它们的emp_id是多少？（prior emp_id = lead_id）
• 查出emp_id=2和3 -> 谁的 lead_id=2和3，它们的emp_id是多少 ？
• 查出 emp_id=4,5,6,7 -> 谁的 lead_id=4,5,6,7，它们的emp_id是多少 ？
• 发现没有了，查询结束

因此按照逻辑，该sql含义还是：找出emp_id=1职员的所有下属（子节点）

② connect by 子节点ID = prior 父节点ID，找出其所有祖先节点

select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
from employee
start with  emp_id=6
connect by emp_id = prior lead_id
order by lead_id;

       这刚好跟上面相反，因为是prior lead_id，所以该sql含义是找出emp_id=6职员的所有上级（祖先节点）

prior指的是前一个值（最开始就是start with的条件查出的值）：

• emp_id=6的那位，你的leader是谁？ -> 查出 lead_id=3，所以它leader是员工3
• emp_id=3的那位 ，你的leader是谁？-> 查出 lead_id=1，所以它leader是员工1
• emp_id=1的那位 ，你的leader是谁？-> 查出 lead_id=0
• 因为实际上没有emp_id=0的，所以查询结束了

因此按照逻辑，该sql含义还是：找出emp_id=6职员的所有上级（祖先节点）

③ 查询一个节点的叔伯节点

with t as (
select employee.*,prior emp_name,level le
from employee
start with  emp_id=1
connect by prior emp_id = lead_id
)
select *
from t
left join t tt on tt.emp_id=6
where t.le = (tt.le-1)
and t.emp_id not in (tt.lead_id);

看起来不太好懂这样写条件什么意思，其实画一下就很好理解了

条件	t			tt			条件
	emp_id	lead_id	level	emp_id	lead_id	level
	1	0	1	1	0	1
t.emp_id not in (tt.lead_id)	3	1	2	3	1	2
t.le = (tt.le-1)	2	1	2	2	1	2
	4	2	3	4	2	3
	5	2	3	5	2	3
	6	3	3	6	3	3	tt.emp_id=6
	7	3	3	7	3	3

查询结果，就是emp_id=2的记录

④ 查询族兄

with t as (
select employee.*,prior emp_name,level le
from employee
start with emp_id=1
connect by prior emp_id=lead_id
)
select t.*
from t t
left join t tt on tt.emp_id=6
where t.le=tt.le and t.emp_id<>6;

这比刚才那个好理解，就是查跟自己同一等级的，t.emp_id<>6是为了避免查到自己

条件	t			tt			条件
	emp_id	lead_id	level	emp_id	lead_id	level
	1	0	1	1	0	1
	3	1	2	3	1	2
	2	1	2	2	1	2
t.le = tt.le	4	2	3	4	2	3
	5	2	3	5	2	3
t.emp_id<>6	6	3	3	6	3	3	tt.emp_id=6
	7	3	3	7	3	3

查询结果

⑤ level伪列的使用，格式化层级

select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level
       from employee
       start with emp_id=1
       connect by prior emp_id=lead_id;

⑥ connect_by_root 查找根节点

select connect_by_root emp_name,emp_name,lead_id,salary
       from employee  
       start with lead_id=1
       connect by prior emp_id = lead_id;

⑦ connect_by_iscycle 标注循环行

connect_by_iscycle必须要与nocycle一起用，否则会报错。

-- 插入一条数据，与另一条emp_id=7的数据组成循环行
insert into employee values('3',7,'joker_cycle','21000.00','003');
commit;

此时有了循环行，如果不加nocycle，就会遇到最开始的 ORA-01436 错误。

select emp_id,emp_name,lead_id,salary
from employee 
start with lead_id=0
connect by prior emp_id = lead_id;

添加nocycle后能查出，但结果不一定符合业务需求。

-- connect_by_iscycle("CYCLE"), connect by nocycle
select emp_id,emp_name,lead_id,salary,connect_by_iscycle as cycle 
from employee 
start with lead_id=0
connect by nocycle prior emp_id = lead_id;

⑧ connect_by_isleaf 判断是否为叶子节点

select emp_id,emp_name,lead_id,salary,connect_by_isleaf
from employee
start with lead_id=0
connect by nocycle prior emp_id=lead_id;

三、 高级用法

下面这些有点像算法题，有它的写法套路，了解了解，真遇到了照搬写法就行。

1. 范围展开

with tmp as
(select 2 as id1, 5 as id2 from dual
union all
select 8,10 from dual
) select id1,id2, id1+level-1 from tmp
connect by level<=id2-id1+1
and prior id1=id1
and prior dbms_random.value() is not null;

 

2. 拆分单个字符串

单字符串拆分成多行

var b1 varchar2(4000);
exec :b1:='100,101,102,103,104,105';

select REGEXP_SUBSTR( :b1,'[^,]+', 1, level) as value
from dual
connect by level <= regexp_count(:b1, '[^,]+');

3. 拆分表字段字符串为多行

 

with tmp as
(select 1 as id,'c,b,a' as name from dual
union all
select 2,'d,e,f' from dual
union all
select 3,'h,g' from dual
)
select id, REGEXP_SUBSTR( name,'[^,]+', 1, level) as value
from tmp
connect by level <= regexp_count(name, '[^,]+')
and prior id=id
and prior dbms_random.value() is not null
order by id,value;

4. connect by与半连接

非常少有的in和exists性能不相等，建议in与connect by结合使用

 

参考

ORA-01436: 用户数据中的 CONNECT BY 循环_launch_225的博客-CSDN博客

oracle的start with connect by prior如何使用 - 李润 - 博客园

oracle之connect by的用法_W_DongQiang的博客-CSDN博客

tiger liu《SQL写法与改写-第二期》