[Oracle] 계층형 질의
계층형 질의란?
: 한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관계를 이루며 존재할 때, 이 관계에 따라 레코드를 hierarchical(상하위) 한 구조로 가져올 때 사용되는 SQL
회계팀, 인사팀, 재무팀 부모부서 => 경영지원부
국내영업팀, 해외영업팀, 영업지원팀 부모부서 => 영업본부
경영지원부 자식부서 => 회계팀, 인사팀, 재무팀
영업본부 자식부서 => 국내영업팀, 해외영업팀, 영업지원팀
먼저 위에 조직도에 맞춰 테이블을 생성한다.
CREATE TABLE TB_CMM_DEPT
(
DEPT_CD VARCHAR2(10) NOT NULL PRIMARY KEY,
PAR_DEPT_CD VARCHAR2(10),
DEPT_NM VARCHAR2(50)
);
[TB_CMM_DEPT 테이블 컬럼명]
DEPT_CD | 부서코드 |
PAR_DEPT_CD | 상위부서코드 |
DEPT_NM | 부서명 |
테이블을 생성한 후 데이터를 넣어준다.
※ START WITH
: 계층 구조 전개의 시작 위치를 지정하는 구문으로 즉, 루트 데이터를 지정한다.
ex01. A회사가 최상위 레코드인 경우
SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD IS NULL;
ex02. 경영지원부가 최상위 레코드인 경우
SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD='DEPT_002';
※ CONNECT BY
: 다음에 전개될 자식 데이터를 지정하는 구문
* PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정
- PRIOR 자식= 부모 : 부모->자식 방향으로 전개하는 순방향 전개
- PRIOR 부모 = 자식 : 자식->부모 방향으로 전개하는 역방향 전개
※ NOCYCLE
: 동일한 데이터를 반복해서 읽는 CYCLE 형성 방지
※ ORDER SIBLINGS BY
: 형제노드(동일한 LEVEL)의 데이터 사이에서 정렬 수행
1. 다음 SQL을 통해 최상위 ROW를 선택했다.
SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD='DEPT_001';
PAR_DEPT_CD = 'DEPT_001'인 경영지원부와 영업본부가 선택된다.
이때, 최상위로 지정한 ROW에서 어떤 방향으로 전개할 것인가를 지정하려고 한다.
1. 부모행 방향으로 전개 (Bottom-Up)
SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD='DEPT_001'
CONNECT BY PRIOR PAR_DEPT_CD = DEPT_CD;
[결과 데이터]
2. 자식행 방향으로 전개 (Top-Down)
SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD='DEPT_001'
CONNECT BY PAR_DEPT_CD = PRIOR DEPT_CD;
[결과 데이터]
※ LEVEL
: 계층구조 쿼리해서 수행 결과의 DEPTH를 표현하는 의사컬럼(가짜컬럼)이다.
SELECT LEVEL,
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;
[참고]