hierarchical query
조직구성도(Organization chart)와 같은 계층구조(트리구조)를 갖는 데이터구조가 있고,
이와 같은 구조를 가진 데이터에는 connect by를 사용하여 질의할 수 있다.
즉, hierarchical data에 query한다고 한다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
MANAER가 3명(JONES, BLAKE, CLARK)이 있음을 알 수 있다.
SQL> select * from emp where job='MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
그럼, 3명중 JONES를 제일 우두머리로 하여 직속 부하직원들을 알아보자.
위의 EMP테이블의 MGR컬럼은 그 직원의 바로 윗고참을 표시한다.
JONES의 EMPNO은 7566이고 MGR컬럼값으로 7566을 가진 직원은 SCOTT,FORD가 있다.
따라서, SCOTT, FORD는 JONES의 바로 밑에 있는 놈임을 알 수 있다.
조직은 제일 윗대가리뿐 아니라 중간계층을 거쳐 말단사원까지 있음을 상기하자.
그럼, SCOTT, FORD가 밑에 부하직원을 가지는지 알아봐야 할 것이다.
각각 EMPNO이 7788, 7902이다.
MGR컬럼값이 7788인 놈은 ADAMS이고, 7902인 놈은 SMITH이다.
즉, SCOTT의 부하직원은 ADAMS이고, FORD의 부하직원은 SMITH이다.
ADAMS와 SMITH는 EMPNO를 각각 7876, 7369를 가지는데, 이에 대한 MGR컬럼값을 가진 놈은
존재하지 않으므로 얘들이 말단 사원임을 알 수 있고, 여기까지 탐색하면 된다.
보기 좋게 트리형태로 나타내면 다음과 같다.
level 1 JONES
/
level 2 SCOTT FORD
/
level 3 ADAMS SMITH
이런 관계를 긁어내는 질의를 만들려고 하니 좀 복잡하다.
오라클에서는 start with, connect by로 이런 계층적인 데이터에 질의할 수 있도록 하고 있다.
'start with 조건'에서는 ROOT노드(JONES)를 지정하고
'connect by 조건'에서는 부모-자식간의 관계를 지정한다.
prior 오퍼레이터는 parent row를 가리키는 뜻이다.
실제 질의를 통해 알아보면 다음과 같다. level컬럼은 pseudo컬럼으로 트리의 레벨을 표시한다.
SQL> select empno, ename, mgr, level
2 from emp
3 start with ename='JONES'
4 connect by prior empno=mgr;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3
같은 방식으로 BLAKE의 부하직원들을 보자.
SQL> select empno, ename, mgr, level
2 from emp
3 start with ename='BLAKE'
4 connect by prior empno=mgr;
EMPNO ENAME MGR LEVEL
---------- ---------- ---------- ----------
7698 BLAKE 7839 1
7499 ALLEN 7698 2
7521 WARD 7698 2
7654 MARTIN 7698 2
7844 TURNER 7698 2
7900 JAMES 7698 2
6 개의 행이 선택되었습니다.
empno이 7500보다 큰 놈만 선택하려면 where조건을 사용한다.
SQL> select level, empno, ename, mgr
2 from emp where empno > 7500
3 start with ename='BLAKE'
4 connect by prior empno=mgr;
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
1 7698 BLAKE 7839
2 7521 WARD 7698
2 7654 MARTIN 7698
2 7844 TURNER 7698
2 7900 JAMES 7698
테이블의 모든 데이터에 대해 계층구조를 보자.
SQL> select lpad('*', level, '*' ) || ename ename
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
ENAME
-------------------------------------------------
*KING
**JONES
***SCOTT
****ADAMS
***FORD
****SMITH
**BLAKE
***ALLEN
***WARD
***MARTIN
***TURNER
***JAMES
**CLARK
***MILLER
14 개의 행이 선택되었습니다.
|
This article comes from dbakorea.pe.kr (Leave this line as is)
|
