Oracle Advanced SQL 강좌

계층구조 쿼리란?

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

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

?

1 2 3 4

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을 기준으로 계층 구조로 조회하는 예이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 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의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- 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별로 급여 합계와 사원수를 조회하는 예제이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- 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연산자를 이용해서 조회하는 예제이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 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)으로 조회 해 보자

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 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 상위 = 하위