순환(RECURSIVE) 관계
부모와 자식의 관계처럼 계층적 구조의 자료를 하나의 테이블 내에 구조화하여 기술
ex) 카테고리 테이블이나 조직도 테이블


7369 사번 SMITH의 매니저는 7902 사번 FORD
→ 7902 FORD의 매니저는 7566 사번 JONES
 7566 사번 JONES의 매니저는 7839 KING
▶ 7369 < 7902 < 7566 < 7839

순환(RECURSIVE) 전개
테이블의 순환 관계를 이용하여 부모와 자식 관계의 데이터를 전개하는 것

SELECT LPAD('    ', 2*(LEVEL-1))||EMPNO EMPNO, 
             MGR, 
             ENAME, 
             DEPTNO 
  FROM EMP 
[WHERE EMPNO != '7844'] // 최종 결과에서 EMPNO가 7844인 데이터 제외 
  START WITH MGR IS NULL 
CONNECT BY [NOCYCLE] PRIOR EMPNO = MGR 
[ORDER SIBLINGS BY 컬럼명]; 

- START WITH : 계층 구조의 데이터를 읽어나가는데 있어 시작점을 지정, 서브쿼리 이용 가능
ex) START WITH MGR IN (SELECT ........)
- CONNECT BY : 다음에 읽을 자식 데이터를 지정, 전개를 수행하려는 두 개의 컬럼을 설정하는 조인 조건에 해당
ex) PRIOR 자식컬럼 = 부모컬럼 : 부모 → 자식 방향으로 내려가는 순방향 조회
    PRIOR 부모컬럼 = 자식컬럼 : 자식 → 부모 방향으로 올라가는 역방향 조회
- NOCYCLE : 동일한 데이터를 반복해서 읽는 CYCLE 형성 방지
- ORDER SIBLINGS BY : 형제 노드(동일한 LEVEL)의 데이터 사이에서 정렬 수행
※ 순환 전개에서 ORDER BY 절은 무쓸모

 

 

✔일반적으로 순환 전개에 일반 조인을 추가하면 해당 SQL은 악성 SQL로 변하므로 주의 요망!!

※ SYS_CONNECT_BY_PATH 함수와 함께 사용 가능 (부모로부터 상속받은 모든 데이터 추출)

SELECT LPAD('    ', 2*(LEVEL-1))||유저 유저, 
             SYS_CONNECT_BY_PATH(권한, ' ') 권한 
  FROM 권한 
CONNECT BY PRIOR 유저 = 부모유저 
  START WITH 유저 = 'A'; 

* 성능 보장을 위해 반드시 START WITH 절의 유저 컬럼과 CONNECT BY 절의 부모유저 컬럼에 인덱스가 있어야 함

by 위대한 정미나 2019. 8. 2. 11:37