오라클 계층형 질의

1. 계층형 테이블이란?

컬럼끼리 계층(LEVEL)을 갖는 테이블을 뜻한다. 예를 들어 부서와 그 부서의 상하위부서, 대분류와 소분류등이 있다.

계층형 테이블의 예시

create table 지부 (지부번호 number (20), 지부명 varchar2(40), 상위지부명 varchar2(40));

insert into 지부 values (10, '아시아지부', NULL);
insert into 지부 values (11, '한국지부', '아시아지부');
insert into 지부 values (12, '일본지부', '아시아지부');
insert into 지부 values (13, '중국지부', '아시아지부');

select LEVEL, 지부번호, 지부명, 상위지부명
from 지부
start with 상위지부명 IS NULL
connect by prior 지부명 = 상위지부명;

위 쿼리에서 상위지부는 아시아 지부이며, 아시아 지부의 하위지부로는 한국, 일본, 중국 지부가 존재한다. LEVEL은 계층형 테이블을 사용했을 때 사용가능하며, 한 행이 속한 계층( 최상위 1->2->3->....)을 출력해준다.

2. 계층형 쿼리 사용법

계층형 쿼리는 계층형 테이블에 사용할 수 있는 구문이다.

select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 상위지부명 IS NULL
connect by prior 지부명 = 상위지부명;

1. Start with 구문

start with은 시작위치를 지정해주는 문장이다. 위 쿼리에서 상위지부명 IS NULL로 표기하였는데, 즉, 상위지부명이 
NULL인 아시아지부부터 시작하여, 정방향/역방향에 따라 하위/상위 레벨에 속한 행을 출력한다.  

2. connect by prior 구문

정방향과 역방향을 결정하는 구문이다. 
정방향이란? 
루트노드(가장 상위) 부터 하위 레벨로 내려오면서 출력하는 구문이다. 
역방향이란?
리프노드(가장 하위) 부터 상위레벨로 올라가면서 출력하는 구문이다. 

정방향 지정 방법

connect by prior 지부명 = 상위지부명; 
or
connect by 상위지부명 = prior 지부명;

역방향 지정 방법

connect by 지부명 = prior 상위지부명; 
or
connect by prior 상위지부명 = 지부명;

3. order siblings by 구문

가끔 최상위 노드에 속한 행이 2개이상인 경우가 존재한다. 이럴 때 정렬을 하지 않으면 여러개의 최상위 노드에서 갈라져 
나온 행들끼리 섞여서 출력하기에 한눈에 알아보기 보기 힘들다. 
이 때 최상위 노드끼리 파티션하여 정렬하는 용도로 사용한다. 

3. 계층형 쿼리 사용 예

create table 지부 (지부번호 number (20), 지부명 varchar2(40), 상위지부명 varchar2(40));

insert into 지부 values (10, '아시아지부', NULL);
insert into 지부 values (11, '한국지부', '아시아지부');
insert into 지부 values (12, '일본지부', '아시아지부');
insert into 지부 values (13, '중국지부', '아시아지부');
insert into 지부 values (20, '북아메리카지부', NULL);
insert into 지부 values (21, '캐나다지부', '북아메리카지부');
insert into 지부 values (22, '맥시코지부', '북아메리카지부');
insert into 지부 values (23, '미국지부', '북아메리카지부');
insert into 지부 values (231, '뉴욕지부', '미국지부');
insert into 지부 values (232, '맨하튼지부', '미국지부');
insert into 지부 values (233, '오하이오지부', '미국지부');

위와 같이 테이블을 생성하였다.

정방향 사용예 1

select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 상위지부명 IS NULL
connect by prior 지부명 = 상위지부명;

해설: start with 상위지부명 IS NULL 
	  connect by prior 지부명 = 상위지부명;
상위 지부명이 NULL로시작하는 북아메리카, 아시아지부 부터 그 하위의 지부들을 전부 출력한다. 

정방향 사용예 2

select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 상위지부명 = '미국지부'
connect by prior 지부명 = 상위지부명;

해설: start with 상위지부명 = '미국지부'
	  connect by prior 지부명 = 상위지부명;
상위 지부명이 '미국지부'인 그 하위의 지부들을 전부 출력한다. 

정방향 사용예 3

select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 상위지부명 IS NULL
connect by prior 지부명 = 상위지부명
and 지부명 = '미국지부'
order siblings by 지부번호; 

해설: start with 상위지부명 IS NULL
	  connect by prior 지부명 = 상위지부명
	  and 지부명 = '미국지부'
  	  order siblings by 지부번호; 
      
상위 지부명이 NULL로 시작하는 지부의 하위 지부들중 지부명이 '미국지부'인 것만 필터링하여 출력한다. 

추가해설 : 왜 위 쿼리에서는 아시아지부까지 출력 되었을까? where절과 달리 start with절로 지정된 루트노드는 일단 
출력하고, connect by절은 그 하위의 노드에 대해서만 필터링이 가능하기 때문이다. 
일단 start with로 지정된 아시아와, 북아메리카를 출력 후 지부명 = '미국지부'를 만족하는 행만 출력되었다.

역방향 사용예

select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 지부명 = '오하이오지부'
connect by  지부명 = prior 상위지부명

해설 : start with 지부명 = '오하이오지부'
	  connect by  지부명 = prior 상위지부명

지부명이 오하이오 지부인것을 최하위 노드로 하여, 역방향으로 그 상위인 지부를 출력한다. 

주의) 이 때  start with으로 지정된 오하이오지부가 레벨 1로 지정되었으며,
최상위인 북아메리카 지부가 레벨 3으로 지정되었다. 리프노드(최하위)인데도 불구하고 말이다.

양방향 사용예
Union연산자를 사용하여 중간노드부터 상, 하위로 검색하는 것도 가능하다.

select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 지부명 = '미국지부'
connect by  지부명 = prior 상위지부명
union
select LEVEL, 지부번호, 지부명, 상위지부명 
from 지부
start with 지부명 = '미국지부'
connect by prior 지부명 =  상위지부명
order by 지부번호;

해설 : 중간단계 노드인 미국지부를 중앙으로 하여, 윗행에는 '미국지부'상위노드인 북아메리카지부가, 
아랫행에는 미국지부의 하위노드인 뉴욕, 맨하튼, 오하이오 지부가 나타나도록 하였다.