[Oracle] 계층형 쿼리(Hierarchical Queries)

Hierarchical Queries

해당 테이블에 계층형 데이터가 있다면 계층형 쿼리 절을 사용해서 계층 순서에 맞게 데이터를 읽어올 수 있다.

---

 

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

 

START WITH 

최상위 노드를 설정한다. 위의 예시 코드에서는 상위 노드가 설정되어 있지 않은 노드를 찾아 설정해줬다.

 

CONNECT BY  (NOCYCLE) [condition]

상위 노드와 하위 노드의 관계를 설정한다.

 

NOCYCLE  loop를 유발하는 데이터가 있더라도 결괏값을 반환해주는 파라미터이다.(NOCYCLE이 없으면 에러가 발생한다.) 만약 상위-하위 노드의 관계를 제대로 설정해 주지 않고 상위 노드가 하위 노드를 다시 상위로 가지고 있거나 하면 loop가 발생한다. 이때는 가상 컬럼(Pseudo-Column)인 CONNECT_BY_ISCYCLE을 사용해 어떤 열에서 loop가 발생하고 있는지 확인해서 해결해야 한다.

 

[condition]

만약, 상위에서 하위로 계층 구조를 설정하여 데이터 값을 불러오고 싶다면(Top Down)

CONNECT BY PRIOR 하위 컬럼 = 상위 컬럼

반대로 하위에서 상위로 구조를 설정하고 싶다면(Bottom Up) 아래와 같이 설정하면 된다.

CONNECT BY PRIOR 상위 컬럼 = 하위 컬럼
or
CONNECT BY 하위컬럼 = PRIOR 상위 컬럼

 

 

※ 가상 컬럼(Pseudo-Column)

이름	설정
LEVEL	최상위 데이터부터 하위 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF	최하위 노드(leaf)이면 1, 아니면 0을 반환한다.
CONNECT_BY_ISCYCLE	루프가 발생하는 노드이면 1, 아니면 0을 반환한다.

 

Note

---

계층형 쿼리에서는 ORDER BY 절이나 GROUP BY 절을 사용할 수 없다. 이렇게 정렬하려는 순간 계층형 구조가 깨지고 만다. 만약 똑같은 상위 노드를 가진 하위 노드들을 정렬하고 싶다면 ORDER SIBLINGS BY 절을 사용한다.

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;
      
      
LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3

 

 

Examples for loop

---

hr.employees 테이블을 예로 들어보자.

 

 

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
David	                          148        100          2

 

David의 매니저는 King이고 King은 매니저가 없기 때문에 최상위에 있는 매니저임을 알 수 있다.  만약 이때 King의 매니저를 David로 설정하면 loop가 발생한다.

 

 

오류 메시지

ERROR: ORA-01436: CONNECT BY loop in user data

 

해결 방법

NOCYCLE 파라미터를 이용해서 데이터를 강제 반환해주고 CONNECT_BY_ISCYCLE 가상 컬럼을 사용해 loop가 발생한 행을 찾아 상위-하위 관계가 제대로 정립되어있는지 확인한다.

 

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;

Employee                   Cycle  LEVEL Path
------------------------- ------ ------ -------------------------
David                        1      2 	/King/David

 

 

 

 

---

ref.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm