계층형 쿼리

부모와 자식와 같은 구조를 계층형 구조라 부르는데 ORACLE에서 이러한 계층형 데이터를 분석하기 위한 기능을 제공한다. ⇒ 계층형 질의

동일 테이블에 상위, 하위 데이터가 존재하고 엔터티가 순환관계로 설계하는 경우 발생한다.

 

# 계층형 질의는 SQL 서버에서는 지원하지 않는다.

 

SELECT ~

FROM <테이블명>

START WITH <상위 컬럼명> IS NULL

CONNECT BY PRIOR <하위 컬럼명> = <상위 컬럼명>;

 

1 한 테이블에 컬럼간 상하위 관계가 있는 경우 상위 컬럼이 NULL인 행을 먼저 출력을 한다.

2 그 이후 CONNECT BY 절에 의해 이전 행에서 하위 컬럼값과 같은 값을 상위 컬럼에서 찾아 행을 출력한다.

3 2과정 반복 

 

루트 노드

계층 전개가 시작되는 곳을 루트 노드라고 한다. (LEVEL = 1)

일반적으로 START WITH 절에 의해서 지정된다.

 

리프 노드

계층 전개가 끝나는 곳을 리프 노드라고 한다. 

⇒ 자식 노드가 없는 노드

 

형제 노드

같은 계층에 해당하는 값들을 의미한다.

 

순방향 전개

루트 노드에서 리프 노드로 전개

 

역뱡향 전개

리프 노드에서 루트 노드로 전개

 

순방향/역방향 전개

CONNECT BY PRIOR 자식컬럼 = 부모컬럼

⇒ 순방향 전개 

 

CONNECT BY 자식컬럼 = PRIOR 부모컬럼

⇒ 역방향 전개 

 

CONNECT BY PRIOR 부모컬럼 = 자식컬럼

⇒ 역방향 전개

 

CONNECT BY 부모컬럼 = PRIOR 자식컬럼

⇒ 순방향 전개

 

# 부모 데이터에서 자식 데이터로 전개하는 것을 순방향 전개라고 한다.

 

SELECT <컬럼>

FROM <테이블명>

WHERE <조건>

START WITH <부모데이터 정보>

CONNECT BY PRIOR <부모와 자식의 계층 조건>;

 

START WITH

계층 구조 전개의 시작위치(루트 데이터, 부모 데이터의 정보)

 

CONNECT BY

다음에 전개될 자식데이터를 지정한다.

계층형 전개의 조건식으로 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.

 

PRIOR

CONNECT BY절에 사용되며 이전 레벨 데이터를 지칭할 때 사용한다.

 

WHERE

모든 전개 수행 후에 지정된 조건을 만족하는 데이터만 추출한다.

⇒ 계층 데이터 형성 후 가장 마지막에 적용되는 조건이다.

 

가상 컬럼

LEVEL : 루트 데이터면 1, 하위로 갈수록 1씩 증가한다.

CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1, 그렇지 않으면 0 반환

SYS_CONNECT_BY_PATH(<컬럼명>, '<경로분리자>') : 루트 데이터로부터 현재 전개할 데이터까지의 경로를 표시한다.

CONNECT_BY_ROOT(<컬럼명>) : 현재 전개할 데이터의 루트 데이터를 표시한다.

# CONNECT_BY_ROOT에서 최상위 관리자 본인의 이름도 등장한다.