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 entries show how to implement all the CONNECT BY features using the new recursive WITH clause.
Basic recursive query
CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):
SELECT e.ename, e.empno, e.mgr
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
/
Same result using the recursive WITH clause:
WITH r_emps (ename, empno, mgr) AS (SELECT e.ename, e.empno, e.mgr
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT e.ename, e.empno, e.mgr
FROM r_emps e
/
The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the
anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.
Formatting the result set
CONNECT BY queries often use the LEVEL pseudo column to format the output.
SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
, e.empno, e.mgr
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
/
The same can be achieved using the following recursive WITH query:
WITH r_emps (ename, empno, mgr, lvl)
AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
,e.empno, e.mgr
FROM r_emps e
/
To simulate the LEVEL pseudo column using the recursive WITH clause, one has to add a virtual level column on the root elements, which will be incremented along the hierarchy.
Sorting the result set
The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.
SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
, e.empno, e.mgr
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
ORDER SIBLINGS BY e.ename
/
Same result can be achieved using the recursive WITH clause with the SEARCH clause.
WITH r_emps (ename, empno, mgr, lvl)
AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
, e.empno ,e.mgr
FROM r_emps e
ORDER BY e.sorting
/
SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children, the ename column is used as the order attribute within the hierarchy and assigned to the attribute sorting which can afterwards be used in the outermost query.