start with ... connect by 层次查询的用法报错：ORA-01436: CONNECT BY loop in user data

Question

--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)     
);
--插入数据
insert into automobiles values( 1,1,'汽车','mobile',84321.99,'Assembly');
insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture');
insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase');
insert into automobiles values( 4,1,'附件','attached',15212,'Assembly');
insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase');
insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture');
insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture');
insert into automobiles values( 8,6,'车轮','Wheel',2062.2,'Manufacture');
insert into automobiles values( 9,6,'挡泥板','Mudguard',990,'Manufacture');
insert into automobiles values( 10,8,'轮胎','Tyre',300,'Purchase');
insert into automobiles values( 11,3,'发送机盘','Bonnet',3212,'Manufacture');
insert into automobiles values( 12,3,'活塞','Piston',1112.2,'Manufacture');
insert into automobiles values( 13,3,'汽化器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 14,4,'变速器','Gearbox',5712.25,'Manufacture');
insert into automobiles values( 15,4,'仪表板','Dashboard',538.92,'Manufacture');
insert into automobiles values( 16,14,'制动器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 17,14,'变速杆','Gearshift',2001,'Manufacture');
insert into automobiles values( 18,17,'传动轴','Shaft',1101,'Manufacture');
insert into automobiles values( 19,15,'里程表','Milometer',350.28,'Purchase');
/
--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe 
from automobiles
start with part_id=1
connect by prior part_id=parent_id 
order by level;
--缩进显示
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by prior part_id=parent_id 
order by level;在执行select level语句的时候，报如下错误：ORA-01436: CONNECT BY loop in user data
如何解决啊？

Answer 1

你的数据根节点的数据,造成了loop.
加上nocycle就可以了，或者把你的数据1,1--〉改为1,null--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe 
from automobiles
start with part_id=1
connect by nocycle prior part_id=parent_id 
order by level;
--缩进显示
select level,
    lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by nocycle prior part_id=parent_id 
order by level;

Answer 2

--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)     
);
--插入数据，将parent_id改为null就ok了吧。
insert into automobiles values( 1,null,'汽车','mobile',84321.99,'Assembly');
insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture');
insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase');
insert into automobiles values( 4,1,'附件','attached',15212,'Assembly');
insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase');
insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture');
insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture');
insert into automobiles values( 8,6,'车轮','Wheel',2062.2,'Manufacture');
insert into automobiles values( 9,6,'挡泥板','Mudguard',990,'Manufacture');
insert into automobiles values( 10,8,'轮胎','Tyre',300,'Purchase');
insert into automobiles values( 11,3,'发送机盘','Bonnet',3212,'Manufacture');
insert into automobiles values( 12,3,'活塞','Piston',1112.2,'Manufacture');
insert into automobiles values( 13,3,'汽化器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 14,4,'变速器','Gearbox',5712.25,'Manufacture');
insert into automobiles values( 15,4,'仪表板','Dashboard',538.92,'Manufacture');
insert into automobiles values( 16,14,'制动器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 17,14,'变速杆','Gearshift',2001,'Manufacture');
insert into automobiles values( 18,17,'传动轴','Shaft',1101,'Manufacture');
insert into automobiles values( 19,15,'里程表','Milometer',350.28,'Purchase');
/
--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe 
from automobiles
start with part_id=1
connect by prior part_id=parent_id 
order by level;
--缩进显示
select level,
    lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by prior part_id=parent_id 
order by level;将第一条数据中的parent_id改为null，否则loop循环找parent_id就找不到了！

Answer 3

connect by nocycle 即可

Answer 4

记录循环了，
用connect by nocycle

Answer 5

要么 connect by nocycle要么 改数据