계층형 쿼리(Hierachical select)

connect by
start with

자료의 구조가 계층적으로 이루어진 경우
상위자료에서부터 하위자료로의 자료 전개 및
하위자료에서 상위자료로의 역 전개를 위하여 사용되는 SELECT 형식이다.


예)
오라클에서 제공하는 샘플테이블이 있는 경우 사용 가능한 예제


-- Top-down 방식

select level, lpad(empno, level*4, '_') as empno1, e.*
from emp e
start with empno = 7839
connect by PRIOR empno = mgr;

LEVEL	EMPNO1	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7839	7839	KING	PRESIDENT		81/11/17	5000		10
2	____7566	7566	JONES	MANAGER	7839	81/04/02	2975		20
3	________7788	7788	SCOTT	ANALYST	7566	82/12/09	3000		20
4	____________7876	7876	ADAMS	CLERK	7788	83/01/12	1100		20
3	________7902	7902	FORD	ANALYST	7566	81/12/03	3000		20
4	____________7369	7369	SMITH	CLERK	7902	80/12/17	800		20
2	____7698	7698	BLAKE	MANAGER	7839	81/05/01	2850		30
3	________7499	7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
3	________7521	7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
3	________7654	7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
3	________7844	7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
3	________7900	7900	JAMES	CLERK	7698	81/12/03	950		30
2	____7782	7782	CLARK	MANAGER	7839	81/06/09	2450		10
3	________7934	7934	MILLER	CLERK	7782	82/01/23	1300		10

-- Bottom-up방식

select level, lpad(empno, level*4, '_') as empno1, e.*
from emp e
start with empno = 7369
connect by empno = PRIOR mgr;

LEVEL	EMPNO1	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	7369	SMITH	CLERK	7902	80/12/17	800		20
2	____7902	7902	FORD	ANALYST	7566	81/12/03	3000		20
3	________7566	7566	JONES	MANAGER	7839	81/04/02	2975		20
4	____________7839	7839	KING	PRESIDENT		81/11/17	5000		10

-- SIBLING 키워드(계층형 구조의 order by,  같은 층 끼리만 비교함)

select level, lpad(empno, level*4, '_') as empno1, ename, sal, job, sys_connect_by_path(ename, '/')
from emp e
start with empno = 7839
connect by PRIOR empno = mgr
order SIBLINGS by sal desc;

LEVEL	EMPNO1	ENAME	SAL	JOB	SYS_CONNECT_BY_PATH(ENAME,'/')
1	7839	KING	5000	PRESIDENT	/KING
2	____7566	JONES	2975	MANAGER	/KING/JONES
3	________7788	SCOTT	3000	ANALYST	/KING/JONES/SCOTT
4	____________7876	ADAMS	1100	CLERK	/KING/JONES/SCOTT/ADAMS
3	________7902	FORD	3000	ANALYST	/KING/JONES/FORD
4	____________7369	SMITH	800	CLERK	/KING/JONES/FORD/SMITH
2	____7698	BLAKE	2850	MANAGER	/KING/BLAKE
3	________7499	ALLEN	1600	SALESMAN	/KING/BLAKE/ALLEN
3	________7844	TURNER	1500	SALESMAN	/KING/BLAKE/TURNER
3	________7521	WARD	1250	SALESMAN	/KING/BLAKE/WARD
3	________7654	MARTIN	1250	SALESMAN	/KING/BLAKE/MARTIN
3	________7900	JAMES	950	CLERK	/KING/BLAKE/JAMES
2	____7782	CLARK	2450	MANAGER	/KING/CLARK
3	________7934	MILLER	1300	CLERK	/KING/CLARK/MILLER

계층형 쿼리 관련 함수

-- sys_connect_by_path() 
    : 계층형 구조의 전체 경로를 나타냄, 
     두번째 인수인 구분자는 열값의 일부의 문자로는 사용불가.
     아래의 쿼리를 예로 들면 ename중에 /가 들어가는 이름이 있으면 /는 사용 불가

select level, lpad(empno, level*4, '_') as empno1, ename, sal, job, sys_connect_by_path(ename, '/')
from emp e
start with empno = 7839
connect by PRIOR empno = mgr;

LEVEL	EMPNO1	ENAME	SAL	JOB	SYS_CONNECT_BY_PATH(ENAME,'/')
1	7839	KING	5000	PRESIDENT	/KING
2	____7566	JONES	2975	MANAGER	/KING/JONES
3	________7788	SCOTT	3000	ANALYST	/KING/JONES/SCOTT
4	____________7876	ADAMS	1100	CLERK	/KING/JONES/SCOTT/ADAMS
3	________7902	FORD	3000	ANALYST	/KING/JONES/FORD
4	____________7369	SMITH	800	CLERK	/KING/JONES/FORD/SMITH
2	____7698	BLAKE	2850	MANAGER	/KING/BLAKE
3	________7499	ALLEN	1600	SALESMAN	/KING/BLAKE/ALLEN
3	________7521	WARD	1250	SALESMAN	/KING/BLAKE/WARD
3	________7654	MARTIN	1250	SALESMAN	/KING/BLAKE/MARTIN
3	________7844	TURNER	1500	SALESMAN	/KING/BLAKE/TURNER
3	________7900	JAMES	950	CLERK	/KING/BLAKE/JAMES
2	____7782	CLARK	2450	MANAGER	/KING/CLARK
3	________7934	MILLER	1300	CLERK	/KING/CLARK/MILLER

-- connect_by_root() : 상관관계 쿼리에서 level이 0인 최상위 로우의 정보를 얻어올 수 있다.

SELECT LPAD('   ', 4*(LEVEL-1)) || ename ename, empno, 
CONNECT_BY_ROOT  empno "Root empno", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;

ENAME	EMPNO	Root empno	LEVEL
KING	7839	7839	1
JONES	7566	7839	2
SCOTT	7788	7839	3
ADAMS	7876	7839	4
FORD	7902	7839	3
SMITH	7369	7839	4
BLAKE	7698	7839	2
ALLEN	7499	7839	3
WARD	7521	7839	3
MARTIN	7654	7839	3
TURNER	7844	7839	3
JAMES	7900	7839	3
CLARK	7782	7839	2
MILLER	7934	7839	3

-- connect_by_isleaf() : 계층형 구조에서 로우의 최하위 레벨 여부를 반환한다.

SELECT LPAD('', 4*(LEVEL-1)) || ename ename, empno, 
CONNECT_BY_ISLEAF "leaf", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE  PRIOR empno=mgr;

ENAME	EMPNO	leaf	LEVEL
KING	7839	0	1
JONES	7566	0	2
SCOTT	7788	0	3
ADAMS	7876	1	4
FORD	7902	0	3
SMITH	7369	1	4
BLAKE	7698	0	2
ALLEN	7499	1	3
WARD	7521	1	3
MARTIN	7654	1	3
TURNER	7844	1	3
JAMES	7900	1	3
CLARK	7782	0	2
MILLER	7934	1	3

참조 : http://www.oracleclub.com/lecture/1903