Tags
How to implement CONNECT BY features
ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.
Root and Path
CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy:
SELECT e.ename, e.empno, e.mgr
,SYS_CONNECT_BY_PATH(e.ename,'/') AS path
,CONNECT_BY_ROOT e.ename AS root
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
ORDER SIBLINGS BY e.ename
/
ENAME EMPNO MGR PATH ROOT
------- ----- ---- ----------------------- ----
KING 7839 /KING KING
BLAKE 7698 7839 /KING/BLAKE KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
WARD 7521 7698 /KING/BLAKE/WARD KING
CLARK 7782 7839 /KING/CLARK KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
JONES 7566 7839 /KING/JONES KING
FORD 7902 7566 /KING/JONES/FORD KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
Same result using the recursive WITH clause:
WITH r_emps (ename, empno, mgr, path, root)
AS (SELECT e.ename, e.empno, e.mgr
, '/' || e.ename AS path
, e.ename AS root
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr
, m.path || '/' || e.ename AS path
, m.root
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.ename, e.empno, e.mgr, e.path, e.root
FROM r_emps e
ORDER BY e.sorting
/
Path and root are set to ename in the anchor query. While the path is enlarged along the hierarchy by adding separator and path the root is simply passed all the way down to the leafs.