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

계층구조 쿼리란?

오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.

예를 들어서 아래의 데이터를 보면

EMPNO   ENAME    SAL    MGR

------ ------- ------ ------

  7369  SMITH     800   7902

  7902  FORD     3000   7566

• - empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
• - empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.

이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 조회 할 수 있다.

계층구조 쿼리 Synctax

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 절 순서로 풀리게 되어있다.

계층구조 쿼리 예제

간단예제

아래는 직업이 PRESIDENT을 기준으로 계층 구조로 조회하는 예이다.

-- LEVEL컬럼으로 depth를 알수 있다.

-- JONES의 관리자는 KING 이며, SCOTT의 관리자는 JONES 이다.

-- 상/하의 계층 구조를 쉽게 조회 할 수 있다.

SELECT LEVEL, empno, ename, mgr

  FROM emp

 START WITH job = 'PRESIDENT'

CONNECT BY PRIOR  empno = mgr;

 

 

LEVEL      EMPNO  ENAME        MGR

------ -------- --------    -------

     1       7839   KING

     2       7566   JONES      7839

     3       7788   SCOTT      7566

     4       7876   ADAMS      7788

     3       7902   FORD       7566

     4       7369   SMITH      7902

...

PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서 상하위간의 관계를 기술할때 사용.

LEVEL의 활용

LEVEL Pseudocolumn을 이용하면 계층구조 쿼리를 좀 더 다양하게 활용 할 수 있다.

아래는 LEVEL의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.

-- SQL*Plus에서만 깔끔하게 보기위해서

COL ename FORMAT A20;

 

 

-- 왼쪽에 LEVEL만큼 공백을 추가하여 계층구조로 조회하는 예제

SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job

  FROM emp

 START WITH job='PRESIDENT'

CONNECT BY PRIOR empno=mgr;

 

 

LEVEL ENAME                     EMPNO    MGR    JOB

------ --------------------    -------  -----  --------

     1 KING                       7839         PRESIDEN

     2     JONES                  7566   7839  MANAGER

     3         SCOTT              7788   7566  ANALYST

     4             ADAMS          7876   7788  CLERK

     3         FORD               7902   7566  ANALYST

     4             SMITH          7369   7902  CLERK

     2     BLAKE                  7698   7839  MANAGER

...

아래는 LEVEL별로 급여 합계와 사원수를 조회하는 예제이다.

-- LEVEL별로 급여 합계와 사원수를 조회하는 예제 SELECT LEVEL, AVG(sal) total, COUNT(empno) cnt   FROM emp  START WITH job='PRESIDENT' CONNECT BY PRIOR empno=mgr       GROUP BY LEVEL  ORDER BY LEVEL;   LEVEL      TOTAL        CNT -------- ---------- ----------        1       5000          1        2       8275          3        3      13850          8        4       1900          2

PRIOR의 활용

PRIOR연산자를 SELECT 절에서 사용해보자.

아래는 사원의 관리자를 PRIOR연산자를 이용해서 조회하는 예제이다.

-- SQL*Plus에서만 깔끔하게 보기위해서

COL mgrname FORMAT A10;

 

 

-- SELECT절에 "PRIOR ename mgrname"을 확인해 보자

SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename,

       PRIOR ename mgrname,

       empno, mgr, job

  FROM emp

 START WITH job='PRESIDENT'

CONNECT BY PRIOR empno=mgr;

 

 

  LEVEL ENAME                MGRNAME         EMPNO        MGR JOB

------- -------------------- ---------- ---------- ---------- ---------

      1 KING                                  7839            PRESIDENT

      2     JONES            KING             7566       7839 MANAGER

      3         SCOTT        JONES            7788       7566 ANALYST

      4             ADAMS    SCOTT            7876       7788 CLERK

      3         FORD         JONES            7902       7566 ANALYST

      2     BLAKE            KING             7698       7839 MANAGER

      3         MARTIN       BLAKE            7654       7698 SALESMAN

      3         TURNER       BLAKE            7844       7698 SALESMAN

      3         JAMES        BLAKE            7900       7698 CLERK

      2     CLARK            KING             7782       7839 MANAGER

      3         MILLER       CLARK            7934       7782 CLERK

PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서만 사용할 수 있는 것은 아니다.

Bottom Up 조회 예제

위 간단 예제를 역순(자식에서 부모로 트리 구성, Bottom Up)으로 조회 해 보자

-- SQL*Plus에서만 깔끔하게 보기위해서

COL ename FORMAT A20;

 

 

-- ename을 기준으로 Bottom Up으로 조회하는 예제이다.

SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job

  FROM emp

 START WITH ename='SMITH'  -- 최 하위 노드 값이 와야 한다.

CONNECT BY PRIOR mgr = empno;

 

 

LEVEL ENAME                EMPNO      MGR    JOB

------ ---------------    -------- -------- ---------

     1 SMITH                 7369     7902   CLERK

     2     FORD              7902     7566   ANALYST

     3         JONES         7566     7839   MANAGER

     4             KING      7839            PRESIDENT

PRIOR 컬럼에 따라(상위 OR 하위) 계층전개 방향이 달라진다.

• - 순방향(Top Down-상위~하위) : PRIOR 하위 = 상위
• - 역방향(Bottom Up-하위~상위) : PRIOR 상위 = 하위

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