(SQL) 계층형 조회

1. 계층형 조회

계층형 데이터란 동일테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

 

SELECT ...
FROM 테이블
WHERE 조건 AND 조건 ..
START WITH 조건
CONNECT BY [NOCYCLE] 조건 AND 조건 ..
[ORDER SIBLINGS BY 컬럼명, 컬럼명...]

 

- START WITH : 계층구조 전개의 시작위치를 지정하는 구문. 루트 데이터를 지정한다.

- CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문. 자식데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다.

- PRIOR : CONNECT BY 절에 사용되며 현재 읽은 컬럼을 지정(이전 ROW값 -> 상수화 대상)

PRIOR 자식 = 부모 : 부모 -> 자식 방향으로 전개하는 순방향 전개

- NOCYCLE : CYCLE은 이미 나타난 데이터가 전개 중에 다시 나타난 경우를 의미한다. 사이클이 발생한 데이터는 오류가 발생하는데, NOCYCLE을 추가하면 사이클이 발생한 데이터 이후는 전개하지 않는다.

- ORDER BY SIBLINGS BY : 형제 노드(동일 LEVEL)사이에서 정렬을 수행한다.

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

 

2. 계층형 질의에서 사용되는 가상컬럼

LEVEL	루트데이터면 1, 그 하위 데이터면 2 리프 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프데이터면 1, 아니면 0
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 가지는데, 해당 데이터가 조상으로서 존재하면 1, 아니면 0

- 리프 데이터 : 자식이 없는 것

- 조상 : 자신으로부터 루트까지의 경로에 존재하는 데이터. CYCLE옵션을 사용했을 때만 사용 가능

EX) 계층형 조회에서 최대 계층의 수를 구하는 문제 MAX(LEVEL)의 값이 4이므로 트리의 최대 깊이는 4이다.   EX) 계층형조회   EMPNO 7698과 7389의 관리자는 8000이다.

3. 계층형 질의에서 사용되는 함수

1) LPAD(값, 총 문자길이, 채움문자)

- 값을 총 문자길이로 만듦. 채움문자를 생략하면 SPACE가 들어감

- 계층형 조회 결과를 명확히 볼 수 있음

EX)

2) SYS_CONNECT_BY_PATH(컬럼, 경로분리자)

- 루트 데이터부터 현재 전개할 데이터까지의 경로 표시

 

3) CONNECT_BY_ROOT 컬럼

- 현재 전개할 데이터의 루트 데이터를 표시

EX)