여행을 개발하다

[Oracle] 계층형 쿼리 - START WITH ~ CONNECT BY 구문 본문

데이터베이스/Oracle

[Oracle] 계층형 쿼리 - START WITH ~ CONNECT BY 구문

yhtragramming 2020. 3. 8. 19:09

 

오라클의 계층형 쿼리와 대표적인 계층형 쿼리 구문인 START~WITH 구문에 대해 이야기해보고자 한다.

먼저 계층형 쿼리가 무엇인지에 대해 알아본다.

 

계층형 쿼리?

- 한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관계를 이루며 존재할 때, 이 관계에 따라 레코드를 hierarchical(상하위) 한 구조로 가져올 때 사용되는 SQL을 의미한다.

 

역시 단어 그 자체로만 설명하려니 어려운데, 쉽게 생각하면 조직도, 메일함, 문서함 등을 예로 들 수 있다.

회사 조직도

위 회사 조직도는 최상위에 A 회사를 중심으로 경영지원부, 영업본부로 나뉜다. 그리고 경영지원부는 다시 회계팀, 인사팀, 재무팀, 영업본부는 국내영업팀, 해외영업팀 등으로 나뉜다. 이때, 회계팀, 인사팀, 재무팀의 부모 부서는 경영지원부가 된다. 거꾸로 회계팀, 인사팀, 재무팀은 경영지원부의 자식 부서가 된다(영업본부도 동일한 구조이므로 설명 생략).

 

이들의 관계를 테이블로 구성해볼 것인데, 테이블명은 'TB_CMM_DEPT'로 한다.

CREATE TABLE TB_CMM_DEPT
( 
   DEPT_CD     VARCHAR2(8) NOT NULL PRIMARY KEY,
   PAR_DEPT_CD VARCHAR2(8),
   DEPT_NM     VARCHAR2(50),
   USE_YN      CHAR(1),
   REG_DTM     DATE,
   REG_USER    VARCHAR2(30),
   MOD_DTM     DATE,
   MOD_USER    VARCHAR2(30)
);
TABLE_NAME	COLUMN_NAME	  DATA_TYPE	  NULLABLE	IS_PK	 COMMENTS
----------  ------------  ----------  --------  -----   ----------
TB_CMM_DEPT	DEPT_CD	      VARCHAR2(8)		      Y	      부서코드
TB_CMM_DEPT	PAR_DEPT_CD	  VARCHAR2(8)	  Y		        상위부서코드
TB_CMM_DEPT	MOD_USER	  VARCHAR2(30)	  Y		           수정자
TB_CMM_DEPT	REG_USER	  VARCHAR2(30)	  Y		           등록자
TB_CMM_DEPT	USE_YN	          CHAR	      Y		          사용여부
TB_CMM_DEPT	REG_DTM	          DATE	      Y		           등록일
TB_CMM_DEPT	DEPT_NM	       VARCHAR2(50)	  Y		           부서명
TB_CMM_DEPT	MOD_DTM           DATE	      Y		           수정일자

 

계층형 구조의 레코드를 저장하기 위해서는 각 부서를 유일무이하게 구분할 수 있는 부서 코드(DEPT_CD)를 Primary Key로 잡고, 상위부서를 표현하는 상위부서 코드(PAR_DEPT_CD)를 별도로 구성한다. 그리고 위의 조직도에 맞게 레코드를 Insert 하면 다음과 같다.

SELECT   DEPT_CD, 
         PAR_DEPT_CD,
         DEPT_NM,
         USE_YN
	
   FROM TB_CMM_DEPT;
DEPT_CD	    PAR_DEPT_CD	  DEPT_NM	   USE_YN
---------  ------------- ----------  -----------
DEPT_001	  NULL        A회사	         Y
DEPT_002	DEPT_001	  경영지원부	     Y
DEPT_003	DEPT_001	  영업본부	     Y
DEPT_004	DEPT_002	  회계팀          Y
DEPT_005	DEPT_002	  인사팀	         Y
DEPT_006	DEPT_002	  재무팀	         Y
DEPT_007	DEPT_003	  국내영업팀	     Y
DEPT_008	DEPT_003	  해외영업팀	     Y
DEPT_009	DEPT_003	  영업기획팀  	 Y
DEPT_010	DEPT_003	  영업지원팀	     Y

 

이러한 구조의 테이블을 상 하위 구조의 일목요연한 레코드로 SELECT 할 때 사용하는 것이 오라클의 START WITH ~ CONNECT BY 구문이다.

 

 

START WITH ~ CONNECT BY

 

START WITH

- 계층 질의의 루트(부모행)로 사용될 행을 지정한다.

- 서브 쿼리를 사용할 수도 있다.

 

START WITH 구문에서는 어떤 레코드를 최상위 레코드로 잡을지 지정한다.

 

예제로 언급한 TB_CMM_DEPT이라는 테이블을 살펴본다. 사용자가 지정하기에 따라 A 회사가 최상위가 될 수도 있고, 그 아래의 부서가 최상위가 될 수도 있다.

 

 

* A 회사(DEPT_001)가 최상위 레코드인 경우

SELECT * 
       FROM TB_CMM_DEPT
       START WITH PAR_DEPT_CD IS NULL

 

*경영지원부(DEPT_002)가 최상위 레코드인 경우

SELECT * 
       FROM TB_CMM_DEPT
       START WITH PAR_DEPT_CD = 'DEPT_002'

 

* 서브 쿼리를 사용할 경우 (부서명이 '경영지원부' 부서 코드를 조건에 넣음)

SELECT * 
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = (   SELECT DEPT_CD 
                              FROM  TB_CMM_DEPT
                             WHERE DEPT_NM  = '경영지원부'
                            )

 

 

CONNECT BY

- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정할 수 있다.

-PRIOR연산자와 함께 사용하여 계층구조로 표현할 수 있다.

-CONNECT BY PRIOR자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리 구성 (Top Down)

-CONNECT BYPRIOR부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)

-CONNECT BY NOCYCLE PRIOR: NOCYCLE 파라미터를 이용하여 무한 루프 방지

- 서브 쿼리를 사용할 수 없다.

 

 

어떤 느낌인지 확실하게 와닿진 않는데, 다음 질문에 대한 답을 명시해 주는 곳이라고 생각하면 이해하기 편하다.

지정된 최상위 루트 ROW와 연관된 레코드(부모 혹은 자식)를 어떻게 전개해 나갈 것인가?
상위(부모) 방향으로, 아님 하위(자식) 방향으로?

 

만약 다음의 SQL을 통해 최상위 ROW를 선택했다고 가정한다.

SELECT * 
       FROM TB_CMM_DEPT
       START WITH PAR_DEPT_CD = 'DEPT_001'

 

그럼 최상위 ROW는 총 2개로, 경영지원부와 영업본부가 선택된다.

이때, 최상위로 지정한 ROW에서 어떤 방향으로 계층(트리) 전개를 할 것인가를 지정하는 부분이다. 여기서 부모행 방향의 선별 기준은 START WITH 구문에 지정된 컬럼이다(PAR_DEPT_CD).

부모행 방향으로 전개할 것인가? (Bottom-Up)
자식행 방향으로 전개할 것인가? (Top-Down)

그리고 방향은 'PRIOR'가 어디에 지정되어 있냐에따라 달라지는데, PRIOR가 없는 곳에서 PRIOR가 있는 곳으로 전개된다.

예를 들어, 아래 쿼리는 부모행 방향의 기준은 'PAR_DEPT_CD'이며, PRIOR 역시 부모행에 붙어있다. 결국부모행 방향으로 전개되는 Bottom-Up 방식으로 레코드가 선택된다.

SELECT DEPT_CD,
       PAR_DEPT_CD,
       DEPT_NM 
       FROM TB_CMM_DEPT
       START WITH PAR_DEPT_CD = 'DEPT_001'
       CONNECT BY PRIOR PAR_DEPT_CD =   DEPT_CD;
DEPT_CD	   PAR_DEPT_CD	 DEPT_NM
---------  -----------  ---------
DEPT_002	DEPT_001	경영지원부
DEPT_001		          A회사
DEPT_003	DEPT_001	영업본부
DEPT_001		          A회사

 

이번에는 반대로 자식행 방향으로 PRIOR를 걸어본다.

SELECT DEPT_CD,
       PAR_DEPT_CD,
       DEPT_NM 
       FROM TB_CMM_DEPT
       START WITH PAR_DEPT_CD = 'DEPT_001'
       CONNECT BY PAR_DEPT_CD =  PRIOR DEPT_CD;
DEPT_CD	   PAR_DEPT_CD	 DEPT_NM
---------  ----------  ------------
DEPT_002	DEPT_001	경영지원부
DEPT_004	DEPT_002	회계팀
DEPT_005	DEPT_002	인사팀
DEPT_006	DEPT_002	재무팀
DEPT_003	DEPT_001	영업본부
DEPT_007	DEPT_003	국내영업팀
DEPT_008	DEPT_003	해외영업팀
DEPT_009	DEPT_003	영업기획팀
DEPT_010	DEPT_003	영업지원팀

 

 

LEVEL Pseudocolumn

- LEVEL은 계층구조 쿼리에서 수행 결과의 Depth를 표현하는 의사컬럼이다.

 

의사컬럼이란, '가짜 컬럼'을 의미한다. 즉, 테이블 생성 시에 DBA나 생성자가 별도로 정의한 컬럼이 아니다. 단순하게 레코드들이 최상의 레코드로부터 몇 단계 깊이에 있는지를 참고하는 용도로만 사용된다.

SELECT  LEVEL AS LVL,
        TCD.DEPT_CD,
        TCD.PAR_DEPT_CD,
        TCD.DEPT_NM
  FROM  TB_CMM_DEPT TCD
  START WITH TCD.PAR_DEPT_CD = 'DEPT_001'
  CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD;

 

위 SQL 질의 시, 아래와 같은 결과가 나타난다. 최상위 ROW에 해당하는 경영지원부 및 영업본부의 LEVEL은 1로 시작되며, 그 하위 부서들은 단계별로 숫자가 가산되어 Depth가 표현된다. 예를 들어, 인사팀 아래에는 '근태관리파트'가 있다고 가정하면, 근태관리파트의 LEVEL은 3이 되는 것이다.

LVL	  DEPT_CD	 PAR_DEPT_CD	DEPT_NM
---  ---------  -----------   -------------
1	DEPT_002	DEPT_001	경영지원부
2	DEPT_004	DEPT_002	회계팀
2	DEPT_005	DEPT_002	인사팀
2	DEPT_006	DEPT_002	재무팀
1	DEPT_003	DEPT_001	영업본부
2	DEPT_007	DEPT_003	국내영업팀
2	DEPT_008	DEPT_003	해외영업팀
2	DEPT_009	DEPT_003	영업기획팀
2	DEPT_010	DEPT_003	영업지원팀

 

 

ORDER SIBLINGS BY + 컬럼명

- ORDER SIBLINGS BY 절을 사용하면 계층구조 쿼리에서 편하게 정렬 작업을 할 수 있다.

 

'ORDER SIBLINGS BY + 컬럼명'을 명시하면, 계층 구조의 레코드를 가독성 있게 가져올 수 있다.

이를 단순히 ORDER BY를 사용할 때와 비교해본다.

-- 1. ORDER BY 사용시 
SELECT  LEVEL AS LVL,
        TCD.DEPT_CD,
        TCD.PAR_DEPT_CD,
        TCD.DEPT_NM
  FROM  TB_CMM_DEPT TCD
  START WITH TCD.PAR_DEPT_CD = 'DEPT_001'
  CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD
  ORDER BY TCD.DEPT_CD;
-- 1. ORDER BY 결과
LVL	 DEPT_CD	PAR_DEPT_CD	   DEPT_NM
--- ---------  ------------  ---------------
1	DEPT_002	DEPT_001	경영지원부
1	DEPT_003	DEPT_001	영업본부
2	DEPT_004	DEPT_002	회계팀
2	DEPT_005	DEPT_002	인사팀
2	DEPT_006	DEPT_002	재무팀
2	DEPT_007	DEPT_003	국내영업팀
2	DEPT_008	DEPT_003	해외영업팀
2	DEPT_009	DEPT_003	영업기획팀
2	DEPT_010	DEPT_003	영업지원팀

기준 컬럼을 동일하게 DEPT_CD로 지정하면, ORDER BY는 DEPT_CD 레코드 그 자체의 알파벳(사전순) 순으로만 정렬된다. 하지만, ORDER SIBLINGS BY를 사용하면 알파벳순만 아닌 계층 구조에 최적화된 상태로 레코드를 가져올 수 있다.

-- 2. ORDER SIBLINGS BY 사용시
SELECT  LEVEL AS LVL,
        TCD.DEPT_CD,
        TCD.PAR_DEPT_CD,
        TCD.DEPT_NM
  FROM  TB_CMM_DEPT TCD
  START WITH TCD.PAR_DEPT_CD = 'DEPT_001'
  CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD
  ORDER SIBLINGS BY TCD.DEPT_CD;
-- 2. ORDER SIBLINGS BY 결과
LVL	 DEPT_CD	PAR_DEPT_CD	 DEPT_NM
--- ---------  ------------ ----------
1	DEPT_002	DEPT_001	경영지원부
2	DEPT_004	DEPT_002	회계팀
2	DEPT_005	DEPT_002	인사팀
2	DEPT_006	DEPT_002	재무팀
1	DEPT_003	DEPT_001	영업본부
2	DEPT_007	DEPT_003	국내영업팀
2	DEPT_008	DEPT_003	해외영업팀
2	DEPT_009	DEPT_003	영업기획팀
2	DEPT_010	DEPT_003	영업지원팀

 

 

CONNECT BY의 실행 순서는 다음과 같다.

- 첫째START WITH절

- 둘째CONNECT BY절

- 셋째WHERE절 순서로 풀리게 되어있다.

출처 :http://www.gurubee.net/lecture/1300

 

MS-SQL로 계층형 쿼리를 만들어나갈 때는 WITH 절을 별도로 구성하여 Depth 및 레코드를 정렬해왔다.

하지만 오라클의 경우 이 구문을 통해 보다 쉽게 계층형 쿼리를 작성할 수 있다.

 

지금까지 오라클의 START WITH ~ CONNECT BY 구문에 대해 알아보았다.

 

* 부족한 부분이나 함께 토론해서 고쳐야 할 부분이 있다면 댓글 언제든지 환영합니다.

긴 글 읽어주셔서 감사합니다 : )

 

저작자표시

'데이터베이스 > Oracle' 카테고리의 다른 글

[Oracle] XMLELEMENT, XMLAGG 함수 - 다수 레코드 1열로 가져오기  (0) 2020.03.07
[Oracle] Data Dictionary  (0) 2020.02.09
'데이터베이스/Oracle' Related Articles more
Comments