(Oracle) 계층형 쿼리 START WITH CONNECT BY PRIOR

Oracle 계층형 쿼리 START WITH CONNECT BY PRIOR

oracle START WITH CONNECT BY

오라클 쿼리 분석 과정에서 'START WITH ~ CONNECT BY PRIOR (+ ORDER SIBLINGS BY)'라는 계층형 쿼리 구문을 발견하여 관련 내용을 정리해 보았습니다.

 

 

계층형 쿼리란?

데이터베이스에서 계층적인 구조를 가진 데이터를 조회하거나 조작하기 위해 사용되는 쿼리입니다.

대표적으로 '기업의 조직도', '제품 카테고리', '게시글 및 댓글' 같은 데이터 유형이 계층 구조를 가질 수 있으며, 이러한 데이터를 효율적으로 다루기 위해 계층형 쿼리를 사용합니다.

 

---

START WITH ~ CONNECT BY PRIOR

'START WITH ~ CONNECT BY PRIOR' 구문은 오라클에서 계층형 쿼리를 위해 지원되는 기능인데요.

아래 내용을 통해 예시에 사용될 테이블 및 데이터, 그리고 실행 쿼리를 먼저 보고 해당 쿼리에 대한 세부적인 내용을 살펴보겠습니다.

 

예시로 사용할 조직도 계층

(해당 이미지와 같은 회사의 조직도를 데이터화하여 계층형 쿼리를 사용해 볼 것입니다.)

 

 

CREATE TABLE TB_CO_DEPT
(
    DEPT_NM        VARCHAR2(20),	
    DEPT_CD        VARCHAR2(20),
    PAR_DEPT_CD    VARCHAR2(20)
);

COMMENT ON COLUMN TB_CO_DEPT.DEPT_NM IS '부서명';
COMMENT ON COLUMN TB_CO_DEPT.DEPT_CD IS '부서 코드';
COMMENT ON COLUMN TB_CO_DEPT.PAR_DEPT_CD IS '상위 부서 코드';

(테이블 생성 쿼리)

 

예시에 사용될 테이블은 필수 데이터만 가지고 간단하게 만들었습니다.

각각의 컬럼은 SQL 문에서 보시는 것과 같이 '부서명', '부서 코드', '상위 부서 코드'를 나타냅니다.

 

 

INSERT ALL
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('A회사', 'A0000', null)
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('a부서', 'a0000', 'A0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('b부서', 'b0000', 'A0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('c부서', 'c0000', 'A0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('a1팀', 'a0001', 'a0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('a2팀', 'a0002', 'a0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('b1팀', 'b0001', 'b0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('b2팀', 'b0002', 'b0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('c1팀', 'c0001', 'c0000')
	INTO TB_CO_DEPT (DEPT_NM, DEPT_CO, PAR_DEPT_CD) VALUES ('c2팀', 'c0002', 'c0000')
SELECT * FROM DUAL;

(예시로 사용할 데이터 추가)

 

예시에 사용될 데이터이며, 조직도의 최상위 데이터인 'A회사'의 경우 'PAR_DEPT_CD(상위 부서 코드)'가 null 입니다.

 

 

SELECT * 
FROM TB_CO_DEPT
START WITH PAR_DEPT_CD IS NULL
CONNECT BY PRIOR DEPT_CD = PAR_DEPT_CD;

(실행한 계층형 쿼리)

 

위에서 생성한 테이블 및 데이터를 기준으로 'TB_CO_DEPT' 테이블에 대해 'START WITH ~ CONNECT BY PRIOR' 계층형 쿼리를 사용했을 때, 아래와 같이 결과가 계층형 구조로 반환되는 것을 볼 수 있는데요.

 

START WITH CONNECT BY PRIOR 결과

계층형 쿼리의 수행 순서 및 세부 내용은 다음과 같습니다.

 

 

1. START WITH : 트리 구조의 최상위 노드를 지정하는 구문입니다.

예시에서 상위 부서 코드가 없는 'A회사'를 최상위 노드로 지정하기 위해 'PAR_DEPT_CD IS NULL' 조건이 사용되었으며, 예를 들어 'a부서'를 최상위 노드로 하위 계층을 조회하려는 경우 DEPT_CD = 'a0000'과 같은 조건을 사용할 수 있습니다.

이때 최상위 노드를 지정하는 START WITH 구문에서는 필요에 따라 '서브 쿼리'도 사용될 수 있습니다.

 

 

2. CONNECT BY : 상위 노드와 하위 노드의 관계를 지정하는 구문으로 계층 구조를 탐색할 때 사용됩니다.

'PRIOR' 키워드를 통해 상위 노드와의 관계를 나타내며, 예시에서는 상위 노드의 부서가 하위 노드의 상위 부서 코드라는 것을 나타내기 위해 'PRIOR DEPT_CD = PAR_DEPT_CD'와 같은 조건이 사용되었습니다.

위 쿼리에서는 'PRIOR DEPT_CD = PAR_DEPT_CD' 조건을 통해 'Top-Down' 방식의 계층 조회를 실행했다면, 필요에 따라 'PRIOR PAR_DEPT_CD = DEPT_CD' 조건을 통해 'Bottom-Up' 계층 조회도 가능합니다.

CONNECT BY 구문에서는 START WITH 구문과는 다르게 '서브 쿼리'를 사용할 수 없다는 특징이 있습니다.

 

 

SELECT *
FROM TB_CO_DEPT
START WITH DEPT_CD = 'c0002'
CONNECT BY PRIOR PAR_DEPT_CD = DEPT_CD;

('c2팀'을 기준으로 Bottom-Up 계층 조회를 하는 쿼리 예시)

 

 

---

LEVEL 및 ORDER SIBLINGS BY

'LEVEL'과 'ORDER SIBLINGS BY'는 계층 구조 쿼리에서 추가적으로 사용되는 구문입니다.

 

START WITH CONNECT BY 구문 LEVEL

먼저 'LEVEL'의 경우 계층 구조 쿼리에서 수행 결과의 Depth를 표현하는 가상의 컬럼입니다.

최상위 노드 기준 LEVEL 1부터 시작하여 하위 노드 레벨로 내려갈수록 값이 증가하며, 최상위 노드로부터 몇 단계 깊이에 있는지 참고하는 용도로 사용됩니다.

 

 

START WITH CONNECT BY ORDER SIBLINGS BY

이어서 'ORDER SIBLINGS BY'는 계층형 쿼리 결과(계층 구조를 유지한 상태)에서 형제 노드 간의 정렬을 지정할 때 사용되는 구문입니다.

 

 

여기까지 오라클에서 계층형 데이터 구조에 사용되는 'START WITH ~ CONNECT BY PRIOR'에 대해서 살펴봤습니다.

내용 중 잘못된 부분이나 궁금하신 부분은 댓글 남겨주시면 확인하겠습니다. 감사합니다.

 

 

 

< 참고 자료 >

https://tragramming.tistory.com/82