一個遞迴查詢

今天有同學在群裡詢問一個遞迴查詢的寫法，記錄一下：

表t 表t1(id1,id2 多於表t表的id關聯)
id name id1 id2
1 a 1 2
2 b 2 3
3 c 2 4
4 d 3 5
5 e

表t1,id1,id2 是一個部門上下級關係,查詢所有部門的列表,按照級別關係寫成完整字串.
1:a
2:a\b
3:a\b\c
4:a\b\d
5:a\b\c\e

如下：
create table t(id number,name varchar2(10));
insert into t values(1,'a');
insert into t values(2,'b');
insert into t values(3,'c');
insert into t values(4,'d');
insert into t values(5,'e');

create table t1(id1 number,id2 number);
insert into t1 values(1,2);
insert into t1 values(2,3);
insert into t1 values(2,4);
insert into t1 values(3,5);

create or replace function sp_get_name(p_num number) return varchar2 is
 p_name varchar2(100);
begin
 with tt as
 (select distinct t1.id1, id2
 from t1
 connect by id2 = prior id1
 start with id2 = p_num)
 select replace(wmsys.wm_concat(name), ',', '\')
 into p_name
 from (select distinct t.id, t.name
 from tt, t
 where tt.id1 = t.id
 or tt.id2 = t.id
 order by t.id);
 return p_name;
end;

SQL> select id, decode(sp_get_name(id), null, 'a', sp_get_name(id)) name from t;

 ID NAME
---------- -----------------------------------------------------------------------
 1 a
 2 a\b
 3 a\b\c
 4 a\b\d
 5 a\b\c\e