Oracle connect by 例子

转载自：http://hi.baidu.com/ljmybfq/item/f9d48a1651ce560dd1d66d45

层次查询子句connect by，用于构造层次结果集的查询。
语法：
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
说明：
a、START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。
b、当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用，用法见示例2。

用法举例：

示例1：显示所有地名关系结构。
SQL> select * from t;
AREA_ID AREA_NAME MGR_ID
-------- ---------- ------
86 中国
01 北京 86
02 福建 86
0101 海淀区 01
0102 朝阳区 01
0103 东城区 01
0104 西城区 01
0201 厦门 02
0202 福州 02
020101 湖里 0201
020102 思明 0201
010401 复兴门 0104
010402 西单 0104
已选择13行。
SQL>
SQL> set pagesize 50
SQL> col AreaName for a12
SQL> col Root for a10
SQL> col Path for a24
SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName",
2 connect_by_root area_name "Root",
3 connect_by_isleaf "IsLeaf",
4 level ,
5 SYS_CONNECT_BY_PATH(area_name, '/') "Path"
6 from t
7 start with mgr_id is null
8 connect by prior area_id = mgr_id;
AreaName Root IsLeaf LEVEL Path
------------ ---------- ------ ---------- ------------------------
中国 中国 0 1 /中国
北京 中国 0 2 /中国/北京
海淀区 中国 1 3 /中国/北京/海淀区
朝阳区 中国 1 3 /中国/北京/朝阳区
东城区 中国 1 3 /中国/北京/东城区
西城区 中国 0 3 /中国/北京/西城区
复兴门 中国 1 4 /中国/北京/西城区/复兴门
西单 中国 1 4 /中国/北京/西城区/西单
福建 中国 0 2 /中国/福建
厦门 中国 0 3 /中国/福建/厦门
湖里 中国 1 4 /中国/福建/厦门/湖里
思明 中国 1 4 /中国/福建/厦门/思明
福州 中国 1 3 /中国/福建/福州
已选择13行。
说明：
a、prior：是单一操作符，放在列名的前面，等号左右均可； 放在父 ID 就是 寻找 祖先节点 ，放到本身 ID就是寻找 子节点 ；
b、connect_by_root：是单一操作符，返回当前层的最顶层节点；
c、connect_by_isleaf：是伪列，判断当前层是否为叶子节点，1代表是，0代表否；
d、level：是伪列，显示当前节点层所处的层数；
e、SYS_CONNECT_BY_PATH：是函数，显示当前层的详细路径。

示例2：找出人事部门中存在跟其他部门互为管理者的人员名单。
SQL> select * from t2;
EMP DEPT MGR
------------ ------ ----------
刘涛 总裁办
李飞 总裁办 刘涛
张强 总裁办 刘涛
王鹏 人事 李飞
李华 人事 李飞
张强 人事 李飞
李飞 行政 张强
吴华 行政 张强
已选择8行。
SQL>
SQL> col emp for a12
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
2 from t2
3 start with dept ='人事'
4 connect by prior emp = mgr;
ERROR:
ORA-01436: 用户数据中的 CONNECT BY 循环

未选定行

说明：张强和李飞互为管理者，因此，要用nocycle，如下所示：
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
2 from t2
3 start with dept ='人事'
4 connect by nocycle prior emp = mgr;
emp
------------
王鹏
李华
张强
李飞
王鹏
李华
吴华
已选择7行。

SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp",
2 connect_by_iscycle "IsCycle"
3 from t2
4 start with dept ='人事'
5 connect by prior emp = mgr;
connect by prior emp = mgr
*
第 5 行出现错误:
ORA-30930: CONNECT_BY_ISCYCLE 伪列要求 NOCYCLE 关键字

说明：在用connect_by_iscycle定位节点时，也要用nocycle关键字，如下所示：

SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp",
2 connect_by_iscycle "IsCycle"
3 from t2
4 start with dept ='人事'
5 connect by nocycle prior emp = mgr;emp IsCycle
------------ -------
王鹏 0
李华 0
张强 0
李飞 1
王鹏 0
李华 0
吴华 0
已选择7行。
SQL>

示例3：仅显示第二层（即level=2）省市名称。
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName"
2 from t
3 where level = 2
4 start with mgr_id is null
5 connect by prior area_id = mgr_id;
AreaName
------------
北京
福建

示例4：用connect by构造序列。
SQL>
SQL> select rownum rn
2 from dual
3 connect by rownum<=10;
RN
----------
1
2
3
4
5
6
7
8
9
10
已选择10行。
SQL> select rownum*2 -1 rn
2 from dual
3 connect by rownum<=10;
RN
----------
1
3
5
7
9
11
13
15
17
19
已选择10行。

-----------------------------------------
附：建表语句
create table t (area_id varchar2(6), area_name varchar2(10), mgr_id varchar2(6));
insert into t values('86', '中国', null);
insert into t values('01', '北京', '86');
insert into t values('02', '福建', '86');
insert into t values('0101', '海淀区', '01');
insert into t values('0102', '朝阳区', '01');
insert into t values('0103', '东城区', '01');
insert into t values('0104', '西城区', '01');
insert into t values('0201', '厦门', '02');
insert into t values('0202', '福州', '02');
insert into t values('020101', '湖里', '0201');
insert into t values('020102', '思明', '0201');
insert into t values('010401', '复兴门', '0104');
insert into t values('010402', '西单', '0104');
commit;

create table t2 (emp varchar2(10), dept varchar2(6), mgr varchar2(10));
insert into t2 values('刘涛', '总裁办', null);
insert into t2 values('李飞', '总裁办', '刘涛');
insert into t2 values('张强', '总裁办', '刘涛');
insert into t2 values('王鹏', '人事', '李飞');
insert into t2 values('李华', '人事', '李飞');
insert into t2 values('张强', '人事', '李飞');
insert into t2 values('李飞', '行政', '张强');
insert into t2 values('吴华', '行政', '张强');
commit;