계층적 질의

계층적 질의

1. 정의

동일 테이블에 상위와 하위의 포함 관계를 가진 계층형 데이터를 계층형 구조로 조회하기 위한 질의

2. 계층적 질의 문법

SELECT 컬럼명

FROM 테이블명

WHERE 조건

START WITH 조건

CONNECT BY [NOCYCLE] 조건

[ORDER SIBLINGS BY 컬럼명];

START WITH : 계층 구조의 시작 위치를 지정(루트 데이터)

CONNECT BY : 부모와 자식 데이터의 관계를 정의

PRIOR : SELECT, WHERE, CONNECT BY 절에서 사용하며, 현재 조회한 컬럼을 지정, 일반적으로 순방향, 역방향 정할 때 사용함

NOCYCLE : 사이클이 발생한 이후의 데이터는 조회하지 않음

ORDER SIBLINGS BY : 동일 레벨(형제 노드)에서 정렬을 수행

3. 계층적 질의 가상 컬럼

LEVEL : 루트데이터는 1, 하위 데이터는 리프 데이터까지 1씩 증가

CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면 1, 리프 데이터가 아니면 0

CONNECT_BY_ISCYCLE : CYCLE이 존재하면 1, 그렇지 않으면 0

4. 계층형 질의 함수

SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 데이터까지의 경로를 표시

CONNECT_BY_ROOT : 현재 데이터의 루트 데이터를 표시

5. 실습

1) SCOTT계정의 EMP 테이블 검색

SELECT * FROM EMP;

2) 순방향 계층형 질의 예제

SELECT EMPNO, ENAME, MGR, LEVEL, CONNECT_BY_ISLEAF AS ISLEAF, SYS_CONNECT_BY_PATH(ENAME, '/') AS EMP_PATH, CONNECT_BY_ROOT(ENAME) AS ROOT FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;

3) 역방향 계층형 질의 예제

SELECT EMPNO, ENAME, MGR, LEVEL, CONNECT_BY_ISLEAF AS ISLEAF, SYS_CONNECT_BY_PATH(ENAME, '/') AS EMP_PATH, CONNECT_BY_ROOT(ENAME) AS ROOT FROM EMP START WITH EMPNO = 7876 CONNECT BY PRIOR MGR = EMPNO;

- START WITH에서 지정해주는 루트의 값이 특정한 값으로 변경됨.

- PRIOR이 상위개념(부모) 앞에 붙음.

- 실행결과를 보면 루트 레벨이 자식부터 시작함.

- PATH 경로도 자식부터 부모순으로 진행됨.

4) 계층적 질의에서 WHERE절의 사용

- 계층형 질의가 수행되어 계층형 구조가 형성된 후 WHERE절에 의해 필터링 됨.

- 즉, FROM절 → START WITH. CONNECT BY 등 계층형 질의 → WHERE절

SELECT EMPNO, ENAME, MGR, LEVEL, CONNECT_BY_ISLEAF AS ISLEAF, SYS_CONNECT_BY_PATH(ENAME, '/') AS EMP_PATH, CONNECT_BY_ROOT(ENAME) AS ROOT FROM EMP WHERE EMPNO = 7876 START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;

- WHERE절을 제외하고 실행하면 아래와 같음.

- 계층형 쿼리가 먼저 실행하여 구조를 생성한 후 WHERE절 조건을 확인함.

- WHERE EMPNO = 7876 이므로, 위의 결과에서 EMPNO가 7876 하나만 필터링되어 출력됨.

from http://classjune.tistory.com/125 by ccl(A) rewrite - 2021-11-17 16:00:41