[SQL/ORACLE] 계층형 쿼리

DANI·2023년 11월 4일

ORACLE_SQL

목록 보기

6/10

📕 계층형 쿼리란?

계층형 쿼리(Hierarchical Query)는 오라클에서만 지원하고 있는 아주 막강한 기능 중의 하나다.

계층형 쿼리는 2차원 형태의 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말한다.
계층형 구조란 상하 수직 관계의 구조로 사원-대리-과장-부장과 같은 직급, 판매부-구매부 같은 부서 구조, 정부 부처등이 계층형 구조에 속한다. 또한 엑셀과 같은 프로그램의 메뉴 구조, 답변형 게시판뿐만 아니라 책의 목차도 이에 포함된다.

💻 계층형 쿼리 구문

SELECT 컬럼1, 컬럼2...
FROM 테이블
WHERE 조건
START WITH 최상위 조건
CONNECT BY [NOCYCLE] [PRIOR 계층형 구조 조건]

START WITH 조건 : 계층형 구조에서 최상위 계층의 ROW 를 식별하는 조건을 명시한다.

START WITH MANAGER_ID IS NULL; // MANAGER_ID 가 NULL인 ROW가 최상위
START WITH JOB_CODE = 'J1'; // JOB_CODE 가 'J1'인 ROW가 최상위

CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건] : 계층형 구조가 어떤 식으로 연결되는 지를 기술하는 부분이다.
- NOCYCLE : 데이터를 펼치면서 이미 나타났던 데이터가 다시 나타나는 경우 CYCLE이 형성되
  었다라고 한다. 이때 오류가 발생하는데 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터를 출력하지 않는다.

CONNECT BY PRIOR 부모노드 = 자식노드  
CONNECT BY 자식노드 = PRIOR 부모노드

🔴 계층형 쿼리 조회 예시 1

// LEVEL : 계층형 쿼리에서만 사용할 수 있는 의사 컬럼으로 계층형 구조에 따른 레벨 값을 자동으로 반환
// LPAD : LEVEL 값에 따라 왼쪽에 공백을 붙인다.
SELECT LEVEL, LPAD(' ', 3*(LEVEL-1)) || D.DEPT_TITLE AS DEPT_TITLE,
    J.JOB_NAME,
    E.EMP_NAME, 
    E.EMP_ID,
    E.MANAGER_ID
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
START WITH E.MANAGER_ID IS NULL -- 널값부터 시작
CONNECT BY PRIOR E.EMP_ID = E.MANAGER_ID -- E.MANAGER_ID가 자식노드 E.EMP_ID가 부모노드
ORDER SIBLINGS BY J.JOB_CODE; -- 계층형 구조를 보존한 상태로 정렬

🔵 결과

E.MANAGER_ID가 부모노드 E.EMP_ID가 자식노드인 상태로 계층이 형성된다.
즉, E.MANAGER_ID IS NULL인 대표가 최상위 노드가 되는 것이고, 이 때 대표의 EMP_ID는 200이다.
CONNECT BY PRIOR E.EMP_ID = E.MANAGER_ID에 의해 E.MANAGER_ID의 부모는 E.EMP_ID가 된다.

🔴 계층형 쿼리 조회 예시 2

계층형 구조를 보존한 상태로 조건 추가하기

SELECT LEVEL, LPAD(' ', 3*(LEVEL-1)) || D.DEPT_TITLE AS DEPT_TITLE,
    J.JOB_NAME,
    E.EMP_NAME, 
    E.EMP_ID,
    E.MANAGER_ID
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMP_ID = E.MANAGER_ID
AND J.JOB_CODE < 'J5' -- 계층형 구조를 보존한 상태로 조건 추가
ORDER SIBLINGS BY J.JOB_CODE;

🔵 결과

🔴 계층형 쿼리 조회 예시 3

CONNECT_BY_ROOT : 최상위 로우를 반환

SELECT CONNECT_BY_ROOT D.DEPT_TITLE AS ROOT_DEPT , LEVEL, LPAD(' ', 3*(LEVEL-1)) || D.DEPT_TITLE AS DEPT_TITLE,
    J.JOB_NAME,
    E.EMP_NAME, 
    E.EMP_ID,
    E.MANAGER_ID
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMP_ID = E.MANAGER_ID
ORDER SIBLINGS BY J.JOB_CODE;

🔵 결과

🔴 계층형 쿼리 조회 예시 4

CONNECT_BY_ISLEAF : 최하위 로우(리프노드)면 1을 반환, 그렇지 않으면 0을 반환

SELECT CONNECT_BY_ISLEAF, LEVEL, LPAD(' ', 3*(LEVEL-1)) || D.DEPT_TITLE AS DEPT_TITLE,
    J.JOB_NAME,
    E.EMP_NAME, 
    E.EMP_ID,
    E.MANAGER_ID
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMP_ID = E.MANAGER_ID
ORDER SIBLINGS BY J.JOB_CODE;

🔵 결과

🔴 계층형 쿼리 조회 예시 5

SYS_CONNECT_BY_PATH(colm, char) : 루트 노드부터 자신의 행까지 연결된 경로 정보를 반환

// SYS_CONNECT_BY_PATH(컬럼명, 구분기호) 
SELECT SYS_CONNECT_BY_PATH(E.MANAGER_ID, '|') AS PATH, LEVEL, LPAD(' ', 3*(LEVEL-1)) || D.DEPT_TITLE AS DEPT_TITLE,
    J.JOB_NAME,
    E.EMP_NAME, 
    E.EMP_ID,
    E.MANAGER_ID
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMP_ID = E.MANAGER_ID
ORDER SIBLINGS BY J.JOB_CODE;

🔵 결과

💡 CONNECT_BY_ISCYCLE

계층형 쿼리는 루프 알고리즘을 사용하기 때문에 조건을 잘못 줄 경우 무한루프를 타게 된다.
루프가 발생된 원인을 찾아 수정해야되는데 이 때, NOCYCLE을 추가하고 SELECT절에 CONNECT_BY_ISCYCLE 의사 컬럼을 사용하면 된다. CONNECT_BY_ISCYCLE 는 현재 로우가 자식을 갖고 있으면서 동시에 부모 로우이면 1을, 그렇지 않으면 0을 반환한다.

💻 예시 테이블 생성

CREATE TABLE EX_TABLE AS
SELECT ROWNUM seq,
'2023' || LPAD(CEIL(ROWNUM/1000), 2, '0') MONTH, 
// "||" 는 CONCAT
//CEIL 함수는 소수점 첫째 자리에서 올림하는 함수로, 주어진 숫자와 가장 근접한 큰 정수를 출력
//LPAD("값", "총 문자길이", "채움문자")
ROUND(DBMS_RANDOM.VALUE(100,1000)) AMT -- 난수를 생성하는 패키지
FROM DUAL
CONNECT BY LEVEL <= 12000;
// 명시한 숫자만큼의 로우를 반환함
// 내부적으로는 등비수열의 합만큼 로우를 생성한다.