[SQLD] 2-12. 계층형 질의

계층형 질의

 

구문

SELECT ~
FROM 테이블명
[WHERE 조건]
START WITH (최상위 조건) (IS NULL)
CONNECT BY [NOCYCLE] (PRIOR 계층형 구조 조건)
[ORDER SIBLINGS BY 컬럼1, 컬럼2,...]

-----실행 순서-----
START WITH -> CONNECT BY -> WHERE

≫ START WITH

• 계층 구조 전개의 시작위치 지정 -> 루트 데이터 지정 (Excess)
• 최상위 노드의 부모 노드는 NULL값 이므로 IS NULL 조건 추가
• 서브 쿼리 사용 O
• 지정된 컬럼은 WHERE조건과 상관없이 무조건 리턴

≫ CONNECT BY [NOCYCLE] [PRIOR ~]

• 다음에 전개될 자식 데이터 지정
• 서브 쿼리 사용 X
• NOCYCLE : 동일한 데이터가 다시 나타나는 사이클이 발생하면 발생 이후 데이터를 전개하지 않는다 (무한 루프 방지)
• PRIOR 자식 = 부모 : 순방향 전개
• PRIOR 부모 = 자식 : 역방향 전개
• 부모 행 선별 기준 = START WITH 구문에 지정된 컬럼

PRIOR 없는 곳 → PRIOR 있는 곳으로 전개

≫ ORDER SIBLINGS BY

• 형제 노드(동일 LEVEL) 사이에서 정렬 수행

 

가상 컬럼 (Pseudo Column)

- SELECT절에서 사용

가상 컬럼	설명
LEVEL	- 루트 노드(최상위 노드) = 1 - 노드가 낮아질수록 1씩 증가
CONNECT_BY_ISLEAF	- 자식노드가 있는지 여부 체크 - 자식노드 있으면 0, 없으면 1
CONNECT_BY_ROOT	- 계층형 쿼리에서 최상위 노드 찾아서 리턴
CONNECT_BY_ISCYCLE	- 전개 과정에서 자식을 가지고, 그 해당 데이터가 조상이면 1, 조상이 아니면0 리턴 - CYCLE 옵션 사용했을 때만 사용 가능

≫ LEVEL 별로 들여 쓰기

- LPAD(' ', 4*(LEVEL-1)) || (컬럼명)

  -> 4칸의 공백으로 들여 쓰기

 

계층형 질의 함수

함수	설명
SYS_CONNECT_BY_PATH	- 루트 데이터부터 현재 전개할 데이터까지의 경로 표시 - SYS_CONNECT_BY_PATH(컬럼, 경로분리자)
CONNECT_BY_ROOT	- 현재 전개할 데이터의 루트 데이터 표시 - 단항 연산자 - CONNECT_BY_ROOT 컬럼명

 

예시 테이블

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 ( 110, 106, '구축2팀');
INSERT INTO DEP VALUES ( 111, 104, 'ERP시스템');

SELECT LEVEL, LPAD(' ',4*(LEVEL-1)) || DEP_CD "부서 번호", PARENT_CD "부모 노드", 
SYS_CONNECT_BY_PATH(DEP_CD, '->') 경로, CONNECT_BY_ROOT(DEP_CD) "최상위 노드",
DEPT_NAME "부서 이름", CONNECT_BY_ISLEAF "자식 노드 존재 여부(있으면 0 없으면 1)"
FROM DEP
START WITH PARENT_CD IS NULL
CONNECT BY NOCYCLE PRIOR DEP_CD = PARENT_CD; -- PRIOR 자식 = 부모 (부모->자식 순방향 전개)