계층형 질의

계층형 질의

 

• 계층형 데이터: 동일 테이블에 계층적으로 상/하위 데이터가 포함된 데이터
• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생 (조직, 사원, 메뉴 등)
  
  

•  Oracle 계층형 질의

SELECT ...
FROM 테이블
WHERE condition AND condition
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition ...
[ORDER SIBLINGS BY column, column, ...]

- 계층형 질의에서 사용되는 가상 칼럼

가상 칼럼	설명
LEVEL	루트 데이터이면 1, 그 하위이면 2이다. 리프(Leaf) 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프 데이터이면1, 그렇지 않으면 0이다.
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용할 수 있다.

 

• START WITH절: 레벨의 시작
• CONNECT BY절: 그 다음에 자식 레벨 지정 (이때 CONNECT BY절의 조건을 만족해야 됨)
• PRIOR 자식 = 부모: [부모→자식]으로 순방향 전개, 리프 = 1
• PRIOR 부모 = 자식: [자식→부모]으로 역방향 전개, 루트 = 1
• ORDER SIBLINGS BY: 형제 NODE 위치를 바꿈
• NOCYCLE: 이미 나타난 동일한 데이터가 전개 중에 다시 나타나면  이것을 CYCLE 형성이라고 함.
  사이클 발생한 데이터는 런타임 오류 발생 → NOCYCLE 추가 → 사이클 발생 이후 데이터는 전개하지 않는다.

 

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

함수	설명
SYS_CONNECT_BY_PATH	루트 데이터로부터 현재 전개할 데이터까지의 경로를 표시한다. 사용법 : SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다. 사용법 : CONNECT_BY_ROOT 칼럼

 

• SQL Server 계층형 질의
  • CTE(Common Table Expression)로 재귀 호출하여 상위부터 하위 방향 전개

WITH 테이블명_ANCHOR AS
(SELECT 하위칼럼명, 칼럼명, 상위칼럼명, 0 AS LEVEL
FROM 테이블명
WHERE 상위칼럼명 IS NULL /* 재귀 호출의 시작점 */
UNION ALL
SELECT R.칼럼명, R.칼럼명, R.계층칼럼며으 A.LEVEL + 1
FROM 테이블명_ANCHR A, 테이블명 R
WHERE A.하위칼럼 = R.상위칼럼)