-- Create table
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(256),
UPPERDEPID number(10)
);
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
DEPNAME ROOT ISLEAF LEVEL PATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
General Deparment General Deparment 0 1 /General Deparment
-Development General Deparment 0 2 /General Deparment/Development
---Server Development General Deparment 1 3 /General Deparment/Development/Server Development
---Client Development General Deparment 1 3 /General Deparment/Development/Client Development
-QA General Deparment 0 2 /General Deparment/QA
---TA General Deparment 1 3 /General Deparment/QA/TA
---Porject QA General Deparment 1 3 /General Deparment/QA/Porject QA
7 rows selected
说明:
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点,如果这个节点下面有子节点,则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径,并用“/”分隔
oracle递归查询使用
转自:
Oracle “CONNECT BY” 使用