Oracle SQL) 계층형 쿼리와 셀프조인

 # 계층형 데이터

   - 동일 테이블에 계층적으로 상위 및 하위 데이터가 포함된 데이터

    ex) EMP 테이블의 사원번호 및 그 사원의 관리자번호

 

 # 셀프조인 

  - FROM 절에 동일 테이블을 2번이상 기술하는 조인 (재귀호출)

 

 # 순방향 전개 

   - 자식 노드로 전개

 # 역방향 전개

   - 부모 노드로 전개

---

 # 리프데이터

   - 자식노드가 존재하지 않는 데이터

 # 루트데이터

   - 계층형데이터에서 최상위에 위치하는 데이터 (순방향이냐 역방향이냐에 따라 루트가 달라짐)

 # 사이클

   - 데이터 전개 중 이미 나타났던 동일한 데이터가 다시 나타날때, 이것을 사이클이 발생했다고 지칭하며, 런타임 오류를 발생시킨다.

 

 # 계층형 쿼리(질의) (Hierarchical Query)

  # Oracle

# Syntax

SELECT ... FROM ... WHERE ...
       START WITH 조건 AND 조건 ...
       CONNECT BY [NOCYCLE] 조건 AND 조건 ...
      [ORDER SIBLINGS BY 칼럼1, 칼럼2, ...]
      
+) [NOCYCLE] 키워드는 사이클이 발생한 이후의 데이터를 전개하지않을때 사용한다
+) [ORDER SIBLINGS BY 칼럼1, 칼럼2, ...] 키워드는 형제노드의 정렬기준을 정할때 사용한다
+)  WHERE절은 모든 전개가 실행된 뒤 실행된다.
      
# 계층형 쿼리에서 사용되는 가상칼럼
1. LEVEL  -  루트데이터이면 1, 하위데이터이면 2, 리프데이터까지 1씩 증가한다.
2. CONNECT_BY_ISLEAF  -  전개과정에서 해당데이터가 리프데이터이면 1, 아니면 0
3. CONNECT_BY_ISCYCLE -  사이클발생 지점에 1을 리턴하여 사이클 발생지점을 체크할 수 있다.

# 계층형 쿼리에서 사용되는 함수
1. SYS_CONNECT_BY_PATH(col, str)      - 데이터전개 경로표시
2. CONNECT_BY_ROOT(col)               - 현재 전개할 데이터의 루트데이터 표시

# 사용예시

 SQL> SELECT LEVEL AS LV 
             ,EMPNO
             ,MGR
             ,CONNECT_BY_ISLEAF AS ISLEAF
      FROM EMP
      START WITH MGR IS NULL                  #(MGR == NULL 인 곳이 스타팅포인트)
      CONNECT BY MGR = PRIOR EMPNO;           #(추출될 데이터의 MGR == 이전노드의 EMPNO)

# 실행결과
LV       EMPNO         MGR            ISLEAF
_____________________________________________
1           001                                     0
2           002              001                  0 (# 리프노드가 아니다. 흘러온 방향대로 002의 자식노드를 검색한다)          
3           003              002                  1 (# EMPNO 003은 자식노드가없다 -> 003의 부모노드인 002로 돌아가서 다시 검색한다)
4           004              002                  1 (# EMPNO 004은 자식노드가없다 -> 004의 부모노드인 002로 돌아가서 다시 검색한다)
5           313              001                  1 (# EMPNO 313은 자식노드가없다 -> 313의 부모노드인 001로 돌아가서 다시 검색한다)

001 -> 002 -> 003 -> 002 -> 004 -> 002 -> 00
(
 # EMPNO 313 의 MGR 001이 나오게 된 배경
   ( EMPNO 004은 자식노드가없다 -> 004의 부모노드인 002로 돌아가서 다시 검색한다 )
   의 세부과정
   -> 004를 MGR로 가지는 데이터를 찾는다(=004의 자식노드를 찾는다)
   -> 발견하지 못했다 (004는 리프노드이다)
   -> 스택에서 004를 삭제한다 
   -> 스택에서 데이터를 꺼낸다 (002) -> 002을 MGR로 가지는 노드 더이상 없음 -> 002 삭제
   -> 스택에서 데이터를 꺼낸다 (001) -> 001을 MGR로 가지는 노드를 찾는다 -> EMP 313
)

# EMPNO 313 의 MGR 001이 나오게 된 배경을 보여주는 예시 쿼리문
SQL>  SELECT LEVEL AS LV 
             ,EMPNO
             ,MGR
             ,CONNECT_BY_ISLEAF AS ISLEAF
             ,SYS_CONNECT_BY_PATH(EMPNO,'->') AS PATH
      FROM EMP
      START WITH MGR IS NULL
      CONNECT BY MGR = PRIOR EMPNO;
      
# 실행결과
LV       EMPNO         MGR            ISLEAF                  PATH
___________________________________________________________________________
1           001                                     0             ->001
2           002              001                  0             ->001->002
3           003              002                  1             ->001->002->003 
4           004              002                  1             ->001->002->004
5           313              001                  0             ->001->313 

---