SYS_CONNECT_BY_PATH(column, delimiter) RETURN VARCHAR2
CONNECT BY 層次查詢的 SELECT 列表中。CREATE TABLE test (id NUMBER, pid NUMBER, name VARCHAR2(20));
INSERT INTO test VALUES (1, 0, 'ROOT');
INSERT INTO test VALUES (2, 1, 'CHILD1');
INSERT INTO test VALUES (3, 1, 'CHILD2');
INSERT INTO test VALUES (4, 2, 'GRAND1');
INSERT INTO test VALUES (5, 2, 'GRAND2');
COMMIT;
SELECT LEVEL,
SYS_CONNECT_BY_PATH(id, '/') AS path_id,
SYS_CONNECT_BY_PATH(name, ' > ') AS path_name
FROM test
START WITH pid = 0
CONNECT BY PRIOR id = pid;
結果:
LEVEL PATH_ID PATH_NAME
----- ------------ --------------------
1 /1 > ROOT
2 /1/2 > ROOT > CHILD1
3 /1/2/4 > ROOT > CHILD1 > GRAND1
3 /1/2/5 > ROOT > CHILD1 > GRAND2
2 /1/3 > ROOT > CHILD2
SELECT LEVEL,
SYS_CONNECT_BY_PATH(pid, '/') AS path_pid
FROM test
START WITH pid = 0
CONNECT BY PRIOR pid = id; -- 注意條件反轉
結果展示從葉節點向上追溯父鏈路。
SYS_CONNECT_BY_PATH + SUBSTR。CONNECT_BY_ROOT name AS root_name。ORDER SIBLINGS BY name。9i Release 2 (9.2.0.2) 引入,10g/11g/12c/19c/21c 完整支援。