계층형 구조(Hierarchical Structure)는 부모와 자식 간의 관계를 깊이로 구분하여 표현하는 것으로 트리구조, 보통 카테고리를 분류나 조직도에 많이 사용된다. 개발자들이 흔히 사용하는 Visual Studio나 이클립스와 같은 IDE의 Project 탐색기, 폴더의 탐색기에도 사용된다.
일반적으로 RDBMS에서는 행(Row) 단위로 데이터가 조회되어 다른 행과의 관계를 나타내는 계층형 구조를 조회하기 힘들다. 하지만, WITH문을 이용하면 계층형 구조의 데이터를 조회할 수 있다. Oracle은 10g버전부터 CONNECT BY문 을 이용하여 계층형 쿼리를 만들 수 있고, 11g R2 버전부터 Recursive Subquery Factoring 기능이 추가되어 WITH문으로 재귀가 가능하게 되었다.
이번 포스트에서는 Oracle을 기반으로 계층형 쿼리를 만드는 방법에 대해서 알아보도록 하겠다.
1. 계층형 쿼리(Hierarchical Queries)란?
1-1. 부모와 자식 간의 관계를 가지는 데이터에 깊이(Level)을 부여하여 조회하는 쿼리.
- 부모 노드와 자식 노드로 구성된 트리 구조를 조회하는 쿼리.
- 부모 노드가 없는 노드가 최상위 노드가 됨.
1-2. ANSI SQL방식의 WITH문을 재귀호출하여 구현할 수 있음.
- 오라클은 10g버전부터 CONNECT BY문으로 가능하며, 11g R2 버전부터 지원.
2. 예제 데이터.
2-1. Oracle을 설치하면 기본적으로 사용할 수 있는 SCOTT 유저의 EMP 데이터를 사용.
2-2. 데이터 확인.
- 모든 사원은 고유의 사원번호(EMPNO)를 가지고 있음.
- 관리자(MGR)가 있는 노드는 자식 노드가 되며, 관리자(MGR)는 부모 노드가 됨.
- KING(7839)은 부모 노드가 없고 자식 노드만 있는 최상위 노드임.
3. WITH절로 재귀를 사용하는 방법.
3-1. 다음의 조건을 만족해야함.
- 조회에 사용되는 컬럼명들을 WITH문을 정의할 때 반드시 명시.
- 시작조건이 되는 테이블과 SubQuery를 Driving으로 사용하는 테이블을 UNION ALL로 결합.
- 무한루프에 빠지지 않는 종료조건.
3-2. 1~10의 수를 출력하시오.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
WITH NUMBERS ( NUM ) AS (
SELECT 1 AS NUM
FROM DUAL
UNION ALL
SELECT A.NUM + 1 AS NUM
FROM NUMBERS A
WHERE 1=1
AND A.NUM < 10
)
SELECT T1.NUM
FROM NUMBERS T1
|
cs |
- 1 ln: WITH문으로 별칭이 [ NUMBERS ]인 SubQuery를 정의.
* WITH문을 재귀적으로 사용하려면 반드시 컬럼명을 명시해주어야 함.
- 2~3 ln: 시작 조건, 최상위 노드를 지정.
* CONNECT BY절에서 START WITH에 해당하는 부분.
* 생략할 경우 모든 노드에 대한 계층을 조회함.
- 4 ln: WITH문에서 재귀를 쓰기위해서는 반드시 UNION ALL로 연결해야함.
- 5 ln: 이전에 수행된 행을 다시 조회하면서 수를 1씩 더함.
- 6 ln: Driving테이블로 [1 ln]에서 정의한 [ NUMBERS ]를 사용하여 재귀호출.
- 8 ln: 재귀의 종료조건으로 최하위 노드에 접근했을 때까지만 조회.
* 종료조건을 제대로 설정하지않으면 무한루프에 빠지는 오류가 발생함.
4. CONNECT BY를 이용한 예제.
4-1. 사원번호가 7839인 KING을 기준으로 사원들의 관계를 조회.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT LEVEL
, T1.EMPNO
, T1.MGR
, T1.JOB
, LPAD(' ', (LEVEL-1)*2, ' ')||ENAME AS HIERARCHY
FROM SCOTT.EMP T1
WHERE 1=1
START WITH T1.EMPNO = 7839
CONNECT BY PRIOR T1.EMPNO = T1.MGR
ORDER SIBLINGS BY T1.EMPNO
|
cs |
- 1 ln: LEVEL은 CONNECT BY와 함께 사용되는 예약어로 계층의 깊이를 숫자로 나타냄.
- 5 ln: LEVEL을 이용하여 들여쓰기를 구현.
- 8 ln: 시작 조건, 최상위 노드를 지정.
* 생략할 경우 모든 노드에 대한 계층을 조회함.
- 9 ln: CONNECT BY절, PRIOR는 이전 결과셋의 컬럼을 참조하는 키워드.
* 현재 행의 MGR가 이전 결과셋의 EMPNO와 같은 것을 연결.
- 10 ln: CONNECT BY절에서 ORDER BY를 사용하면 계층이 깨지기 때문에 전용 키워드를 사용.
4-2. 실행 결과.
5. WITH문을 이용한 예제.
5-1. 사원번호가 7839인 KING을 기준으로 사원들의 관계를 조회.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
WITH EMP_HIERARCHY ( LVL, EMPNO, MGR, HIERARCHY ) AS (
SELECT 1 AS LVL
, A.EMPNO AS EMPNO
, A.MGR AS MGR
, A.ENAME AS HIERARCHY
FROM SCOTT.EMP A
WHERE 1=1
AND A.EMPNO = 7839
UNION ALL
SELECT A.LVL + 1 AS LVL
, B.EMPNO
, B.MGR
, LPAD(' ', A.LVL*2, ' ')||B.ENAME AS HIERARCHY
FROM EMP_HIERARCHY A
LEFT OUTER JOIN SCOTT.EMP B
ON B.MGR = A.EMPNO
WHERE 1=1
AND B.EMPNO IS NOT NULL
)
SEARCH DEPTH FIRST BY EMPNO SET SORT_ORDER
SELECT T1.LVL
, T1.EMPNO
, T1.MGR
, T1.HIERARCHY
FROM EMP_HIERARCHY T1
ORDER BY SORT_ORDER
|
cs |
- 1 ln: WITH문으로 별칭이 [ EMP_HIERARCHY ]인 SubQuery를 정의.
- 2~8 ln: 시작 조건, 최상위 노드를 지정.
* CONNECT BY절에서 START WITH에 해당하는 부분.
* 생략할 경우 모든 노드에 대한 계층을 조회함.
- 10 ln: 노드의 깊이를 나타내는 값으로 CONNECT BY절에서 LEVEL컬럼에 해당함.
- 13 ln: [10 ln]에서 만든 LEVEL을 이용하여 들여쓰기를 구현.
- 14 ln: Driving테이블로 [1 ln]에서 정의한 [ EMP_HIERARCHY ]를 사용하여 재귀호출.
- 15~16 ln: 현재 조회된 사원번호(A.EMPNO)가 관리자(B.MGR)인 사원을 조인.
- 18 ln: 재귀의 종료조건으로 최하위 노드에 접근했을 때까지만 조회.
* LEFT OUTER JOIN은 조인되는 대상이 없으면 NULL이므로 사원번호가 없는 경우를 탈출조건으로 설정.
- 20 ln: SEARCH DEPTH FIREST BY는 WITH문으로 조회된 데이터를 정렬하는 키워드.
* SEARCH DEPTH FIREST BY [ 기준컬럼명 ] SET [ 가상컬럼명 ].
* CONNECT BY절에서 ORDER SLIBINGS BY와 같이 계층 구조를 유지하면서 정렬하는 기능.
- 26 ln: 가상컬럼명으로 정렬을 실행.
5-2. 실행결과.
- 결과는 당연히 CONNECT BY절을 썼을 때와 같은 결과를 얻을 수 있음.
6. 내가 생각하는 오라클에서도 WITH문을 사용해야하는 이유.
6-1. 가장 큰 이유는 ANSI SQL이기 때문이다.
- CONNECT BY절은 오라클에서만 사용할 수 있는 쿼리이기 때문에 다른 DBMS에서는 사용할 수 없음.
6-2. 필요한 기능들을 직접 구현해서 사용할 수 있음.
- ORDER SLIBINGS BY절은 이미 정해진 기준에 따라서만 정렬할 수 있지만, WITH문을 사용하면 사용자의 요구에 맞게 정렬 조건을 구현할 수 있음.
- 이 외에도 무한루프에 빠지지않게 하는 조건 등 ORACLE에서 기본으로 제공하는 기능들은 대부분 구현할 수 있음.
6. 마치며.
- 웹페이지에서 메뉴나 조직도를 구성할 때 많이 사용되는 쿼리라서 포스트로 한번 다루어보았는데, 알아두면 유용하게 쓸 수 있다.
- 필자는 테스트 데이터를 만들 때, [ 3-2 ] 예제처럼 Row를 만들어서 바로 INSERT해버린다. 이렇게 테스트 데이터를 만들면 매우 편하다.
'DATABASE > ORACLE' 카테고리의 다른 글
| [PROCEDURE] 독립적인 트랜잭션 설정 (0) | 2020.07.23 |
|---|---|
| [SPOOL] SQL 스크립트를 출력하는 스풀링 (0) | 2020.04.24 |
| [SQL] WITH문 (2) | 2020.01.22 |
| [PL/SQL] 예제 (1) - 1~100의 수를 더하는 과정을 출력 (0) | 2020.01.22 |
| [PL/SQL] 무작정 시작하기 (5) - EXCEPTION 예외 (0) | 2020.01.16 |
댓글