[Oracle] SYS_CONNECT_BY_PATH

위치를 시각화 해주는 쿼리

WITH T(MENU_NM, SEQ1, SEQ2) AS (
  SELECT '상1'                , 1, 0 FROM DUAL UNION ALL
  SELECT '상1중1'           , 2, 1 FROM DUAL UNION ALL
  SELECT '상1중2'           , 3, 1 FROM DUAL UNION ALL
  SELECT '상2중1'           , 9, 7 FROM DUAL UNION ALL
  SELECT '상1중1하1'       , 4, 2 FROM DUAL UNION ALL
  SELECT '상1중1하2'       , 5, 2 FROM DUAL UNION ALL
  SELECT '상1중2하1'       , 6, 3 FROM DUAL UNION ALL
  SELECT '상1중1하1밑1'   , 8, 4 FROM DUAL UNION ALL
  SELECT '상2'                , 7, 0 FROM DUAL)
SELECT MENU_NM,
       LEVEL AS LV,
       SEQ1,
       SEQ2,
       SYS_CONNECT_BY_PATH(MENU_NM, ' > ') AS FILE_PATH
FROM T
START WITH SEQ2 = 0
CONNECT BY PRIOR SEQ1 = SEQ2

결과는

	MENU_NM	LV	SEQ1	SEQ2	FILE_PATH
1	상1	1	1	0	> 상1
2	상1중1	2	2	1	> 상1 > 상1중1
3	상1중1하1	3	4	2	> 상1 > 상1중1 > 상1중1하1
4	상1중1하1밑1	4	8	4	> 상1 > 상1중1 > 상1중1하1 > 상1중1하1밑1
5	상1중1하2	3	5	2	> 상1 > 상1중1 > 상1중1하2
6	상1중2	2	3	1	> 상1 > 상1중2
7	상1중2하1	3	6	3	> 상1 > 상1중2 > 상1중2하1
8	상2	1	7	0	> 상2
9	상2중1	2	9	7	> 상2 > 상2중1