[Basic] 계층적 질의문

1 계층적 질의문 개념

• 간단한 계층적 구조.
  학과의 상위가 학부, 그 상위는 공대다. 정보미디어학부 아래에 컴퓨터공학과가 있고, 그래서 컴퓨터공학과의 college 100은 정보미디어학부의 deptno이다.
• 꼭대기가 Root, 가장 바닥이 leaf.

2 출력하기

(top down. deptno가 자식, college가 부모 관계)
SELECT [LEVEL], deptno, dname, college
FROM department
START WITH deptno = 10
CONNECT BY [NOCYCLE] PRIOR deptno = college;

(bottom up. college가 부모, deptno가 자식 관계)
SELECT [LEVEL], deptno, dname, college
FROM department
START WITH deptno = 101
CONNECT BY [NOCYCLE] PRIOR college = deptno;

• LEVEL은 계층별로 레벨 번호(depth)가 표시된다. 루트는 1, 하위로 갈수록 1씩 증가한다.
• START WITH은 출력의 시작점. top down 이면 시작하려는 상위, bottom up이면 시작하려는 하위. subquery 사용 가능하다.
• CONNECT BY PRIOR 여기서 계층 관계의 데이터 지정한다.
  • 현재 노드의 자식 = 다음 노드의 부모 로 지정하면 top down. 현재 노드의 자식과 다음 노드의 부모가 같은 번호이므로 아래로 내려간다.
  • 현재 노드의 부모 = 다음 노드의 자식 으로 지정하면 bottom up.
• NOCYCLE은 무한 루프를 방지한다.
• 실행순서
  • START WITH => CONNECT BY => WHERE

2-1 응용 출력1 - 시각적 표시

SELECT LEVEL, LPAD(dname, (LEVEL-1)*4, '-') 조직도
FROM department
START WITH dname = '공과대학'
CONNECT BY PRIOR deptno = college;

혹은
LPAD(' ', (LEVEL-1)*2)||dname 조직도

• 이런 식으로 하면 LEVEL이 클수록(depth가 깊을수록) 왼쪽에 들여쓰기가 된다. 1인 시작은 들여쓰기가 0

2-2 응용 출력2 - CONNECT_BY_ROOT

SELECT LPAD(ename, (LEVEL-1)*4, ' ') 사원명, empno 사번, 
	CONNECT_BY_ROOT empno 최상위사번, LEVEL
FROM emp
START WITH job = UPPER('president')
CONNECT BY PRIOR empno = mgr;

• CONNECT_BY_ROOT 는 해당 행의 ROOT를 표시

2-3 응용 출력3 - CONNECT_BY_ISLEAF

SELECT LPAD(ename, (LEVEL-1)*4, ' ') 사원명, empno 사번, 
	CONNECT_BY_ISLEAF Leav_TF, LEVEL
FROM emp
START WITH job = UPPER('president')
CONNECT BY PRIOR empno = mgr;

• CONNECT_BY_ISLEAF는 단말 노드(leaf node)인지. 1이면 단말, 0이면 그렇지 않다.

SELECT LPAD(ename, (LEVEL-1)*4, ' ') 사원명, empno 사번, LEVEL
FROM emp
WHERE CONNECT_BY_ISLEAF = 1
START WITH job = UPPER('president')
CONNECT BY PRIOR empno = mgr;

• 이런 식으로 이용하면 leaf node만 출력 가능

2-4 응용 출력4 - ORDER SIBLINGS BY

SELECT LPAD(ename, (LEVEL-1)*4, ' ') 사원명, empno 사번, LEVEL
FROM emp
START WITH job = UPPER('president')
CONNECT BY PRIOR empno = mgr
ORDER BY SIBLINGS BY ename;

• 계층적 구조에서 일반적인 order by로 마무리를 해버리면, 계층 구조를 무시한 채 order by 순서대로 출력된다.
  but ORDER BY SIBLINGS BY로 기준을 설정하면, 계층을 지키는 범위에서 그 기준대로 출력한다.

2-5 응용 출력5 - SYS_CONNECT_BY_PATH

SELECT LPAD(ename, (LEVEL-1)*4, ' ') 사원명, empno 사번, 
	SYS_CONNECT_BY_PATH(ename, '/') path
FROM emp
START WITH job = UPPER('president')
CONNECT BY PRIOR empno = mgr;

• 계층의 path를 '/'로 구분하며 표시한다. /KING/JONES/SCOTT 이렇게.