계층형 쿼리

계층형 쿼리 Hierarchical Query

계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미한다.
테이블에 계층형 데이터가 존재하는 경우, 데이터를 조회하기 위해 계층형 쿼리를 사용한다.
노드처럼 이루어진 데이터라고 생각하면 된다.

과정

오라클에서 계층형 쿼리를 사용하는 과정은 아래와 같다.

• SELECT : 조회하고자 하는 컬럼을 지정한다.
• FROM : 대상 테이블을 지정한다.
• WHERE : 모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 필터링하여 추출한다.
• START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.
• CONNECT BY : CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는 구문이다.
  • CONNECT BY (NOCYCLE) (PRIOR) A AND B 방식으로 사용된다.
  • PRIOR (PK) = (FK) 형태를 사용하면 부모에서 자식 방향으로 전개되는 순방향 전개가 수행된다.
  • PRIOR (FK) = (PK) 형태를 사용하면 자식에서 부모 방향으로 전개되는 역방향 전개가 수행된다.
  • NOCYCLE 이 추가되면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
• ORDER BY SIBLINGS BY : 동일한 레벨의 형제 노드 사이에서 정렬을 수행한다.

계층형 쿼리에서 사용되는 가상 컬럼

LEVEL	루트데이터면 1, 그 하위데이터면 2 ... 하위데이터가 있을때마다 1씩 증가한다.
CONNECT_BY_ISLEAF	전개과정에서 리프 데이터(=자식 데이터가 하나도 없는 데이터) 이면 1, 그렇지 않으면 0을 출력한다.
CONNECT_BY_ISCYCLE	전개과정에서 자식을 갖는데 해당 데이터가 부모로서 존재하면 1, 그렇지 않으면 0을 출력한다.
SYS_CONNECT_BY_PATH	루트데이터부터 자신까지 오는 경로를 출력한다.

계층형 쿼리 실습

위의 형태로 생긴 노드를 데이터화 하면 아래와 같다.

CREATE TABLE EMP (
    EMPNM VARCHAR2(4),
    BOSSNM VARCHAR2(4) );

INSERT INTO EMP VALUES ('A', NULL);
INSERT INTO EMP VALUES ('B', 'A');
INSERT INTO EMP VALUES ('C', 'A');
INSERT INTO EMP VALUES ('D', 'B');
INSERT INTO EMP VALUES ('E', 'B');
INSERT INTO EMP VALUES ('F', 'B');
INSERT INTO EMP VALUES ('G', 'C');
INSERT INTO EMP VALUES ('H', 'C');
INSERT INTO EMP VALUES ('I', 'D');
INSERT INTO EMP VALUES ('J', 'D');
INSERT INTO EMP VALUES ('K', 'E');
INSERT INTO EMP VALUES ('L', 'G');
INSERT INTO EMP VALUES ('M', 'H');
INSERT INTO EMP VALUES ('N', 'H');

여기서 BOSSNM 이 NULL 인 값을 첫 시작값으로 하는 순방향 전개를 펼쳐보자.

추가로, LEVEL 컬럼을 주고 CONNECT_BY_ISLEAF 를 사용하여 리프데이터인 경우 1을 출력하게 하였다.

SELECT LEVEL
       , EMPNM
       , BOSSNM
       , CONNECT_BY_ISLEAF
FROM EMP
START WITH BOSSNM IS NULL
CONNECT BY PRIOR EMPNM = BOSSNM;

B 부터 내려가 F 까지 전부 타고, C 로 이어져 N 까지 타는 순방향 전개로 진행되었다.

이번에는 CONNECT_BY_ROOT 명령어를 사용하여 루트데이터를 출력해보자. 모든 데이터의 최상위 관리자는 A 이므로 A 가 출력되는것이 맞다.

SELECT LEVEL
       , EMPNM
       , BOSSNM
       , CONNECT_BY_ISLEAF
       , CONNECT_BY_ROOT EMPNM AS "최상위관리자"
FROM EMP
START WITH BOSSNM IS NULL
CONNECT BY PRIOR EMPNM = BOSSNM;

SYS_CONNECT_BY_PATH 을 사용하여 경로도 출력해보자.

SELECT LEVEL
       , EMPNM
       , BOSSNM
       , CONNECT_BY_ISLEAF
       , CONNECT_BY_ROOT EMPNM AS "최상위관리자"
       , SYS_CONNECT_BY_PATH(EMPNM , '/') AS "경로"
FROM EMP
START WITH BOSSNM IS NULL
CONNECT BY PRIOR EMPNM = BOSSNM;