WITH t1(empno, ename, mgr, lv, root, path) AS
(
SELECT empno, ename, mgr , 1 lv , ename root , ename path
FROM emp
WHERE mgr IS NULL -- START WITH mgr IS NULL
UNION ALL
SELECT c.empno , c.ename , c.mgr
, p.lv + 1 lv -- LEVEL
, p.root -- CONNECT_BY_ROOT(ename)
, p.path ||'-'|| c.ename path -- SYS_CONNCET)BY_PATH(ename)
FROM emp c
INNER JOIN t1 p
ON p.empno = c.mgr -- CONNECT BY PRIOR empno = mgr
) SEARCH DEPTH FIRST BY empno SET ord -- ORDER SIBLINGS BY empno
SELECT * FROM t1 ;
- WITH 문에 정의한 집합이 WITH 문 안에서 재귀적으로 참조되는 형태다.
- UNION ALL의 위쪽 쿼리는 계층의 시작자료를 검색하는 부분이다.
- 아래쪽 쿼리는 재귀참조를 이용해 계층관계를 기술하는 부분이다.
- 계층쿼리의 함수들은 논리적인 수식을 이용하여 구현할 수 있다.
계층쿼리가 더 간단한데 재귀쿼리는 뭐 하러 사용하나? 하고 생각할 수 있다. 재귀쿼리는 구문은 복잡하지만 그 구조만 정확하게 이해한다면 다양하게 활용할 수 있다.
1. 위 질문에서 root와 path를 만들어 냈던 것처럼 여러 가지를 유연하게 만들어 낼 수 있다.
2. 계층 쿼리로 해결하지 못한 것을 재귀쿼리로 해결할 수도 있다.
3. MS SQL Server에서도 동일한 구문을 사용할 수 있다.