Oracle DB의 계층, 그리고 순환 쿼리

옥수수수염챠 2020. 3. 19. 13:19

오라클 계층 쿼리(Hierarchical Query)

계층형 쿼리란 ?

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형래를 말한다. 크게는 회사의 조직도, 한 가족의 가계도에서 부터 학교의 학과구조 등이 있다. 계층형 쿼리는 이러한 계층 구조가 적용된 데이터를 반환하는 질의어이며, RDBMS중에서도 유일하게 Oracle만 이 계층형 쿼리를 지원한다.(즉 다른 DB에서는 계층형쿼리가 아닌 아래의 순환쿼리를 이용해서 작업을 해주어야 한다.)

계층형 쿼리에서 자주 사용되는 용어

계층형 구조는 나무를 거꾸로 세워놓은 것 같은 형태로 보여 트리구조라고도 한다. 그렇기 때문에 관련 용어들도 트리구조의 용어와 유사한 부분이 많다.

명칭	설명
Level	각 계층을 나타낸다.
Node	각 데이터를 칭한다.
Root	가장 첫번째 노드를 말한다.
Parent Node	노드의 상위노드
Child Node	노드의 자식노드
Leaf	가장 아래 Level의 노드(자식 노드가 없는 노드)

샘플 예제와 설명

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE DEP (
     DEP_CD NUMBER NOT NULL, -- 부서코드
     PARENT_CD NUMBER, -- 상위부서 코드
     DEPT_NAME VARCHAR2(100) NOT NULL, -- 부서이름
     PRIMARY KEY (DEP_CD)
);
 
INSERT INTO DEP VALUES ( 101, NULL, '총괄개발부');
INSERT INTO DEP VALUES ( 102, 101, '모바일개발센터');
INSERT INTO DEP VALUES ( 103, 101, '웹개발센터');
INSERT INTO DEP VALUES ( 104, 101, '시스템개발센터');
 
INSERT INTO DEP VALUES ( 105, 102, '쇼핑몰(모바일)');
INSERT INTO DEP VALUES ( 106, 103, '외주SI');
INSERT INTO DEP VALUES ( 107, 103, '쇼핑몰');
INSERT INTO DEP VALUES ( 108, 105, '전산지원팀');
INSERT INTO DEP VALUES ( 109, 106, '구축1팀');
INSERT INTO DEP VALUES ( 100, 106, '구축2팀');
INSERT INTO DEP VALUES ( 111, 104, 'ERP시스템');

cs

조회 결과 : 
계층형 쿼리 문법 : 



1
2
3
4
5

SELECT [컬럼]...
FROM [테이블]
WHERE [조건]
START WITH [루트노드]
CONNECT BY [NOCYCLE][PRIOR 연결 조건];



START WITH : 어느 노드부서 조회할지 지정한다.
CONNECT BY : 부모노드와 자식노드의 관계를 연결한다.
TOP-DOWN : 최상위노드부터 자식노드 순서대로 조회하고 싶을 경우, PRIOR 부모노드 = 자식노드의 부모노드 
BOTTLE-UP : 최하위노드부터 최상위노드 순대로 조회하고 싶을 경우, 반대로 PRIOR 자식노드 = 부모노드로 걸어주면 된다.

 

1
2
3
4
5
6
7
8

SELECT 
DEPT_NAME,
DEP_CD,
PARENT_CD,
LEVEL
FROM DEP 
START WITH PARENT_CD IS NULL --루트노드 지정
CONNECT BY PRIOR DEP_CD = PARENT_CD; --부모노드와 자식노드 

조회 결과 :

위의 결과에서 보면 알 수 있듯이, 오라클에서는 자동으로 LEVEL이라는 데이터를 제공한다.

이를 응용하여 각 부서의 계층구조 별로 부서명을 들여쓰기 할 수 있다.

1
2
3
4
5
6
7
8

SELECT 
LPAD(' ', 2*(LEVEL-1)) || DEPT_NAME AS DEPT_NAME, --레벨별 들여쓰기
DEP_CD,
PARENT_CD ,
LEVEL
FROM DEP 
START WITH PARENT_CD IS NULL --최 상위노드 설정,
CONNECT BY PRIOR DEP_CD = PARENT_CD;--부모노드와 자식노드 연결


조회 결과 : 
 

오라클 순환 쿼리(Recursion Query)

-- Upload 예정입니다 !

필자는 회사의 조직 Level을 조회하는 쿼리를 작성해야 했다.

일반적인 경우 Oracle의 계층쿼리를 이용하여 작성하면 되지만, 이 케이스는 조금 더 특수하다.

왜냐하면 , 바로 부서의 조직 Level을 예외로 관리하고 있기 때문이다.

기존의 조직 Level에서, 만일 모 부서가 예외로 Level을 하향조정(조직도 특성상 상향조정은 불가하다)할 경우, 예외로 지
정한 Level로 보여주어야 하며 그 하위부서는 상위부서의 조정된 Level을 기준으로 순차적으로 조정을 해야한다.

추가로, 숨김관리에 등록된 부서는 그 하위부서를 포함하여 조회가 되면 안된다.

처음에는 순환쿼리의 존재 자체를 몰라 오라클의 계층쿼리로 해결해보려고 하였지만,

계층쿼리에서 제공하는 Level에서는 예외 Level을 적용하기에 한계가 있어 구글링을 해본 끝에 순환쿼리의 존재에 대해 알게되었다!

* 참고 URL : https://livesql.oracle.com/apex/livesql/file/tutorial_GQMLEEPG5ARVSIFGQRD3SES92.html

결과적으로, 순환쿼리와 계층쿼리의 조합으로 해당 쿼리를 순조롭게 작성할 수 있었다.