ORA-01436: 使用者資料中的 CONNECT BY 迴圈

2009-09-09 11:41

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)    
);
--插入資料
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 解決方案：將第一條資料中的parent_id改為null，否則loop迴圈找parent_id就找不到了。

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/weikaifenglove/archive/2009/05/21/4206979.aspx