[ORACLE] 계층형 쿼리 START WITH ··· CONNECT BY

계층형 쿼리 정의

Oracle만 가지고 있는 기능 중 하나로 동일 테이블에 계층적으로 상위, 하위 데이터가 포함된 데이터를 의미함

※ 본 기능은 Oracle 8i 부터 지원합니다.

 

계층형 구조

 

계층형 쿼리 사용 방법

START WITH 조건1 ··· CONNECT BY 조건2

• START WITH 조건1 : 조건1에 맞는 루트노드를 구한다.
• CONNECT BY 조건2 : 부모와 자식노드간 (1:N) 관계를 작성하며, 반드시 PRIOR 연산자를 포함시켜야한다.
• PRIOR 연산자 : PRIOR 연산자는 계층형 쿼리에서만 사용되는 오라클 SQL 연산자이며, '한식'의 PARENT_FOOD_ID 컬럼의 값을 '음식'의 FOOD_ID 컬럼이 가지고 있으므로 PRIOR 연산자를 FOOD_ID 컬럼 앞에 붙이면 된다.

 

※ 아래 그래프는 계층형 쿼리에서 사용하는 기능이므로 참고해주세요.

연산자	설명
LEVEL	계층형 정보를 표현할 때 레벨을 나타냄
CONNECT BY ISLEAF	해당 로우가 리프노드인지 여부 체크 리프노드인 경우 : 1을 반환 리프노드가 아닌 경우 : 0을 반환
SYS CONNECT BY PATH (COLUMN, CHAR)	루트노드로부터 해당 로우 항목까지 경로 반환
CONNECT BY ISCYCLE	무한 루프 컬럼을 찾음
CONNECT BY ROOT	최상위 로우를 반환
ORDER SIBILINGS BY	계층형 구조 정렬

 

계층형 쿼리 실행

1. 테스트를 위한 임시 테이블 'FOOD' 생성

CREATE TABLE FOOD (
	FOOD_ID INTEGER NOT NULL, -- 음식 ID
    PARENT_FOOD_ID INTEGER,   -- 상위 음식 ID
    FOOD_NAME VARCHAR2(20) NOT NULL, -- 음식 이름
    PRIMARY KEY (FOOD_ID)
);

INSERT INTO FOOD VALUES(100, NULL, '음식');
INSERT INTO FOOD VALUES(101, 100,  '한식');
INSERT INTO FOOD VALUES(102, 100,  '일식');
INSERT INTO FOOD VALUES(103, 100,  '중식');
INSERT INTO FOOD VALUES(104, 101,  '불고기');
INSERT INTO FOOD VALUES(105, 101,  '떡국');
INSERT INTO FOOD VALUES(106, 102,  '초밥');
INSERT INTO FOOD VALUES(107, 103,  '짜장면');
INSERT INTO FOOD VALUES(108, 103,  '탕수육');
INSERT INTO FOOD VALUES(109, 103,  '짬뽕');

2. 계층형 쿼리 형태로 조회 (보기 편하라고 들여쓰기 조회를 사용함)

SELECT LEVEL
     , LPAD(' ', 2*LEVEL-1) || FOOD_NAME
     , FOOD_ID
FROM FOOD
START WITH PARENT_FOOD_ID IS NULL
CONNECT BY PRIOR FOOD_ID = PARENT_FOOD_ID;