[Oracle] 오라클 계층형 쿼리(START WITH.. CONNECT BY)

계층형 쿼리란?

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태를 말합니다. 예를 들자면 특정회사의 부서, 특정학교의 학과등이 있습니다. 계층형 쿼리는 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말합니다. 오라클에서의 계층형 쿼리는 START WITH ... CONNECT BY 절로 생성할 수 있으며 계층형 정보를 표현하기 위한 목적으로 오라클 8부터 지원되었습니다.

 

수행순서

1. START WITH 절에 시작 조건을 찾습니다.

2. CONNECT BY 절에 연결조건을 찾습니다.

 

샘플데이터 생성

위와같은 구조를 이루도록 부서 테이블을 생성하도록 하겠습니다.

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시스템');

 

계층형 쿼리 START WITH.. CONNECT BY 사용법

SELECT [컬럼]...
FROM [테이블]
WHERE [조건]
START WITH [최상위 조건]
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

SELECT 
DEPT_NAME,
DEP_CD,
PARENT_CD,
LEVEL
FROM DEP 
START WITH PARENT_CD IS NULL --최상위노드 설정,
CONNECT BY PRIOR DEP_CD = PARENT_CD;--부모노드와 자식노드 연결

 

START WITH 조건에 계층형 구조의 최상위 계층의 로우를 식별하는 조건을 넣습니다. 위의 예제에서는 최상위 코드의 PARENT_CD가 NULL값이므로 PARENT_CD IS NULL로 조건을 주었습니다. CONNECT BY조건에서는 계층형 구조가 어떤식으로 연결되는지 표현해주면 됩니다. 위 예제에서는 부서코드(DEP_CD)에 상위부서코드(PARENT_CD)를 가지고 있으므로 DEP_CD = PARENT_CD로 표현하였습니다. 마지막 컬럼에 나열한 LEVEL컬럼은 레벨 의사컬럼(LEVEL Pseudocolumn)이라고 하는데 계층형 정보를 표현할때 위 그림의 빨간색 박스와 같이 그 계층의 레벨을 나타내줍니다. 이 레벨 의사컬럼을 다양하게 응용할 수 있습니다. 가장 많이 응용하는 방법이 레벨별로 들여쓰기를 하여 좀 더 직관적으로 데이터를 표현하는것입니다.

 

레벨의사컬럼의 활용 레벨별로 들여쓰기

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;--부모노드와 자식노드 연결

레벨-1 * 공백(2)를 앞에 삽입하여 좀 더 계층형 데이터를 직관적으로 데이터를 만들었습니다. 어떠한가요? 좀 더 계층형 데이터가 직관적으로 보이나요? 위와같이 들여쓰기를 해놓으면 데이터를 확인하기도 훨씬 편합니다.