계층형 쿼리

출처 : https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52332

오라클에서 사용할 수 있는 쿼리 구문.

한 테이블에 담겨있는 레코드들이 상,하관계를 이루며 존재할 때 이 관계에 따라 레코드를 표현할 수 있는 구문입니다.

 

EMP 테이블로 테스트 해보며 정리해보겠습니다.

 

SQL> select * from emp;

EMPNO ENAME      JOB       MGR   HIREDATE            SAL       COMM      DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
 7369 SMITH      CLERK      7902 2020/11/19 20:08:48       800            20
 7499 ALLEN      SALESMAN   7698 2020/11/19 20:08:48      1600       300  30
 7521 WARD       SALESMAN   7698 2020/11/19 20:08:48      1250       500  30
 7566 JONES      MANAGER    7839 2020/11/19 20:08:48      2975            20
 7654 MARTIN     SALESMAN   7698 2020/11/19 20:08:48      1250      1400  30
 7698 BLAKE      MANAGER    7839 2020/11/19 20:08:48      2850            30
 7782 CLARK      MANAGER    7839 2020/11/19 20:08:48      2450            10
 7788 SCOTT      ANALYST    7566 2020/11/19 20:08:48      2000            20
 7839 KING       PRESIDENT       2020/11/19 20:08:48      5000            10
 7844 TURNER     SALESMAN   7698 2020/11/19 20:08:48      1500         0  30
 7876 ADAMS      CLERK      7788 2020/11/19 20:08:48      1100            20
 7900 JAMES      CLERK      7698 2020/11/19 20:08:48       950            30
 7902 FORD       ANALYST    7566 2020/11/19 20:08:48      3000            20
 7934 MILLER     CLERK      7782 2020/11/19 20:08:48      1300            10

14 rows selected.

EMP 테이블은 14개의 행으로 이루어져있으며 그 중에 EMPNO와 MGR의 값을 보면 EMPNO는 각 로우의 PK로써 고유한 값입니다. 그리고 MGR은 있는로우가 대부분이지만 없는 로우도 존재하고 MGR의 값은 EMPNO의  값 중 일부가 있는 것으로 확인됩니다.

 

그렇다면 MGR에 값이 있는경우에는 MGR에 있는 값의 EMPNO가 매니저(상사)인 것을 알 수 있겠습니다.

이것을 계층으로 표현할 수 있는게 오라클의 계층형쿼리인데요 한번 쿼리로 확인해보겠습니다.

 

SQL> select level,a.* from emp a
start with a.mgr is null
connect by prior a.empno = a.mgr;

LEVEL       EMPNO ENAME      JOB       MGR   HIREDATE            SAL       COMM      DEP
----------- ----- ---------- --------- ----- ------------------- --------- --------- ---
          1  7839 KING       PRESIDENT       2020/11/19 20:08:48      5000            10
          2  7566 JONES      MANAGER    7839 2020/11/19 20:08:48      2975            20
          3  7788 SCOTT      ANALYST    7566 2020/11/19 20:08:48      2000            20
          4  7876 ADAMS      CLERK      7788 2020/11/19 20:08:48      1100            20
          3  7902 FORD       ANALYST    7566 2020/11/19 20:08:48      3000            20
          4  7369 SMITH      CLERK      7902 2020/11/19 20:08:48       800            20
          2  7698 BLAKE      MANAGER    7839 2020/11/19 20:08:48      2850            30
          3  7499 ALLEN      SALESMAN   7698 2020/11/19 20:08:48      1600       300  30
          3  7521 WARD       SALESMAN   7698 2020/11/19 20:08:48      1250       500  30
          3  7654 MARTIN     SALESMAN   7698 2020/11/19 20:08:48      1250      1400  30
          3  7844 TURNER     SALESMAN   7698 2020/11/19 20:08:48      1500         0  30
          3  7900 JAMES      CLERK      7698 2020/11/19 20:08:48       950            30
          2  7782 CLARK      MANAGER    7839 2020/11/19 20:08:48      2450            10
          3  7934 MILLER     CLERK      7782 2020/11/19 20:08:48      1300            10

14 rows selected.

 

LEVEL이 1인게 제일 첫번째 값이고 그로부터 숫자가 높아지며 계층을 나타내고 있습니다.

 

좀더 보기 쉽게 표현하면 이렇게 됩니다.

SQL> select level,LPAD(' ',4*(LEVEL-1))||a.ename,a.empno,a.mgr 
from emp a
start with a.mgr is null
connect by prior a.empno = a.mgr;

 

 

==========================================================================

계층구조 쿼리 구문

START WITH

• - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
• - 서브쿼리를 사용할 수도 있다.

CONNECT BY

• - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
• - PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
• - CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
• - CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
• - CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
• - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

• - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

• - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

• - 첫째 START WITH 절
• - 둘째 CONNECT BY 절
• - 세째 WHERE 절 순서로 풀리게 되어있다.

===========================================================================

 

출처 : http://www.gurubee.net/lecture/1300

계층구조 쿼리(Hierarchical Queries)란?