마농님이 dbguide.net에 재귀쿼리로 connect by 문 구현한 전문가컬럼이 있는데

읽어보시려면 가입하셔야할 거예요.

http://www.dbguide.net/knowledge.db?cmd=view&boardUid=186796&boardConfigUid=19&boardStep=&categoryUid=205

대강의 내용은

아래와 같은 구문이

SELECT empno
     , ename
     , mgr
     , LEVEL lv
     , CONNECT_BY_ROOT(ename) root
     , SYS_CONNECT_BY_PATH(ename, '-') path
     , ROWNUM ord
  FROM emp
 START WITH mgr IS NULL
 CONNECT BY PRIOR empno = mgr
 ORDER SIBLINGS BY empno
;

아래와 같이 구현가능하다 하는 겁니다.

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
;

이 자리를 빌어 유용한 정보를 날마다 공유해주시는 마농님께 감사의 말씀을 올립니다^0^

SELECT 1 lv
     , t1.item
     , t1.parent
  FROM table_name t1
 WHERE t1.parent IS NULL
 UNION ALL
SELECT  2 lv
     , t2.item
     , t2.parent
  FROM table_name t1
     , table_name t2
 WHERE t1.parent IS NULL
   AND t2.parent = t1.item
 UNION ALL
SELECT  3 lv
     , t3.item
     , t3.parent
  FROM table_name t1
     , table_name t2
     , table_name t3
 WHERE t1.parent IS NULL
   AND t2.parent = t1.item
   AND t3.parent = t2.item
 UNION ALL
SELECT  4 lv
     , t4.item
     , t4.parent
  FROM table_name t1
     , table_name t2
     , table_name t3
     , table_name t4
 WHERE t1.parent IS NULL
   AND t2.parent = t1.item
   AND t3.parent = t2.item
   AND t4.parent = t3.item
;