Oracle START WITH CONNECT BY PRIOR 사용법

 

Oracle START WITH CONNECT BY PRIOR 사용법

START WITH CONNECT BY PRIOR 구문은 오라클에서 트리와 같은 계층구조를 표현하기 위해 사용합니다.

계층구조를 표현하기 위해 테이블은 상하를 구분할 수 있는 컬럼(속성: ATTRIBUTE)가 존재 해야 합니다.

(예 : 자신의 개인번호와 상사의 개인번호, 상위부서코드와 부서코드 등등)

그럼 기본적인 사용법을 알아 보겠습니다.

-----------------------------------------------------------------------------------------------------------------

SELECT *

  FROM 테이블명

 START WITH <ROOT를 정하는 조건>

 CONNECT BY PRIOR <ROOT 이하 우선순위 조건>

-----------------------------------------------------------------------------------------------------------------

START WITH 다음에는 ROOT(기준)를 정하는 조건을 주어야 합니다. 

무엇을 시작점으로 잡을 것인가를 정하는 것입니다. (상위에 뭐라도 있어야 계층구조가 되겠죠...)

CONNECT BY PRIOR 다음에는 ROOT 이하에 어떤 행(튜플)을 붙일 것인가에 대한 조건을 주게 됩니다.

뭐.. 아직 감이 안오실꺼에요!

예를 들어 설명하도록 하죠.

 

SELECT S1.UP_DEPT_CD
           , S1.DEPT_CD
           , CASE WHEN UP_DEPT_CD = 0 THEN S1.DEPT_NM                  -- 하위부서에만 'ㄴ' 표시.
                  ELSE LPAD('└', 3*(LEVEL - 1)) || S1.DEPT_NM END AS DEPT_NM   -- 공백표시위해 LPAD사용.
    FROM (
          SELECT 1 AS DEPT_CD
               , 0        AS UP_DEPT_CD
               , '부서' AS DEPT_NM 
            FROM DUAL
            UNION
            SELECT 2
                 , 1
                 , '개발부' 
              FROM DUAL
             UNION
             SELECT 3
                  , 2
                  , '개발1팀' 
               FROM DUAL
              UNION
              SELECT 4
                   , 1
                   , '영업부' 
                FROM DUAL
               UNION
              SELECT 5
                   , 4
                   , '영업1팀' 
                FROM DUAL
               UNION
              SELECT 6
                   , 4
                   , '영업2팀' 
                FROM DUAL
               UNION
              SELECT 7
                   , 3
                   , 'DB' 
                FROM DUAL
               UNION
              SELECT 8
                   , 3
                   , '보안' 
                FROM DUAL)  S1
   START WITH S1.DEPT_CD = 1
   CONNECT BY PRIOR S1.DEPT_CD = S1.UP_DEPT_CD

 

쓸대 없이 좀 길게 되긴 했네요.

결과 부터 보시죠.

 

테이블을 만들어서 했다면 소스가 좀 짧아 졌겠지만..

그냥 설명 하겠습니다.

 

 

 

S1 테이블에는 UP_DEPT_CD(상위부서코드), DEPT_CD(부서코드), DEPT_NM(부서명) 이 존재 합니다.

앞에서 설명했듯이 각각 자신의 부서코드와 상위부서 코드를 가지고 있죠.

출력된 결과를 보시면 부서에는 개발부, 영업부가 있고 

개발부에는 개발1팀, 개발1팀에는 DB와 보안부서가 하위부서로 있는 것을 볼 수 있습니다. (영업부는 영업1팀, 2팀)

그럼 어떻게 이런 구조가 되는 지 START WITH 구문을 보도록 하죠.

위 코딩을 보시면 START WITH 뒤 ROOT를 정하는 조건에 DEPT_CD = 1 이란 조건을 주었습니다.

테이블에서 DEPT_CD 가 1인 '부서'를 ROOT(기준)로 잡겠단 말이죠.

그래서 DEPT_CD가 1인 부서를 시작으로 CONNECT BY PRIOR 뒤의 조건인 상위부서코드와 자신의 부서코드가 같은 부서들이 나열되는 것입니다.

1. 기준을 잡았다. (START WITH DEPT_CD = 1)

2. 하위에 붙일 것을 찾는다. (CONNECT BY PRIOR DEPT_CD = UP_DEPT_CD)

 

위의 결과를 보면 조건을 순차적으로 찾는것을 볼수 있습니다.

ROOT의 DEPT_CD가 1이니 UP_DEPT_CD가 1인 것을 찾고, 찾은 결과에 또 조건에 맞는 값을 찾고.... 이런식으로 ㅎ

 

START WITH 조건을 DEPT_CD = 2 로 변경 하면 어떻게 될까요?

DEPT_CD 가 2인 개발부를 ROOT(기준)으로 개발부의 하위 부서들만 존재하게 됩니다.

          

 [참고]START WITH 조건에는 서브쿼리 사용이 가능하고 CONNECT BY PRIOR 조건에는 서브쿼리 사용이 불가합니다.