계층형 질의

계층형 질의 (Hierarchical Query)

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

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

• START WITH : 계층 구조 전개 시작 위치 지정 (루트데이터 지정)
  • START WITH가 없는 경우, 계층 구조 탐색은 테이블의 모든 노드에서 시작될 수 있음
• CONNECT BY : 다음에 전개될 자식 데이터 지정
  • 자식 데이터는 CONNECT BY 절의 조건을 만족해야 함
• PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 컬럼 지정
  • PRIOR 자식 = 부모
    • 계층 구조에서 부모 → 자식 방향으로 순방향 전개
  • PRIOR 부모 = 자식
    • 계층 구조에서 자식 → 부모 방향으로 역방향 전개
• NOCYCLE : 사이클이 발생하면 런타임 오류가 발생하는데, 이 오류를 방지함
  • 사이클(cycle) = 데이터 전개하며 이미 나타났던 동일한 데이터가 다시 나타나는 경우
• ORDER SIBLINGS BY : 형제 노드(동일 레벨) 사이에서 정렬 수행(형제 노드 순서 지정)
• WHERE : 모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 추출(조건식)
  
  
  

가상 컬럼(Pseudo Column - Oracle)

• 오라클은 계층형 질의를 사용할 때 다음과 같은 가상 컬럼(Pseudo Column)을 제공함
• 가상 컬럼은 SELECT 절에만 등장하고, 계층 구조를 나타내기 위해 사용됨(각 노드의 위치, 레벨, 부모-자식 관계 등)

SELECT  컬럼1 컬럼2, ...
        CONNECT_BY_ROOT(컬럼) AS root_value,
        LEVEL AS node_level,
        CONNECT_BY_ISLEAF AS is_leaf
FROM    테이블
CONNECT BY PRIOR column = parent_column;

가상 컬럼	설명
CONNECT_BY_ROOT	루트 노드의 값을 반환
	사용법 : CONNECT_BY_ROOT 컬럼
LEVEL	현재 노드의 레벨을 반환. 루트 데이터(시작)면 1, 하위 데이터면 2. 리프 데이터까지 1씩 증가함
CONNECT_BY_ISLEAF	현재 노드가 말단(리프) 노드인지를 반환. 리프면 1, 아니면 0
CONNECT_BY_ISCYCLE	현재 레코드가 이전에 처리한 레코드와 연결되어 사이클을 형성하는 경우 1, 아니면 0 (사이클 형성 여부)
SYS_CONNECT_BY_PATH	현재 전개할 데이터의 루트 데이터를 표시(단항 연산자)
	사용법 : SYS_CONNECT_BY_PATH(컬럼, 경로분리자)

• 순방향 전개

SELECT LEVEL,
       LPAD(' ', 4 * (LEVEL - 1)) || EMPNO 사원,
       MGR 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM   EMP
START WITH MGR IS NULL # 최상위 사원
CONNECT BY PRIOR EMPNO = MGR; # 부모 → 자식

✅ CONNECT BY PRIOR EMPNO = MGR
: PRIOR 사원번호 = 관리자 라는 형태이므로 순방향 전개를 펼침
✅ CONNECT_BY_ISLEAF
: 해당 가상 컬럼을 사용해, 해당 데이터가 리프 데이터인지 구별하고 있음

※ LPAD 함수 : 계층형 조회 결과를 명확히 하기 위해 사용 (LEVEL 값을 이용해 결과 데이터 정렬)

• 역방향 전개

SELECT     LEVEL,
           LPAD(' ', 4 * (LEVEL - 1)) || 사원 사원,
           관리자,
           CONNECT_BY_ISLEAF ISLEAF
FROM       사원
START WITH 사원 = 'D'
CONNECT BY PRIOR 관리자 = 사원; # 자식 → 부모

✅ 사원 'D'로 부터 상위 관리자를 찾는 역방향 전개 (하위 데이터 → 상위 데이터)
✅ D는 루트 데이터라 레벨이 1이고, 그의 상위 데이터인 C는 레벨이 2, 그의 상위 데이터인 A는 레벨이 3
✅ 리프 데이터는 A