계층형구조 (CONNECT BY)

===================================================================

[예]

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 

           )