1. connect by 기본
WITH MENU
AS
( SELECT 0 AS site_uid, 1 AS mnu_uid, '메뉴1' AS mnu_name, NULL AS mnu_parentUid FROM DUAL
UNION ALL SELECT 0 AS site_uid, 2 AS mnu_uid, '메뉴2' AS mnu_name, 1 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 0 AS site_uid, 3 AS mnu_uid, '메뉴3' AS mnu_name, 2 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 0 AS site_uid, 4 AS mnu_uid, '메뉴4' AS mnu_name, 1 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 0 AS site_uid, 5 AS mnu_uid, '메뉴5' AS mnu_name, 4 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 1 AS site_uid, 1 AS mnu_uid, '메뉴1' AS mnu_name, NULL AS mnu_parentUid FROM DUAL
UNION ALL SELECT 1 AS site_uid, 2 AS mnu_uid, '메뉴2' AS mnu_name, 1 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 1 AS site_uid, 3 AS mnu_uid, '메뉴3' AS mnu_name, 2 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 1 AS site_uid, 4 AS mnu_uid, '메뉴4' AS mnu_name, 1 AS mnu_parentUid FROM DUAL
UNION ALL SELECT 1 AS site_uid, 5 AS mnu_uid, '메뉴5' AS mnu_name, 4 AS mnu_parentUid FROM DUAL
)
SELECT SITE_UID AS SITE_UID
, MNU_UID AS MNU_UID
, LPAD(' ', LEVEL * 2, '-') || MNU_NAME AS MNU_NAME
, MNU_PARENTUID AS MNU_PARENTUID
, CONNECT_BY_ROOT(MNU_NAME) AS ROOT_NAME
, CONNECT_BY_ISLEAF AS ISLEAF
, CONNECT_BY_ISCYCLE AS ISCYCLE
, SYS_CONNECT_BY_PATH(MNU_NAME, '>') AS PATH
, PRIOR MNU_NAME AS PARENT_MNU_NAME
FROM MENU
START WITH SITE_UID = 0
AND MNU_PARENTUID IS NULL
CONNECT BY NOCYCLE PRIOR SITE_UID = SITE_UID
AND PRIOR MNU_UID = MNU_PARENTUID;
SITE_UID MNU_UID MNU_NAME MNU_PARENTUID ROOT_NAME ISLEAF ISCYCLE PATH PARENT_MNU_NAME
---------- ---------- ------------- ------------- --------- ---------- ---------- --------------- ----------------
0 1 - 메뉴1 NULL 메뉴1 0 0 >메뉴1 NULL
0 2 --- 메뉴2 1 메뉴1 0 0 >메뉴1>메뉴2 메뉴1
0 3 ----- 메뉴3 2 메뉴1 1 0 >메뉴1>메뉴2>메뉴3 메뉴2
0 4 --- 메뉴4 1 메뉴1 0 0 >메뉴1>메뉴4 메뉴1
0 5 ----- 메뉴5 4 메뉴1 1 0 >메뉴1>메뉴4>메뉴5 메뉴4
2. 재귀쿼리 (MSSQL SERVER와 같다. 아래의 두개 쿼리의 결과는 같다. 11g에서 지원한다.)
SELECT EMPNO
, TRIM(LPAD(' ', LEVEL * 1, '-')) || ENAME AS ENAME
, MGR
, LEVEL
, CONNECT_BY_ROOT ENAME AS ROOT
, SUBSTR(SYS_CONNECT_BY_PATH(ENAME, '-'), 2) AS PATH
FROM SCOTT.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;
WITH CTE(EMPNO, ENAME, TREE_NM, MGR, LVL, LVL_1, LVL_2, LVL_3, LVL_4, PATH)
AS
(
SELECT EMPNO
, ENAME AS TREE_NM
, ENAME
, MGR
, 1 AS LVL
, ENAME AS LVL_1
, NULL AS LVL_2
, NULL AS LVL_3
, NULL AS LVL_4
, ENAME AS PATH
FROM SCOTT.EMP
WHERE MGR IS NULL
UNION ALL
SELECT A.EMPNO
, TRIM(LPAD(' ', (B.LVL + 1) * 1, '-')) || A.ENAME AS TREE_NM
, A.ENAME
, A.MGR
, B.LVL + 1 AS LVL
, B.LVL_1 AS LVL_1
, DECODE(B.LVL + 1, 2, A.ENAME, 3, B.LVL_2, 4, B.LVL_2) AS LVL_2
, DECODE(B.LVL + 1, 2, B.LVL_2, 3, A.ENAME, 4, B.LVL_3) AS LVL_3
, DECODE(B.LVL + 1, 2, B.LVL_2, 3, B.LVL_3, 4, A.ENAME) AS LVL_4
, B.PATH || '-' || A.ENAME AS PATH
FROM SCOTT.EMP A
JOIN CTE B ON B.EMPNO = A.MGR
)
SEARCH DEPTH FIRST BY EMPNO SET ORD -- ORDER SIBLINGS BY EMPNO
SELECT *
FROM CTE;
EMPNO ENAME TREE_NM MGR LVL LVL_1 LVL_2 LVL_3 LVL_4 PATH ORD
------- ----------- ------- ------- --- ------- ------- ------- ------- ----------------------- -------
7839 KING KING 1 KING KING 1
7566 -JONES JONES 7839 2 KING JONES KING-JONES 2
7902 --FORD FORD 7566 3 KING JONES FORD KING-JONES-FORD 3
7369 ---SMITH SMITH 7902 4 KING JONES FORD SMITH KING-JONES-FORD-SMITH 4
7698 -BLAKE BLAKE 7839 2 KING BLAKE KING-BLAKE 5
7499 --ALLEN ALLEN 7698 3 KING BLAKE ALLEN KING-BLAKE-ALLEN 6
7521 --WARD WARD 7698 3 KING BLAKE WARD KING-BLAKE-WARD 7
7654 --MARTIN MARTIN 7698 3 KING BLAKE MARTIN KING-BLAKE-MARTIN 8
7844 --TURNER TURNER 7698 3 KING BLAKE TURNER KING-BLAKE-TURNER 9
7900 --JAMES JAMES 7698 3 KING BLAKE JAMES KING-BLAKE-JAMES 10
7782 -CLARK CLARK 7839 2 KING CLARK KING-CLARK 11
1111 --PARK PARK 7782 3 KING CLARK PARK KING-CLARK-PARK 12
7934 --MILLER MILLER 7782 3 KING CLARK MILLER KING-CLARK-MILLER 13