Oracle递归查询

创建表：

 1 CREATE TABLE SC_DISTRICT
 2 (
 3   ID         NUMBER(10)                  NOT NULL,
 4   PARENT_ID  NUMBER(10),
 5   NAME       VARCHAR2(255 BYTE)          NOT NULL
 6 );
 7 
 8 ALTER TABLE SC_DISTRICT ADD (
 9   CONSTRAINT SC_DISTRICT_PK
10  PRIMARY KEY
11  (ID));
12 
13 ALTER TABLE SC_DISTRICT ADD (
14   CONSTRAINT SC_DISTRICT_R01 
15  FOREIGN KEY (PARENT_ID) 
16  REFERENCES SC_DISTRICT (ID));

插入数据:

 1 INSERT INTO SC_DISTRICT(ID,NAME) VALUES(1,'四川省');
 2 
 3 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(2,1,'巴中市');
 4 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(3,1,'达州市'); 
 5 
 6 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(4,2,'巴州区');
 7 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(5,2,'通江县');
 8 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(6,2,'平昌县');
 9 
10 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(7,3,'通川区');
11 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(8,3,'宣汉县');
12 
13 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(9,8,'塔河乡');
14 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(10,8,'三河乡');
15 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(11,8,'胡家镇');
16 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(12,8,'南坝镇');
17  
18 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(13,6,'大寨乡');
19 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(14,6,'响滩镇');
20 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(15,6,'龙岗镇');
21 INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(16,6,'白衣镇');

生成表如下：

查询某节点的所有子孙节点

 1 --查询巴中市下面的所有行政组织(结果包含当前节点):
 2 SELECT *
 3 FROM SC_DISTRICT
 4 START WITH NAME='巴中市'
 5 CONNECT BY PRIOR ID=PARENT_ID
 6 
 7 --查询结果:
 8 ID    PARENT_ID      NAME
 9 2    1            巴中市
10 4    2            巴州区
11 5    2            通江县
12 6    2            平昌县
13 13    6            大寨乡
14 14    6            响滩镇
15 15    6            龙岗镇
16 16    6            白衣镇

查询指定节点的递归根节点

 1 --查询响滩镇镇所属的市：
 2 SELECT ID,   PARENT_ID,      NAME,     
 3        CONNECT_BY_ROOT(ID)   CITY_ID,
 4        CONNECT_BY_ROOT(NAME) CITY_NAME
 5 FROM   SC_DISTRICT
 6 WHERE  NAME='响滩镇' 
 7        START WITH PARENT_ID=1
 8        CONNECT BY PRIOR ID=PARENT_ID
 9 --查询结果：
10 ID        PARENT_ID    NAME    CITY_ID    CITY_NAME
11 14        6            响滩镇  2          巴中市

CONNECT BY子句伪列的应用

 1 --LEVEL:查询节点层次，从1开始。
 2 --CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0
 3 SELECT   ID,   NAME,   PARENT_ID,   LEVEL,   CONNECT_BY_ISLEAF
 4 FROM     SC_DISTRICT
 5          START WITH NAME='巴中市'
 6          CONNECT BY PRIOR ID=PARENT_ID 
 7          ORDER BY ID;
 8 
 9 --查询结果：
10 ID    NAME    PARENT_ID    LEVEL    CONNECT_BY_ISLEAF
11 2    巴中市    1    1    0
12 4    巴州区    2    2    1
13 5    通江县    2    2    1
14 6    平昌县    2    2    0
15 13    大寨乡    6    3    1
16 14    响滩镇    6    3    1
17 15    龙岗镇    6    3    1
18 16    白衣镇    6    3    1

查询递归路径

 1 --查询巴中市下行政组织递归路径
 2 SELECT  ID,     NAME,   PARENT_ID,
 3         SUBSTR(SYS_CONNECT_BY_PATH(NAME,'->'),3)   NAME_PATH
 4 FROM    SC_DISTRICT
 5         START   WITH NAME='巴中市'
 6         CONNECT BY PRIOR ID=PARENT_ID
 7         
 8 --查询结果：
 9 ID    NAME    PARENT_ID    NAME_PATH
10 2    巴中市    1    巴中市
11 4    巴州区    2    巴中市->巴州区
12 5    通江县    2    巴中市->通江县
13 6    平昌县    2    巴中市->平昌县
14 13    大寨乡    6    巴中市->平昌县->大寨乡
15 14    响滩镇    6    巴中市->平昌县->响滩镇
16 15    龙岗镇    6    巴中市->平昌县->龙岗镇
17 16    白衣镇    6    巴中市->平昌县->白衣镇

转自：http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html