계층 쿼리 절(hierarchical query clause)은 오라클 데이터베이스의 전통적인 계층 쿼리 구문이다.

​

[기본 문법]

계층 쿼리 절은 WHERE 절 다음에 기술하며, FROM 절이 수행된 후 수행된다.

START WITH 절과 CONNECT BY 절로 구성되며, START WITH 절이 수행된 후 CONNECT BY 절이 수행된다.

START WITH 절은 생략이 가능하다.

​

[START WITH condition] CONNECT BY [NOCYCLE] condition

​

절 - 설명

START WITH 절 - 루트 노드를 생성하며 1번만 수행

CONNECT BY 절 - 루트 노드의 하위 노드를 생성하며 조회 결과가 없을 때까지 반복 수행

​

계층 쿼리 절에서 사용할 수 있는 연산자, 슈도 칼럼, 함수다.

​

유형 - 항목 - 설명

연산자 - PRIOR - 직전 상위 노드의 값을 반환

            - CONNECT_BY_ROOT - 루트 노드의 값을 반환

슈도 칼럼 - LEVEL - 현재 레벨을 반환

                - CONNECT_BY_ISLEAF - 리프 노드인 경우 1, 아니면 0을 반환

                - CONNECT_BY_ISCYCLE - 루프가 발생한 경우 1, 아니면 0을 반환

함수 - SYS_CONNECT_BY_PATH - 루트 노드에서 현재 노드까지의 경로를 반환

​

아래는 계층 쿼리 절을 사용한 쿼리다.

START WITH 절로 MGR이 존재하지 않는 행을 조회하고, CONNECT BY 절로 현재 노드의 MGR이 직전 상위 노드의 EMPNO인 행을 반복해서 조회한다.

ENAME 열은 LEVEL 슈도 칼럼과 LPAD 함수를 사용하여 계층의 레벨에 따라 값을 들여쓰기 했다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

            , PRIOR EMPNO AS EMPNO_P

  FROM EMP

START WITH MGR IS NULL       -- MGR이 존재하지 않는 행

CONNECT BY MGR = PRIOR EMPNO -- MGR이 부모 노드의 EMPNO인 행

;

​

[SYS_CONNECT_BY_PATH 함수]

SYS_CONNECT_BY_PATH 함수는 루트 노드에서 현재 노드까지의 COLUMN을 CHAR로 구분하여 연결한 값을 반환한다. COLUMN 값에 CHAR가 포함되어 있으면 ORA-30004 에러, 연결한 값의 길이가 4000보다 길면 ORA-01489 에러가 발생한다.

​

SYS_CONNECT_BY_PATH (COLUMN, CHAR)

​

에러 - 설명

ORA-30004 - ... 구분 기호를 열 값의 일부로 사용할 수 없습니다.

ORA-01489 - 문자열 연결의 결과가 너무 깁니다.

​

아래는 CONNECT_BY_ROOT 연산자, CONNECT_BY_ISLEAF 슈도 칼럼, SYS_CONNNECT_BY_PATH 함수를 사용한 쿼리다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

            , CONNECT_BY_ISLEAF AS LF

            , CONNECT_BY_ROOT ENAME AS RT

            , SYS_CONNECT_BY_PATH (ENAME, ',') AS PT

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

​

[동작 원리]

계층 쿼리 절은 START WITH 절로 루트 노드를 생성한 후, 결과가 없을 때까지 CONNECT 절을 반복 수행하여 하위 노드를 생성한다.

(계층 쿼리 절은 다양한 방식으로 수행될 수 있다. 수행 방식은 옵티마이저에 의해 결정된다.)

​

[1단계 (LEVEL = 1)]

START WITH 절을 수행하여 루트 노드를 생성한다. START WITH 절은 WHERE 절과 유사하게 동작한다.

​

SELECT 1 AS LV, EMPNO, ENAME, MGR

  FROM EMP

WHERE MGR IS NULL -- START WITH MGR IS NULL

;

​

-- 다음 예제를 위해 1단계 수행 결과를 임시 테이블에 저장하자.

DROP TABLE T1 PURGE;

​

CREATE TABLE T1 AS SELECT 1 AS LV, EMPNO, ENAME, MGR FROM EMP WHERE MGR IS NULL;

​

[2단계 (LEVEL = 2)]

CONNECT BY 절로 임시 테이블에 저장된 1단계 결과와 EMP 테이블을 조인한다.

CONNECT BY 절도 WHERE 절과 유사하게 동작한다.

​

SELECT 2 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 1

     AND C.MGR = P.EMPNO -- CONNECT BY MGR = PRIOR EMPNO

;

​

다음 예제를 위해 2단계 수행 결과를 임시 테이블에 저장하자.

INSERT INTO T1

SELECT 2, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C

WHERE P.LV = 1 AND C.MGR = P.EMPNO;

​

[3단계 (LEVEL = 3)]

CONNECT BY 절로 임시 테이블에 저장된 2단계 결과와 EMP 테이블을 조인한다.

​

SELECT 3 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 2

     AND C.MGR = P.EMPNO;

​

다음 예제를 위해 3단계 수행 결과를 임시 테이블에 저장하자.

INSERT INTO T1

SELECT 3, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C 

WHERE P.LV = 2 AND C.MGR = P.EMPNO;

​

[4단계 (LEVEL = 4)]

CONNECT BY 절로 임시 테이블에 저장된 3단계 결과와 EMP 테이블을 조인한다.

​

SLEECT 4 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 3

     AND C.MGR = P.EMPNO;

​

다음 예제를 위해 4단계 수행 결과를 임시 테이블에 저장하자.

INSERT INTO T1

SELECT 4, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C

WHERE P.LV = 3

     AND C.MGR = P.EMPNO;

​

[5단계 (LEVEL = 5)]

CONNECT BY 절로 임시 테이블에 저장된 4단계 결과와 EMP 테이블을 조인한다.

​

SELECT 5 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 4

     AND C.MGR = P.EMPNO;

​

결과가 반환되지 않으면 CONNECT BY 절의 수행을 멈추고 수행 결과가 저장된 임시 테이블을 조회하여 결과를 반환한다.

​

SELECT * FROM T1;

​

[전개 방향]

순환 관계는 순방향 또는 역방향으로 전개할 수 있다.

순방향 전개와 역방향 전개는 데이터 모델 상의 전개 방향이 반대일 뿐 동작 원리는 동일하다.

​

                           전개 방향             -    START WITH 절             -           CONNECT BY 절

순방향 전개        부모 -> 자식        - 부모 노드 조회                   -         PK에 PRIOR 기술

역방향 전개        자식 -> 부모        - 자식 노드 조회                   -          FK에 PRIOR 기술

​

아래 쿼리는 순방향으로 계층을 전개한다.

순방향 전개는 START WITH 절로 부모 노드를 조회하고, CONNECT BY 절을 통해 자식 노드로 계층을 전개한다.

PK(EMPNO)에 PRIOR 연산자를 기술하여 현재 노드의 MGR이 부모 노드의 EMPNO인 행을 조회한다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

​

아래 쿼리는 역방향으로 계층을 전개한다.

역방향 전개는 START WITH 절로 자식 노드를 조회하고, CONNECT BY 절을 통해 부모 노드로 계층을 전개한다.

FK(MGR)에 PRIOR 연산자를 기술하여 현재 노드의 EMPNO가 자식 노드의 MGR인 행을 조회한다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

  FROM EMP

START WITH ENAME = 'ADAMS'

CONNECT BY EMPNO = PRIOR MGR;

​

[계층 정렬]

계층 쿼리 절은 형제 노드의 행을 정렬하기 위해 SIBLINGS 키워드를 제공한다.

​

계층 쿼리 절에 ORDER BY 절을 사용하면 계층 구조와 무관하게 행이 정렬된다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR, SAL

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIRO EMPNO

ORDER BY SAL;

​

ORDER BY 절에 SIBLINGS 키워드를 사용하면 형제 노드 내에서만 행이 정렬되기 때문에 계층 구조를 유지한 상태로 행을 정렬할 수 있다.

CLARK, BLAKE, JONES는 형제 노드다. SAL 순서로 행이 정렬된다.

BLAKE의 하위 노드인 JAMES ~ ALLEN도 SAL 순서로 행이 정렬된다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR, SAL

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO

ORDER SIBLINGS BY SAL;

​

[루프 처리]

부모 노드가 현재 노드의 자식 노드로 연결되면 루프(loop)가 발생한다.

계층 쿼리 절은 루프를 처리하기 위해 NOCYCLE 키워드와 CONNECT_BY_ISCYCLE 슈도 칼럼을 제공한다.

​

예제를 위해 아래와 같이 테이블을 생성하자.

NVL 함수로 MGR이 널인 KING의 MGR을 SCOTT의 EMPNO인 7788로 변경했다.

​

DROP TABLE EMP_L PURGE;

CREATE TABLE EMP_L AS SELECT EMPNO, ENAME, NVL (MGR, 7788) AS MGR FROM EMP;

​

아래는 EMP_L 테이블을 조회한 결과이다.

KING -> JONES -> SCOTT -> KING 구간에서 루프가 발생한다.

​

SELECT EMPNO, ENAME, MGR FROM EMP_L;

​

계층 전개 시 루프가 발생하면 에러가 발생한다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

  FROM EMP_L

START WITH EMPNO = 7839

CONNECT BY MGR = PRIOR EMPNO;

​

ORA-01436 : CONNECT BY의 루프가 발생되었습니다.

​

아래 쿼리는 CONNECT BY 절에 NOCYCLE 키워드를 기술했다.

CONNECT BY 절에 NOCYCLE 키워드를 기술하면 루프가 발생한 노드를 전개하지 않는다.

IC 열이 1인 행이 루프가 발생한 행이다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || EMPNO AS ENAME, MGR

            , CONNECT_BY_ISCYCLE AS IC

  FROM EMP_L

START WITH EMPNO = 7839

CONNECT BY NOCYCLE MGR = PRIOR EMPNO;

 

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

+ Recent posts