계층형쿼리

- 한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관계를 이루며 존재할 때, 이 관계에 따라 레코드를 hierarchical(상하위)한 구조로 가져올 때 사용되는 SQL

· 노드(node): 표시된 항목(위 그림에서 원 모양)
· 레벨(level): 각각의 계층
· 루트(root): 최상위에 있는 노드
· 부모(parent): 상위에 있는 노드(부모 노드)
· 자식(child): 하위에 있는 노드(자식 노드)
· 리프(leaf): 하위에 연결된 노드가 없는 항목

 SELECT ...
   FROM TABLE
  WHERE CONDITION AND CONDITION
  START WITH CONDITION
CONNECT BY [NOCYCLE] CONDITION AND CONDITION
 [ORDER SIBLINGS BY COLUMN, COLUMN......]

✔️ START WITH
- 루트 노드의 데이터를 지정
- 루트 노드는 최상위 노드
- 서브쿼리 사용가능

✔️ CONNECT BY
- 부모 노드와 자식 노드들 간의 관계를 지정
- PRIOR 연산자로 계층구조를 표현할 수 있음
- 서브쿼리 사용불가능

✔️ PRIOR
- CONNECT BY절에 사용되며, 현재 읽은 컬럼을 지정
- PRIOR 자식 = 부모(자식→부모) , 자식 데이터에서 부모 데이터 방향으로 전개하는 순방향 전개
- PRIOR 부모 = 자식(부모→자식), 부모 데이터에서 자식 데이터 방향으로 전개하는 역방향 전개

✔️ NOCYCLE
- 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말함
- 사이클이 발생한 데이터는 런타임 오류가 나타남 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않음

✔️ ORDER SIBLINGS BY
- 형제 노드(동일 LEVEL) 사이에서 정렬을 수행함

✔️ WHERE
- 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출(필터링)

가상 컬럼(Pseudo Column) 종류

• LEVEL
  - 루트 데이터이면 1, 그 하위 데이터이면 2
  - 리프(Leaf) 데이터까지 1씩 증가함

• CONNECT_BY_ISLEAF
  - 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0

• CONNECT_BY_ISCYCLE
  - 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0
  - 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터
  - CYCLE옵션을 사용했을 때만 사용가능

🪄순방향 전개

 SELECT LEVEL
      , LPAD(' ', 4 * (LEVEL-1)) || EMPNO 사원 
      , MGR 관리자
      , CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP
  START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR; 

💻 출력
LEVEL     EMP            MGR      ISLEAF
------    -------------- -------  ---------
1         7839                    0 
2            7566        7839     0
3               7788     7566     0 
4                  7876  7788     1 

A는 루트 데이터이기 때문에 레벨이 1
A의 하위 데이터인 B, C는 레벨이 2
C의 하위 데이터인 D, E는 레벨이 3
리프 데이터는 B, D, E이다.
관리자 → 사원 방향을 전개이기 때문에 순방향 전개임

🪄역방향 전개

-- 사원'D'로부터 자신의 상위관리자를 찾는 역방향 전개
SELECT LEVEL
      , LPAD(' ', 4 * (LEVEL-1)) || EMPNO사원
      , MGR 관리자
      , CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP 
  START WITH EMPNO = '7876' 
CONNECT BY PRIOR MGR = EMPNO; 

💻 출력
LEVEL     EMP            MGR      ISLEAF
------    -------------- -------  ---------
1         7876           7788     0 
2            7788        7566     0
3               7566     7839     0 
4                  7839           1 

역방향 전개이므로 하위 데이터에서 상위 데이터로 전개됨
D는 루트 데이터이기 때문에 레벨이 1
D의 상위 데이터인 C는 레벨이 2
C의 상위 데이터인 A는 레벨이 3
리프 데이터는 A임

* SYS_CONNECT_BY_PATH

: 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
: [표현법] SYS_CONNECT_BY_PATH(컬럼, 경로분리자)

* CONNECT_BY_ROOT

: 현재 전개할 데이터의 루트 데이터를 표시
: [표현법] CONNECT_BY_ROOT 컬럼

SELECT CONNECT_BY_ROOT(EMPNO) 루트사원
     , SYS_CONNECT_BY_PATH(EMPNO, '/') 경로
     , EMPNO 사원
     , MGR 관리자
  FROM EMP
 START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR; 

💻 출력
루트사원      경로                 사원       관리자
--------    -------------------  ------     ----------
    7839    /7839                7839
    7839    /7839/7566           7566       7839
    7839    /7839/7566/7788      7788       7566