오라클 SQL: CONNECT BY로 계층적 데이터 관리

목차

1. 계층적 쿼리 개요
2. CONNECT BY 구문
3. PRIOR와 START WITH
4. SYS_CONNECT_BY_PATH 함수
5. LEVEL 함수
6. NOCYCLE과 CONNECT_BY_ISCYCLE
7. 예제

---

1. 계층적 쿼리 개요

계층적 쿼리는 트리 구조와 같은 계층적 데이터 관계를 표현하는 데 사용됩니다. 예를 들어, 직원과 관리자 간의 관계, 제품 카테고리, 조직 구조 등이 계층적 데이터의 예입니다. 오라클에서는 CONNECT BY 절을 사용하여 이러한 계층적 데이터를 조회할 수 있습니다.

---

2. CONNECT BY 구문

CONNECT BY 절은 계층적 관계를 정의하는 데 사용됩니다. PRIOR 키워드를 사용하여 부모-자식 관계를 설정합니다.

2.1. 구문

SELECT column1, column2, ...
FROM table_name
START WITH condition
CONNECT BY PRIOR parent_column = child_column;

• START WITH: 트리 구조의 루트 노드를 지정합니다.
• CONNECT BY PRIOR parent_column = child_column: 부모-자식 관계를 정의합니다.

2.2. 예제

• 직원 테이블에서 상사와 부하 직원 간의 계층적 관계를 조회:

SELECT employee_id, manager_id, first_name, last_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

---

3. PRIOR와 START WITH

PRIOR 키워드는 부모-자식 관계를 정의하는 데 사용되며, START WITH 절은 계층 구조의 시작점을 지정합니다.

3.1. 예제

• 특정 직원에서 시작하여 그 부하 직원들을 계층적으로 조회:

SELECT employee_id, manager_id, first_name, last_name
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

이 쿼리는 직원 ID가 100인 직원에서 시작하여 그의 모든 부하 직원들을 계층적으로 조회합니다.

---

4. SYS_CONNECT_BY_PATH 함수

SYS_CONNECT_BY_PATH 함수는 계층적 경로를 문자열로 반환합니다. 이 함수는 계층적 쿼리에서 각 노드의 경로를 표시하는 데 유용합니다.

4.1. 구문

SYS_CONNECT_BY_PATH(column, delimiter)

4.2. 예제

• 직원 경로를 슬래시(/) 구분자로 표시:

SELECT employee_id, first_name, last_name, 
       SYS_CONNECT_BY_PATH(last_name, '/') AS employee_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

---

5. LEVEL 함수

LEVEL 함수는 계층적 쿼리에서 현재 노드의 깊이를 반환합니다. 루트 노드는 LEVEL = 1입니다.

5.1. 예제

• 각 직원의 계층 레벨을 조회:

SELECT LEVEL, employee_id, first_name, last_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

---

6. NOCYCLE과 CONNECT_BY_ISCYCLE

순환 참조(circular reference)를 피하기 위해 NOCYCLE 키워드를 사용합니다. CONNECT_BY_ISCYCLE은 순환 참조가 발생했는지 여부를 나타내는 가상 컬럼입니다.

6.1. 구문

SELECT column1, column2, ...
FROM table_name
START WITH condition
CONNECT BY NOCYCLE PRIOR parent_column = child_column;

6.2. 예제

• 순환 참조를 방지하고 순환 여부를 표시:

SELECT employee_id, manager_id, first_name, last_name,
       CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

---

7. 예제

예제 1: 조직도 조회

조직도의 계층적 관계를 조회하여 각 직원의 상사와 부하 직원을 표시합니다.

SELECT employee_id, manager_id, first_name, last_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

예제 2: 특정 직원의 전체 경로 조회

직원 ID가 150인 직원의 상사 경로를 조회합니다.

SELECT employee_id, first_name, last_name, 
       SYS_CONNECT_BY_PATH(last_name, '/') AS employee_path
FROM employees
START WITH employee_id = 150
CONNECT BY PRIOR manager_id = employee_id;

예제 3: 제품 카테고리 계층 조회

제품 카테고리 테이블에서 각 카테고리와 하위 카테고리를 계층적으로 조회합니다.

SELECT category_id, parent_category_id, category_name, LEVEL
FROM product_categories
START WITH parent_category_id IS NULL
CONNECT BY PRIOR category_id = parent_category_id;

---

위의 예제들을 통해 CONNECT BY를 사용하여 계층적 데이터를 관리하는 방법을 이해할 수 있습니다. 오라클의 계층적 쿼리는 복잡한 데이터 관계를 쉽게 표현하고 관리할 수 있는 강력한 도구입니다. 이를 활용하여 조직도, 제품 카테고리, 직원 관계 등을 효율적으로 관리할 수 있습니다.