[2과목] 2-14. 계층형 질의

■ 계층형 질의

• 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층(depth)를 표현하는 기법
• ex) dept2에서의 부서별 상하관계
• PRIOR의 위치에 따라 연결하는 데이터가 달라짐

select 
from 테이블명
start with 시작조건         -- 시작점을 지정하는 조건 전달
connect by prior 연결조건 ; -- 시작점 기준으로 하위 계급을 찾아가는 조건

 

• START WITH : 데이터를 출력할 시작 지정하는 조건
• CONNECT BY PRIOR : 행을 이어나갈 조건

 

ex) dept2 테이블에 대해 각 부서의 레벨을 출력 (최상위 부서가 1 레벨)

select D.*, level
from dept2 D
start with pdept is null
connect by prior dcode = pdept;

→ 사장실의 dcode를 넘겨 다시 각 행들의 pdept와 비교해야 하므로 먼저 정해져야 하는 값의 방향에 PRIOR 전달 ! 

 

 

ex) 연결조건에 추가 조건이 붙는 경우

select D.*
from dept2 D
start with pdept is null
connect by prior dcode = pdept and area = '서울지사';

 

※ 주의) CONNECT 에서의 추가 조건은 메인 쿼리에서의 대상을 선택하기 위함이 아닌, 연결 조건의 일부로 해석해야 함

 

 

ex) dept2테이블에서 부서의 상-하 관계를 들여쓰기 반영 포함

select D.*, level, lpad(' ', (level-1)*4, ' ')||dname as 부서명
from dept2 D
start with pdept is null
connect by prior dcode = pdept;

 

 

ex) dept2 테이블에서 영업 1팀 기준 상위 부서들을 표현

select D.*, level, lpad(' ', (level-1)*4, ' ')||dname as 부서명
from dept2 D
start with dname='영업1팀' 
connect by decode = prior pdept;

→ 영업 1팀 기준 상위 부서 연결은 영업 1팀의 pdept를 전달해야 함.  따라서 pdept쪽에 prior이 붙음

 

 

※ 계층형 질의 가상 컬럼

• LEVEL : 각 depth를 표현(시작점부터 1)
• CONNECT_BY_ISLEAF : leaf node(최하위 노드) 여부(참:1, 거짓:0)

※  계층형 질의 가상 함수

• CONNECT_BY_ROOT 컬럼명 : 루트노드의 해당 컬럼명이 출력
• SYS_CONNECT_BY_PATH(컬럼, 구분자) : 이어지는 경로 출력
• ORDER SIBLINGS BY 컬럼 : 같은 LEVEL일 경우 정렬 수행

 

ex) 계층형 가상컬럼, 함수 결과

select D.*, level, lpad(' ',(level-1)*4, ' ')||danme as 학과명, connect_by_root dname, sys_connect_by_path(dname, '-')
from department D
connect by prior deptno=pdept
order siblings by dname;