[Oracle] 계층적 쿼리

계층적 질의문

• 관계형 데이터 베이스에서 데이터간의 부모 자식 관계를 표현할 수 있는 칼럼을 계층적인 관계로 표현
• SELECT 명령문에서 START WITH와 CONNECT BY 절을 이용
• 출력 형식은 top-down 또는 bottom-up

SELECT [LEVEL], column, expression ...
FROM table_name
WHERE conditions
START WITH conditions
CONNECT BY PRIOR conditions;

• LEVEL : 계층적 질의문에서 검색된 결과에 대해 계층별로 레벨 번호 표시, 루트 노드는 1, 하위 노드는 1씩 증가
• START WITH : 계층적인 출력 형식을 표현하기 위한 최상위 행을 설정
• CONNECT BY PRIOR : 계층 관계의 데이터를 지정하는 칼럼

 

CONNECT BY PRIOR에서 top-down과 bottom-up 설정 형태

top-dwon 출력 형식 : 루트 노드부터 출력
CONNECT BY PRIOR column1 = column2
column1 = 자식 키, column2 = 부모키
EX) CONNECT BY PRIOR deptno = college;

bottom=up 출력 형식 : 단말 노드부터 먼저 출력
CONNECT BY PRIOR column1 = column2
column1 = 부모 키, column2 = 자식 키
EX) CONNECT BY PRIOR college = deptno

 

 

계층적 구조 이해

예제들을 보기전에 아래의 테이블 구조를 이해한다.

이미지 출처 : 오라클 중심의 SQL 배움터

deptno는 부서 번호이며 college는 그 부서의 상위 부서 번호를 나타낸다.

이미지 출처 : 오라클 중심의 SQL 배움터

 

top-down 방식

-- 계층적 질의문을 사용하여 학과, 학부, 단과대학을 검색하여 단대, 학부, 학과순으로
-- top-down 형식의 계층 구조로 출력. ( 시작 데이터는 10번 부서. )
SELECT deptno, dname, college
FROM department
START WITH deptno=10
CONNECT BY PRIOR deptno=college;

 

bottom-up 방식

-- 계층적 질의문을 사용하여 학과, 학부, 단과대학을 검색하여
-- 학과, 학부, 단대 순으로 bottom-up 형식의 계층 구조로 출력 (시작 데이터는 102번 부서)
SELECT deptno, dname, college
FROM department
START WITH deptno=102
CONNECT BY PRIOR college = deptno;

 

레벨별 구분

-- 단대, 학부, 학과순의 top-down 형식으로 출력
-- 시작 데이터는 '공과대학'이며 각 레벨별로 우측으로 2칸 이동하여 출력
SELECT LPAD('  ', (LEVEL-1)*2) || dname 조직도
FROM department
START WITH dname = '공과대학'
CONNECT BY PRIOR deptno = college;

 

계층 구조에 가지 제거

• WHERE 절이나 CONNECT BY 절을 이용하여 계층 구조의 일부를 제거하고 출력할 수 있다.
• WHERE 절을 이용해 임의의 가지를 삭제한다.
• CONNECT BY 절을 이용해 임의의 가지와 그 자식 노드까지 제한한다.

이미지 출처 : 오라클 중심의 SQL 배움터

 

 

WHERE 절과 CONNECT BY절의 가지 치기 차이

WHERE 절을 이용한 출력 제한

위의 계층도에서 정보 미디어 학부만을 제외하여 출력한다. ( 자식 노드들은 출력 함 )

-- 단대, 학부, 학과순으로 top-down 형식의 계층 구조로 출력
-- 단, 정보미디어학부를 제외하고 출력.
SELECT deptno, college, dname, loc
FROM department
WHERE dname != '정보미디어학부'
START WITH college IS NULL
CONNECT BY PRIOR deptno = college;

 

CONNECT BY 절을 이용한 출력 제한

위의 계층도에서 정보 미디어학부의 자식 노드까지 출력하지 않는다.

-- 단대, 학부, 학과순으로 top-down 형식의 계층구조로 출력
-- 단, 정보미디어학부와 정보미디어학부에 속한 모든 학과를 제외하여 출력
SELECT deptno, college, dname, loc
FROM department
START WITH college IS NULL
CONNECT BY PRIOR deptno = college
AND dname != '정보미디어학부';

정보미디어학부와 그에 속한 컴퓨터공학과, 멀티미디어학과가 제외되었다.