오라클 트리구조 완벽 이해하기 oracle start with connect by 사용법

오라클에서는 트리구조가 아주 중요합니다

level ~ start with ~ connect by prior ~를 많이 사용합니다

그러나 오라클을 처음 접하는 사람은 이 구문을 한 번에 이해하기가 쉽지 않습니다

그래서 한번 이 구문의 예문을 통해 같이 알아보고자 합니다

 

 

아래와 같이 테이블이 존재합니다

 

SELECT DEPTNO, ENAME FROM EMP

 

 

 

가공 전 데이터

 

위의 데이터로 아래와 같이 출력하고자 합니다

어떤 사람은 case문으로 하면 안 되지 않을까 하고 의문을 갖는 사람도 있을 거라 생각합니다

당연히 가능합니다

하지만 case문은 deptno의 값이 쿼리에 값 조건문이 들어가야 합니다

이러한 경우는 deptno가 증가할 경우에 대해 전혀 대비가 되지 않은 쿼리라고 할 수 있습니다

프로젝트를 진행하다 보면 case문을 자주 사용하게 되는데

그 함정에 빠져 큰 그림을 보지 못하고 당장 앞만 보게 되는 경우가 있습니다

그래서 대도록이면 테이블의 데이터 값이 쿼리문에는 없는 것이 범용성이 좋은 쿼리라고 할 수 있습니다

 

그리고 이러한 재밌는 쿼리를 보면

여러 가지를 생각나게 합니다

밑에 deptno별로 모든 직원을 조회해야 한다면

밑에 처럼 안 하면 java에서 map형 list로 담아서 진행해야 합니다

하지만 아래처럼 한다면 list형 map으로 간단히 담아질 것으로 보입니다

하지만 그렇게 되면 자바에서 또다시 다른 작업을 진행해야 하겠지요

아무튼!!

계속해서 보도록 하겠습니다

 

 

 

최종 데이터

 

 

 

아래의 코드로 출력이 가능합니다

처음 접하면 너무 복잡하고 어려워 보이겠지만

같이 천천히 봅시다

 

 

 

 select deptno,
         ltrim(sys_connect_by_path(ename,','),',') emps
    from (
  select deptno,
         ename,
         row_number() over
                  (partition by deptno order by empno) rn,
         count(*) over
                  (partition by deptno) cnt
   from emp
        )
  where level = cnt
  start with rn = 1
 connect by prior deptno = deptno and prior rn = rn-1;

 

 

 

 

반응형

 

 

우선 서브 쿼리를 단독 실행해서 사원에 대한 소속 부서, 이름 empno 오름차순 정렬로 결과가 노출되고 있습니다

순위에 해당 하는 RN는 트리를 이동하는 목적입니다.

 

---

 

start with

start with rn = 1이라는 것은 rn이 1일될때마다 새로운 트리를 만들게 됩니다

 

connect by prior

connect by prior deptno = deptno는 자기 자신을 상위 레벨로 지정을 하고

한 조건을 더 붙여서  rn = rn-1로 합니다

이 의미는 자기 이전행을 상위 레벨로 하겠다 라는 것입니다

이렇게 되면 부서별 마지막 사람이 최하위 레벨이 되며

sys_connect_by_path를 사용하여 모든 행의 이름을 한 칼럼에 노출시킬 수 있습니다

 

sys_connect_by_path

데이터를 읽어오는 그 행의 모든 상위 레벨을 나타낼 수 있게 도와주는 함수입니다

읽어오는 그 행이 최상위라면 자기 행의 데이터만 가져오게 될 것이며,

최하위라면 모든 레벨의 데이터를 가져오게 될 것입니다

 

level

connect by prior를 사용하게 되면 트리구조로 그 행의 트리 레벨을 표현할 수 있습니다

그래서 위와 같이  where level = cnt라는 것은

총인원수를 와 같은 레벨만 보여달라는 것으로

deptno 10은 cnt가 3입니다

그래서 아래와 같이 10은 cnt가 3이며 level도 3인 행만 보여달라는 where 절이였습니다

 

 

---

 

 

where 절인 level 조건부를 제거하여 노출한 데이터

 

 

 

  select deptno,
         ename,
         row_number() over
                  (partition by deptno order by empno) rn,
         count(*) over
                  (partition by deptno) cnt
   from emp

 

 

 

위의 서브 쿼리를 출력할 경우 아래와 같이 노출됩니다

 

서브쿼리만 출력한 데이터

 

 

이렇게 트리구조에 대해서 알아보았습니다

확실히 트리구조는 무척이나 어렵습니다

처음 접하신 분은 이해가 어려울 수도 있을 것 같습니다

하지만 각각 쓰는 구문들이 어떤 역할을 하는지만

정확하게 알고 있다면 다른 사람이 작성한 코드를 분석하는데

많은 도움이 될 것이라 생각합니다

 

힘내시고 화이팅하세요!!

아직 모험은 끝나지않았습니다!!

파이팅!!