[Oracle] CONNECT BY 계층형 쿼리가 어려울 때

1. 계층형?

 

오라클 예제 테이블인 EMP 테이블의 내용을 한번 보겠습니다. 

 

 

EMPNO가 직번이고, MGR이 관리자의 직번입니다.

1번 로우의 EMPNO가 '7839' KING은 회장이라 관리자란이 비어 있습니다. 

2번 로우의 EMPNO가 '7698' BLAKE는 상사직번이 7839(KING)으로 회장님이 직접 관리하는 직원이네요.

...

14번 로우의 EMPNO가 '7788' SCOTT의 관리자는 직번이 7566인 JONES 입니다.

 

EMP 테이블은 이런식으로 직원과 관리자를 관리하고 있습니다. 

이런 구조를 계층형 구조라고 합니다. 

 

 

2. EMP 계층구조를 보기좋게 만들어 주세요~

직원과 관리자의 구조를 보기좋게 만들어 달라는 요청이 들어왔습니다.

오라클에서 미리 CONNECT BY라는 좋은 쿼리를 만들어 놨습니다.

 

최종적으로 만들고자 하는 결과는 다음과 같습니다. 

 

 

순서대로 진행해보겠습니다. 

 

1) 먼저 대상이 되는 리스트를 결정합니다.

우리는 EMP 전체에 대해서 계층형쿼리를 작성할 것이기 때문에 WHERE 조건절 없이 쿼리를 작성해 줍니다.

 

SELECT *    FROM EMP

 

2) 최초 시작행을 정합니다. START WITH라는 명령어를 사용하면 됩니다.

우리는 관리자가 없는(최상위 관리자)부터 시작할 것이기때문에 

'MGR IS NULL'인 행을 시작점으로 합니다.

 

SELECT *   FROM EMP  START WITH MGR IS NULL

 

이 쿼리는 MGR이 NULL인 KING을 시작행으로 가져옵니다.

단, 이 쿼리는 실행되지 않습니다. 

CONNECT BY 절이 필수절이기 때문입니다. 

의외로 START WITH 절은 없어도 됩니다.

 

3) 최초행 이후 다음행을 어떤 조건으로 가져올 것인지를 정합니다. 

CONNECT BY 절을 이용하면 됩니다. 

 

SELECT EMPNO, ENAME, MGR   FROM EMP  START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR

 

CONNECT BY PRIOR EMPNO = MGR

이 절은 이전행의 EMPNO와 현재행의 MGR이 같은 행을 가져오라는 뜻입니다.

 

이렇게 해서 쿼리를 실행해보면 아래와 같은 결과가 나옵니다.

LEVEL은 CONNECT BY 절을 쓰면 오라클이 제공해주는 컬럼입니다. 

LEVEL 1이 제일 상위값입니다. 

 

 

결과를 보면

첫행은 START WITH 절로 MGR 값이 NULL인 KING을 가져왔고,

두번째행은 1번행의 EMPNO값 7839와 MRG값이 같은 JONES 값을 가져왔습니다.

세번째행은 2번째행의 EMPNO값 7566과 MGR값이 같은 SCOTT을 가져왔습니다. 

이런식으로 마지막 값까지 찾은 후 더 이상 하위계층이 없으면 상위로 올라가서 하위계층값을 찾습니다.

이것을 최종적으로 모든 행이 찾아질때까지 반복합니다.

 

위 결과에 대해 LPAD등을 이용해 약간만 작업을 해주면 최종결과를 얻을 수 있습니다.

 

SELECT LPAD(' ',(LEVEL-1)*5)||ENAME    FROM EMP   START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR

 

 레벨에 따라 들여쓰기를 한 쿼리입니다. 

1레벨이면 좌측에 0개의 빈공백이 생성되고

2레벨이면 좌측에 5개,

3레벨이면 좌측에 10개의 빈공백이 생성됩니다.

 

 

위 내용을 트리로 구성해보면 아래와 같습니다. 

 

 

 

 

최하위단까지 찾아가면 상위로 올라가서 하위단을 찾아갑니다.

ADAMS를 찾은 후 SCOTT으로 이동하여 하위계층이 있는 지 찾아봅니다. 

SCOTT에게 더 이상 하위계층이 없으면, JONES로 이동하여 하위단이 있는지 찾아봅니다.

FORD가 발견되었고, 하위단 SMITH까지 찾아갑니다.

위의 방식을 모든 대상을 찾을때까지 계속합니다. 

 

 

마지막으로 정렬에 대해 말씀드리겠습니다. 

CONNECT BY 쿼리에 아래와 같이 ORDER BY를 적용해 보겠습니다.

 

SELECT LPAD(' ',(LEVEL-1)*5)||ENAME AS CATEGORY    FROM EMP   START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR ORDER BY ENAME

 

결과는 다음과 같습니다. 

 

 

 

생각과 전혀 다른 결과입니다. 

계층별로 정렬을 하기 위해서는 SIBLINGS 라는 명령어를 사용하면 됩니다.

참고로 SIBLING은 '형제'라는 뜻으로, 형제 레벨끼리 정렬을 하라는 의미입니다. 

쿼리를 바꿔보면

 

SELECT LPAD(' ',(LEVEL-1)*5)||ENAME AS CATEGORY    FROM EMP   START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR ORDER SIBLINGS BY ENAME

 

ORDER BY 사이에 SIBLINGS를 넣어줍니다. 

 

 

같은 레벨끼리 ORDER BY가 된것을 확인할 수 있습니다.