===================================================================
[예]
SELECT
LEVEL AS LEV
, MENU.*
FROM
TBL_MENU MENU
WHERE MENU_TYPE_CD = '1'
START WITH MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR MENU.MENU_ID = MENU.UP_MENU_ID
ORDER SIBLINGS BY MENU_SEQ
===================================================================
[풀이]
UP_MENU_ID 가 'TOP'인 것부터 순환고리를 시작하며
동일 레벨일경우(즉, UP_MENU_ID 가 'TOP'으로 하는 여러 ROW)는 MENU_SEQ가 먼저인것 부터 계층구조
그리고 마지막으로 MENU_TYPE_CD가 '1'인것만 최종 추출
===================================================================
◈ LEVEL 예약어 : depth
◈ CONNECT BY의 실행순서
(1) START WITH 절
(2) CONNECT BY 절
(3) WHERE 절
◈
START WITH : 시작위치 설정 (서브쿼리를 사용가능)
CONNECT BY : 순환고리의 조건 (서브쿼리를 사용불가)
ORDER SIBLINGS BY : 같은 레벨중 순환고리 순서를 정할때 사용
◈ PRIOR 의 위치
(1) CONNECT BY PRIOR 자식컬럼 = 부모컬럼 ==> 부모에서 자식으로 트리 구성
(2) CONNECT BY 자식컬럼 = PRIOR 부모컬럼 ==> 자식에서 부모으로 트리 구성
◈ 데이터가 많아질 경우....
- START WITH MENU_ID = 'TOP'
MENU_ID 컬럼에 index가 생성되어 있지 않는다면 속도를 보장할 수 없습니다.
- CONNECT BY PRIOR MENU_ID = UP_MENU_ID
역시 PRIOR 쪽의 컬럼값이 상수가 되기 때문에 UP_MENU_ID컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
- 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로 표현하기가 어렵 습니다.
◈
(1) '상품' 메뉴는 모두 출력 안함.
SELECT
LEVEL AS LEV
, MENU.*
FROM
TBL_MENU MENU
WHERE MENU.MENU_NAME <> '상품'
START WITH MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR MENU.MENU_ID = MENU.UP_MENU_ID
ORDER SIBLINGS BY MENU_SEQ
(2) '상품'메뉴 밑으로 모든 메뉴는 출력안함.
SELECT
LEVEL AS LEV
, MENU.*
FROM
TBL_MENU MENU
START WITH MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR MENU.MENU_ID = MENU.UP_MENU_ID
AND MENU.MENU_NAME <> '상품'
ORDER SIBLINGS BY MENU_SEQ
** 참고) 메뉴중 2LEVEL까지만 결과 출력
(WHERE조건으로도 LEVEL <= 2가능함. 그러나 권하지는 않는다.
왜? WHERE 조건은 모든 나온결과에 대해서 FILTER하지만 CONNECT BY절의 조건으로 넣으면 순환자체를 안한다. 즉 성능에 좋다)
SELECT
LEVEL AS LEV
, MENU.*
FROM
TBL_MENU MENU
START WITH MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR MENU.MENU_ID = MENU.UP_MENU_ID
AND LEVEL <= 2
ORDER SIBLINGS BY MENU_SEQ
◈
(1) 들여쓰기로 결과출력
SELECT
LPAD(’ ’, 4*(LEVEL-1)) || MENU.MENU_NAME
, MENU.*
FROM
TBL_MENU MENU
WHERE MENU_TYPE_CD = '1'
START WITH MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR MENU.MENU_ID = MENU.UP_MENU_ID
ORDER SIBLINGS BY MENU_SEQ
(2) 엑셀과 같이 셀 단위로 들여쓰기
SELECT
DECODE(LEV, '1', MENU_NAME, '') AS LEV1
, DECODE(LEV, '2', MENU_NAME, '') AS LEV2
, DECODE(LEV, '3', MENU_NAME, '') AS LEV3
, DECODE(LEV, '4', MENU_NAME, '') AS LEV4
, DECODE(LEV, '5', MENU_NAME, '') AS LEV5
FROM
(
SELECT
LEVEL AS LEV
, MENU.*
FROM TBL_MENU MENU
WHERE MENU_TYPE_CD = '1'
START WITH MENU.MENU_ID = 'TOP'
CONNECT BY PRIOR MENU.MENU_ID = MENU.UP_MENU_ID
ORDER SIBLINGS BY MENU_SEQ
)