회사 사원 중 SCOTT이란 이름을 가진 모든 사람의 정보를 검색하되 SCOTT의 상위 매니저 정보를 사용자에게 트리 구조 그대로 보여줘야 하는 요구사항이 있다고 하자. 트리그리드 UI에 순서대로 뿌려줘야하기 때문에 계층구조가 유지되어야 한다.
계층 쿼리에서 루트부터 노드까지의 경로를 표시하는 문자열을 빌드하는 SYS_CONNECT_BY_PATH 함수가 존재하긴 하나, 주어진 요건처럼 문자열이 아닌 오라클 계층형 구조로 된 데이터셋을 (특정 검색어가 포함된 row뿐만 아니라 해당 row의 부모정보를 모두) 각각의 row로 리턴해야 하는 경우 쿼리를 작성해보았다.
Oracle Live SQL을 이용해 데이터셋 환경 만들기
오라클 PC 다운로드 및 설치가 귀찮아 온라인 환경에서 코드편집과 실행을 가능하게 해주는 Oracle Live SQL을 이용했다. 계층형 데이터셋을 만드는 쿼리 샘플은 여기를 참고했다.
insert into emp
values(
9000, 'SCOTT2', 'SALESMAN', 7698,
to_date('20-2-1981','dd-mm-yyyy'),
1600, 300, 30
);SCOTT 외 SCOTT2라는 동명이인이 있다고 가정하고 쿼리 샘플에 더해 위처럼 데이터셋 한 줄만 더 추가해주었다.
전체 데이터셋 계층구조로 보기
SELECT rpad(' ', (level-1)*3) || ename as employee
, level
, sal
, job
FROM EMP
START WITH mgr is null
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno ASC;
;
EMPLOYEE LEVEL SAL JOB
------------------------------------------
KING 1 5000 PRESIDENT
JONES 2 2975 MANAGER
SCOTT 3 3000 ANALYST <-----
ADAMS 4 1100 CLERK
FORD 3 3000 ANALYST
SMITH 4 800 CLERK
BLAKE 2 2850 MANAGER
ALLEN 3 1600 SALESMAN
WARD 3 1250 SALESMAN
MARTIN 3 1250 SALESMAN
TURNER 3 1500 SALESMAN
JAMES 3 950 CLERK
SCOTT2 3 1600 SALESMAN <-----
CLARK 2 2450 MANAGER
MILLER 3 1300 CLERK위처럼 KING을 대표로 하는 전체 계층조직도를 얻을 수 있다. SCOTT이라는 이름을 가진 직원은 총 2명(<-----표시)이며, 검색결과는 요건처럼 해당 직원들뿐만 아니라 직원들의 상사들의 계층정보가 모두 나와야 한다.
검색데이터셋 계층구조로 보기
select tree1.*
from (
select
rpad(' ', (level-1)*3) || ename as employee
, empno
, level
, sal
, job
from emp e
start with e.mgr is null
connect by prior e.empno = e.mgr
ORDER SIBLINGS BY e.empno ASC
) tree1
inner join (
select distinct parent as empno
from (select connect_by_root (e.empno) as parent, e.empno as child
from emp e
where 1=1
and e.ename LIKE '%SCOTT%'
connect by prior e.empno = e.mgr)
) tree2 ON tree1.empno = tree2.empno;
위 쿼리가 완성된 최종쿼리이다. 자세히 뜯어보면 계층구조를 유지하기 위해 전체결과인 tree1을 먼저 조회하고 이너조인으로 검색결과인 tree2의 검색결과를 필터링하고 있음을 알 수 있다.
그럼 실질적인 검색결과인tree2를 살펴보자. 직관적인 이해를 위해 아래처럼 유사검색이 아닌 SCOTT 일치검색으로 쿼리를 해보았다.
select connect_by_root (e.empno) as parent, e.empno as child, e.ename
from emp e
where 1=1
and e.ename = 'SCOTT'
connect by prior e.empno = e.mgr;
PARENT CHILD ENAME
-------------------------
7788 7788 SCOTT
7566 7788 SCOTT
7839 7788 SCOTT
이전 포스팅에서 알아본 것처럼 START WITH 절이 생략되는 경우는 모든 ROW들을 루트노드로 간주하기 때문에 SCOTT의 경우 KING(EMPNO='7839'), JONES(EMPNO='7566'), 자기자신(EMPNO='7788')이 루트노드일때 총 3번 검색된다. connect_by_root구문을 사용하여 어떤 루트노드(쿼리에서 PARENT 컬럼) 검색 시 SCOTT 정보가 조회되었는지 확인 할 수 있다.
이렇게 필터링된 정보(SCOTT과 일치하는 row와 해당 row의 부모정보)를 tree1구조에 매핑하면 계층구조에 맞게 row를 조회할 수 있다.
EMPLOYEE EMPNO LEVEL SAL JOB
---------------------------------------------------
KING 7839 1 5000 PRESIDENT
JONES 7566 2 2975 MANAGER
SCOTT 7788 3 3000 ANALYST
BLAKE 7698 2 2850 MANAGER
SCOTT2 9000 3 1600 SALESMAN위와 같이 SCOTT과 SCOTT2이라는 이름의 직원 정보가 상위매니저 정보와 함께 조회될 수 있다.
