这是一个CSDN老帖：
http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=170559
我是抱着学习的心态看这个帖子的，下面把握学习结果总结一下。
楼主的问题是这样的：
--------------------------------------------------------------------------------
表A：
id name
1 a
2 b
3 c
4 d
5 e
表B(id1,id2都与A表的id关联，是联合主外键):
id1 id2
1 2
2 3
2 4
3 5
这是一个部门上下级的关系，前面的是上级，后面的下级，我想得到所有部门的列表，按照级别关系写成完整的字串，结果如下：
id full_name
1 a
2 a/b
3 a/b/c
4 a/b/d
5 a/b/c/d
请问怎么写？存储过程或函数都可以，十分感谢！
---------------------------------------------------------
问题的关键是把用LEVEL关键字把层次关系搞搞清楚。
select level from table_B connect by prior id2=id1 start with id1=0;
LEVEL
----------
1
2
3
4
3
SQL> select level from table_B connect by prior id2=id1 start with id1=1;
LEVEL
----------
1
2
3
2
SQL> select level from table_B connect by prior id2=id1 start with id1=2;
LEVEL
----------
1
2
1
SQL> select level from table_B connect by prior id2=id1 start with id1=3;
LEVEL
----------
1
SQL> select level from table_B connect by prior id2=id1 start with id1=4;
LEVEL
----------
SQL> select level from table_B connect by prior id2=id1 start with id1=5;
LEVEL
----------
可以看出，LEVEL值表示id1领导下人员id2在id1集团所处的层数，被领导者紧跟在by prior后面。
select lpad(id2, level*length(id2), ' ') id,
2 ltrim(sys_connect_by_path(id2,'/'), '/') path
3 from Table_B
4 connect by prior id2=id1
5 start with id1=0
6 /
ID PATH
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 1
2 1/2
3 1/2/3
5 1/2/3/5
4 1/2/4
下面给出完整解答：
DROP TABLE Table_A;
Table dropped
SQL> create table Table_A (id number(4), name varchar2(20));
Table created
SQL> insert into Table_A values(1, 'a');
1 row inserted
SQL> insert into Table_A values(2, 'b');
1 row inserted
SQL> insert into Table_A values(3, 'c');
1 row inserted
SQL> insert into Table_A values(4, 'd');
1 row inserted
SQL> insert into Table_A values(5, 'e');
1 row inserted
SQL> commit;
Commit complete
SQL> DROP TABLE Table_B;
Table dropped
SQL> create table Table_B (id1 number(4), id2 number(4));
Table created
SQL> insert into table_B values(0,1);
1 row inserted
SQL> insert into Table_B values(1,2);
1 row inserted
SQL> insert into Table_B values(2,3);
1 row inserted
SQL> insert into Table_B values(2,4);
1 row inserted
SQL> insert into Table_B values(3,5);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT id2, ltrim(sys_connect_by_path(NAME, '/'), '/') path
2 from
3 (SELECT B.*, A.NAME
4 FROM Table_B B, Table_A A
5 WHERE B.id2=A.id)
6 connect by prior id2=id1
7 start with id1 = 0
8 ORDER BY id2
9 /
ID2 PATH
----- --------------------------------------------------------------------------------
1 a
2 a/b
3 a/b/c
4 a/b/d
5 a/b/c/e
