[ORACLE] CONNECT BY 함수 사용하기

ORACLE 에서는 계층데이터를 위해 CONNECT BY 절을 지원합니다.

CONNECT BY 구성

CONNECT BY 는 아래의 3개의 구문으로 구성됩니다.

구문	설명
WHERE	데이터를 가져온 뒤 마지막으로 조건절에 맞게 정리
START WITH	어떤 데이터로 계층구조를 지정하는지 지정
CONNECT BY	각 행들의 연결 관계를 설정

* START WITH 는 가장 처음에 데이터를 거르는 플랜을 타게 되고, 따라서 이 컬럼에는 인덱스가 걸려있어야 성능을 보장받습니다.

* CONNECT BY 절의 결과에는 LEVEL 이라는 컬럼이 있으며, 이는 계층의 깊이를 의미합니다.

CONNECT BY 사용하기

먼저 EMP 테이블에 데이터가 아래와 같이 있습니다.

SQL> SELECT * FROM EMP;

     EMPNO ENAME                          JOB                                MGR HIREDATE        SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
      7839 KING                           PRESIDENT                              81/11/17       5000                    10
      7698 BLAKE                          MANAGER                           7839 81/05/01       2850                    30
      7782 CLARK                          MANAGER                           7839 81/06/09       2450                    10
      7566 JONES                          MANAGER                           7839 81/04/02       2975                    20
      7902 FORD                           ANALYST                           7566 81/12/03       3000                    20
      7369 SMITH                          CLERK                             7902 80/12/17        800                    20
      7499 ALLEN                          SALESMAN                          7698 81/02/20       1600        300         30
      7521 WARD                           SALESMAN                          7698 81/02/22       1250        500         30
      7654 MARTIN                         SALESMAN                          7698 81/09/28       1250       1400         30
      7844 TURNER                         SALESMAN                          7698 81/09/08       1500          0         30
      7900 JAMES                          CLERK                             7698 81/12/03        950                    30
      7934 MILLER                         CLERK                             7782 82/01/23       1300                    10

12 행이 선택되었습니다.

CONNECT BY 구문을 써서 쿼리를 수행시킨 결과는 다음과 같습니다.

아래의 쿼리는 JOB = PRESIDENT 를 기준으로 ( 계층의 LEVEL 1 ), EMPNO 와 MGR 이 같은 관계를 계층적으로 출력합니다.

SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR;

     LEVEL      EMPNO ENAME                                 MGR JOB
---------- ---------- ------------------------------ ---------- ---------------------------
         1       7839 KING                                      PRESIDENT
         2       7566 JONES                                7839 MANAGER
         3       7902 FORD                                 7566 ANALYST
         4       7369 SMITH                                7902 CLERK
         2       7698 BLAKE                                7839 MANAGER
         3       7499 ALLEN                                7698 SALESMAN
         3       7521 WARD                                 7698 SALESMAN
         3       7654 MARTIN                               7698 SALESMAN
         3       7844 TURNER                               7698 SALESMAN
         3       7900 JAMES                                7698 CLERK
         2       7782 CLARK                                7839 MANAGER
         3       7934 MILLER                               7782 CLERK

12 행이 선택되었습니다.

깊이가 깊어지는 경우 LEVEL 을 조건절에 걸어서 원하는 깊이까지만 볼 수 있습니다.

SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR AND LEVEL <= 3;

     LEVEL      EMPNO ENAME                                 MGR JOB
---------- ---------- ------------------------------ ---------- ---------------------------
         1       7839 KING                                      PRESIDENT
         2       7566 JONES                                7839 MANAGER
         3       7902 FORD                                 7566 ANALYST
         2       7698 BLAKE                                7839 MANAGER
         3       7499 ALLEN                                7698 SALESMAN
         3       7521 WARD                                 7698 SALESMAN
         3       7654 MARTIN                               7698 SALESMAN
         3       7844 TURNER                               7698 SALESMAN
         3       7900 JAMES                                7698 CLERK
         2       7782 CLARK                                7839 MANAGER
         3       7934 MILLER                               7782 CLERK

11 행이 선택되었습니다.

혹은 LEVEL 의 깊이가 3이하이며, EMPNO 가 7500 보다 큰 데이터만도 볼수 있습니다.

SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB
FROM EMP
WHERE EMPNO > 7500
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR AND LEVEL <= 3;

     LEVEL      EMPNO ENAME                                 MGR JOB
---------- ---------- ------------------------------ ---------- ---------------------------
         1       7839 KING                                      PRESIDENT
         2       7566 JONES                                7839 MANAGER
         3       7902 FORD                                 7566 ANALYST
         2       7698 BLAKE                                7839 MANAGER
         3       7521 WARD                                 7698 SALESMAN
         3       7654 MARTIN                               7698 SALESMAN
         3       7844 TURNER                               7698 SALESMAN
         3       7900 JAMES                                7698 CLERK
         2       7782 CLARK                                7839 MANAGER
         3       7934 MILLER                               7782 CLERK

10 행이 선택되었습니다.