Oracle Recursive WITH Clause

Oracle WITH 문 (Recursive Query) recursively Explained 🌳

Oracle SQL에서 WITH 절은 복잡한 쿼리를 단순화하고 가독성을 높이기 위해 사용되는 강력한 기능입니다. 특히, WITH 절을 재귀적으로 사용하면 계층적인 데이터를 쉽게 조회하고 처리할 수 있습니다. 예를 들어, 조직도, 부품 관계, 메뉴 구조 등을 다룰 때 매우 유용합니다.

---

재귀 WITH 절의 기본 구조

재귀적인 WITH 절은 두 부분으로 구성됩니다:

• 앵커 멤버 (Anchor Member): 재귀의 시작점이 되는 초기 데이터 집합을 정의하는 부분입니다.
• 재귀 멤버 (Recursive Member): 앵커 멤버의 결과를 기반으로 반복적으로 수행되며, 이전 결과와 결합(JOIN)하여 다음 계층의 데이터를 찾는 부분입니다.

이 두 멤버는 UNION ALL 연산자로 연결됩니다.

WITH temp_table_name (column1, column2, ...) AS (
-- 1. Anchor Member: 재귀의 시작점
SELECT column1, column2, ...
FROM   your_table
WHERE  initial_condition
UNION ALL

-- 2. Recursive Member: 반복 수행 부분
SELECT t.column1, t.column2, ...
FROM   your_table t
JOIN   temp_table_name r ON recursive_join_condition

)
SELECT * FROM temp_table_name;

중요: 재귀 멤버는 반드시 WHERE 절에 재귀를 멈추게 하는 종료 조건을 포함해야 무한 루프에 빠지지 않습니다.

---

사용 예제: 조직도 조회

가장 일반적인 예는 회사의 조직도에서 특정 매니저에 속한 모든 부하 직원을 찾는 것입니다.

1. 예제 테이블 생성

먼저, 직원 정보를 담는 간단한 employees 테이블을 만들어 보겠습니다.

CREATE TABLE employees (
emp_id    NUMBER PRIMARY KEY,
emp_name  VARCHAR2(50),
manager_id NUMBER
);
INSERT INTO employees VALUES (1, '사장', NULL);
INSERT INTO employees VALUES (2, '김부장', 1);
INSERT INTO employees VALUES (3, '이과장', 2);
INSERT INTO employees VALUES (4, '박대리', 3);
INSERT INTO employees VALUES (5, '최사원', 4);
INSERT INTO employees VALUES (6, '정차장', 2);
INSERT INTO employees VALUES (7, '홍사원', 6);

생성된 테이블은 다음과 같습니다:

emp_id	emp_name	manager_id
1	사장	NULL
2	김부장	1
3	이과장	2
4	박대리	3
5	최사원	4
6	정차장	2
7	홍사원	6

2. 재귀 쿼리 작성

'김부장'(emp_id = 2)을 기준으로 모든 하위 직원을 조회하는 재귀 쿼리는 다음과 같습니다. 여기서는 계층의 깊이를 나타내는 level도 함께 조회해 보겠습니다.

WITH emp_hierarchy (emp_id, emp_name, manager_id, lvl) AS (
-- Anchor Member: 시작점 ('김부장')
SELECT
emp_id,
emp_name,
manager_id,
1 AS lvl
FROM
employees
WHERE
emp_id = 2
UNION ALL

-- Recursive Member: 하위 직원 찾기
SELECT
    e.emp_id,
    e.emp_name,
    e.manager_id,
    h.lvl + 1
FROM
    employees e
JOIN
    emp_hierarchy h ON e.manager_id = h.emp_id

)
SELECT
LPAD(' ', (lvl-1) * 4) || emp_name AS employee_name,
emp_id,
manager_id,
lvl
FROM
emp_hierarchy;

3. 쿼리 실행 결과

위 쿼리를 실행하면 다음과 같이 계층 구조로 정렬된 결과를 얻을 수 있습니다.

EMPLOYEE_NAME	EMP_ID	MANAGER_ID	LVL
김부장	2	1	1
이과장	3	2	2
정차장	6	2	2
박대리	4	3	3
홍사원	7	6	3
최사원	5	4	4

---

동작 원리 ⚙️

1. 앵커 멤버 실행: 먼저 앵커 멤버 쿼리가 실행되어 emp_id가 2인 '김부장'의 정보를 찾고, 이 결과가 임시 테이블 emp_hierarchy에 저장됩니다. (lvl = 1)
2. 재귀 멤버 실행 (1차): 재귀 멤버는 emp_hierarchy에 있는 결과(김부장)를 참조합니다. e.manager_id = h.emp_id 조건에 따라 '김부장'을 매니저로 하는 직원들('이과장', '정차장')을 찾습니다. 이 결과는 기존 결과에 UNION ALL로 추가됩니다. (lvl = 2)
3. 재귀 멤버 실행 (2차): 이제 emp_hierarchy에는 '김부장', '이과장', '정차장'의 정보가 있습니다. 재귀 멤버는 바로 이전 단계에서 추가된 '이과장'과 '정차장'을 참조하여 그들의 하위 직원을 찾습니다. '박대리'와 '홍사원'이 찾아져 결과에 추가됩니다. (lvl = 3)
4. 반복 및 종료: 이 과정은 재귀 멤버가 더 이상 새로운 행을 반환하지 않을 때까지 반복됩니다. 마지막으로 '최사원'이 추가된 후에는 더 이상 하위 직원이 없으므로 재귀가 종료됩니다.
5. 최종 SELECT: WITH 절의 실행이 완료되면, 최종 SELECT 문이 emp_hierarchy에 쌓인 모든 데이터를 조회하여 결과를 보여줍니다.