오라클 LEVEL을 이용한 계층형쿼리 쉽게 만들기

계층형 쿼리는 워낙 많이 쓰이고 있고, 프로젝트 개발환경에 따라서 다양한 방법을 사용하고 있으니 그에 따라 적용을 하시면되시구요. 제가 설명드리는건 간단한 개념만 말씀드릴께요.

 

계층형쿼리를 들어가기전에 LEVEL에 대해서 잘모르시겠으면 아래 포스팅을 참고하세요.

 

참고 : 오라클 LEVEL 잘 사용하기(가로컬럼을 세로로 변환)

 

아래쿼리는 간단하게 계층형 쿼리를 만드는 예제입니다. 계층을 만드실려면 일단 구조부터 파악을 하셔야 되는데요. 기본적으로 필요한건 3가지가 있습니다.

 

첫번째 메뉴의 순번입니다. 핵심적인 부분은 아니지만 정렬해서 목록을 보여줄때 필요한 부분입니다. 넘버링 개념으로 생각하시면 됩니다.

 

두번째 해당되는 메뉴의 계층이 필요합니다. 상위부서와 하부부서를 구분할 수있게 계층을 나뉘어야 합니다.

 

세번째 세부메뉴의 상위그룹에 대한 순번입니다. 상위부서와 하부부서를 구분할수 있으면 다시 상하관계에 따른 넘버링을 해서 조직구조를 나타낼수 있기 때문이죠.

 

 

 

 

 

 

사용예제 쿼리는 아래와 같습니다.

 

SELECT LEVEL as Depth,
       LPAD(' ',2*(LEVEL-1))||a.MenuName as MenuName
  FROM (
        SELECT 1 as MenuNo, 0 as ParentNo, '회사' as MenuName FROM dual
          UNION
          SELECT 2,1, '기획처' FROM dual   
          UNION
          SELECT 3,1, '관리처' FROM dual
          UNION
          SELECT 4,2, '기획1팀' FROM dual
          UNION
          SELECT 5,2, '기획2팀' FROM dual
          UNION
          SELECT 6,3, '예산지원팀' FROM dual
          UNION
          SELECT 7,3, '기업지원팀' FROM dual
        )A
        START WITH a.MenuNo = 1
        CONNECT BY PRIOR a.MenuNo = a.ParentNo

 

 

쿼리 사용 결과입니다.

 

         
제일 상위 부서로서 회사가 있습니다. 회사밑의 처실에는 기획처와 관리처가 있네요.
기획처에는 기획1팀과 기획2팀이 있습니다. 관리처에도 예산지원팀, 기업지원팀이 있네요.

 

회사
 기획처
  기획1팀
  기획2팀
 관리처 
  예산지원팀
  기업지원팀

 

이렇게 구조를 파악할 수 있게 되었는데 여기에 오라클이 알아들을 수 있게 숫자를 붙여주어야 됩니다. 그룹화와 전체 넘버링를 해보도록 하겠습니다. 전체넘버링은 표시할 순서를 정하고 그룹은 계층을 나타냅니다.

 

1. 0.회사
2.  1.기획처
3.   2.기획1팀
4.   2.기획2팀
5.  1.관리처 
6.   2.예산지원팀
7.   2.기업지원팀

 

 

처음의 넘버링에 따라서 모든 부서가 순차적으로 나오고 있습니다. 이걸 꺼구로 생각하면 오라클에서 넘버링을 어떻게 해야할지 알수가 있다는겁니다.

 

SELECT 1 AS MenuNo, 0 AS ParentNo, '회사' AS MenuName FROM DUAL
UNION
SELECT 3, 1, '관리처' FROM DUAL
UNION
SELECT 2, 1, '기획처' FROM DUAL
UNION
SELECT 4, 2, '기획1팀' FROM DUAL
UNION
SELECT 5, 2, '기획2팀' FROM DUAL
UNION
SELECT 6, 3, '예산지원팀' FROM DUAL
UNION
SELECT 7, 3, '기업지원팀' FROM DUAL

 

쿼리실행결과 입니다.

 

 

처음컬럼을 MENUNO로서 계층순서를 나타내고 있습니다. 두번째 PARENTNO 컬럼은 그룹을 결정하는 컬럼으로 0 < 1 < 2 < 3 이렇게 부서가 확장되고 있네요.

 

다시 제일 처음의 쿼리를 살펴보면,

 

START WITH a.MenuNo = 1
CONNECT BY PRIOR a.MenuNo = a.ParentNo

 

 

START WITH 로서 시작을 MenuNo 1번으로 시작을하고, 상위메뉴와 하위메뉴를 연결합니다. CONNECT BY PRIOR이 상위와 하위를 연결하는 기능을 가지며 각 그룹에 해당되는 코드를 반복을 합니다.

 

즉, 기획처는 그룹코드가2번이며, 기획1팀과 기획2팀은 부모코드가 2번이고, 자신의 세부코드는 4번과5번이 됩니다.

 

CONNECT BY PRIOR로 부모코드가 2번으로묶여서 2번 반복합니다. 같은 원리로 관리처도 마찬가지로동작을 하구요. LEVEL은 부모코드 1번부터 동작을 하는데 각각 넘버링됩니다.

 

처음 a.MenuNo가 1이고 LEVEL 이 1일때 회사
다음번 CONNECT BY PRIOR 조건이 없어서 다음으로

두번째 a.MenuNo가 2이고 LEVLE이 2일때 기획처
a.MenuNo가 2이고 LEVLE이 3일때 기획1팀, 기획2팀

............

 

표로 살펴보면 좀더 쉽게 알아볼 수 있습니다.

 

a.MenuNo	LEVEL	MENUNAME
1	1	회사
2	2	기획처
2	3	기획1팀
2	3	기획2팀
3	2	관리처
3	3	예산지원팀
3	3	기업지원팀

 

 

자, 계층형 쿼리에 대해서 좀 쉽게 이해를 하셨나요? 조금이나마 이해하시는데 도움이 되면 좋겠습니다.