[PostgreSQL] - 포스트그레 계층형 쿼리(WITH RECURSIVE ~ 재귀 쿼리) 사용법 및 예제(Oracle 계층형 쿼리 대체하는 방법)

#포스트그레 계층형 쿼리(WITH RECURSIVE ~ 재귀 구문) 사용법 및 예제(Oracle 계층형 쿼리 대체하는 방법)

 

 

#PostgreSQL 계층형 쿼리 실습

 

1. 실습 테이블을 생성합니다.

CREATE TABLE DEPT (
     DEP_CD NUMERIC NOT NULL, -- 부서코드
     PARENT_CD NUMERIC, -- 상위부서 코드
     DEPT_NAME VARCHAR(100) NOT NULL, -- 부서이름
     PRIMARY KEY (DEP_CD)
);

 

 

2. 실습 데이터를 생성합니다.

INSERT INTO DEPT VALUES ( 101, NULL, '회사');
INSERT INTO DEPT VALUES ( 102, 101, '개발팀');
INSERT INTO DEPT VALUES ( 103, 101, '영업팀');
INSERT INTO DEPT VALUES ( 104, 101, '마케팅팀');
INSERT INTO DEPT VALUES ( 105, 101, '솔루션팀');

INSERT INTO DEPT VALUES ( 106, 102, '개발1팀');
INSERT INTO DEPT VALUES ( 107, 102, '개발2팀');
INSERT INTO DEPT VALUES ( 108, 102, '개발3팀');

INSERT INTO DEPT VALUES ( 109, 103, '영업1팀');

INSERT INTO DEPT VALUES ( 110, 104, '마케팅1팀');
INSERT INTO DEPT VALUES ( 111, 104, '마케팅2팀');

INSERT INTO DEPT VALUES ( 112, 105, '솔루션1팀');
INSERT INTO DEPT VALUES ( 113, 105, '솔루션2팀');
INSERT INTO DEPT VALUES ( 114, 105, '솔루션3팀');

 

 

3. PostgreSQL = ORACLE 계층형 쿼리 문법적 차이입니다.

-CONNECT_BY_ROOT = 오라클 CONNECT_BY_ROOT 함수와 같습니다.

-LEVEL = 오라클 LEVEL 함수와 같습니다.

-UNION ALL 상위 SELECT문 = 오라클 START WITH 구문과 같습니다.

-UNION ALL 하위 SELECT문 = 오라클 CONNECT BY PRIOR 구문과 같습니다.

-CYCLE, NO CYCLE = 오라클 CYCLE, NO CYCLE 구문과 같습니다.

-PATH = 계층형으로 정렬합니다.

 

 

#PostgreSQL WITH RECURSIVE 계층형쿼리 예시입니다.

WITH RECURSIVE TEMP_DEPT(DEPT_NAME, CONNECT_BY_ROOT, DEPT_CD, PARENT_CD, LEVEL, PATH, CYCLE) AS (
	SELECT
		DEPT_NAME
		,DEPT_CD AS CONNECT_BY_ROOT
		,DEPT_CD
		,PARENT_CD
		,1 AS level
		,ARRAY[DEPT_CD]
		,FALSE
	FROM 
		DEPT
	WHERE 
		PARENT_CD IS NULL -- Oracle - Start With구문
UNION ALL
	SELECT
		D.DEPT_NAME
		,CONNECT_BY_ROOT
		,D.DEPT_CD
		,D.PARENT_CD
		,LEVEL + 1
		,PATH||D.DEPT_CD
		,D.DEPT_CD = ANY(PATH)
	FROM 
		DEPT D
	INNER JOIN TEMP_DEPT P
	ON P.DEPT_CD = D.PARENT_CD -- Oracle - Connect By Prior구문 (앞뒤 위치 상관 없음)
    AND NOT CYCLE -- Oracle - NO CYCLE
)
SELECT 
	LPAD(' ', 2*(LEVEL-1)) || DEPT_NAME AS DEPT_NAME
	,CONNECT_BY_ROOT
	,DEPT_CD
	,PARENT_CD
	,LEVEL
	,PATH
FROM TEMP_DEPT ORDER BY PATH;

 

 

4. 결과

-최상위 노드를 기준으로 상위행의 DEPT_CD가 PARENT_CD와 매칭된다고 이해하면 쉽습니다.