계층형쿼리란 데이터를 조회할 때 상하관계를 표시하는 쿼리이다.
주요 문법
SELECT LPAD(dname, LEVEL*6, '*') 부서명
FROM dept2
CONNECT BY PRIOR dcode = pdept
START WITH dcode = 0001 ;
LEVEL 해당 데이터가 몇 번째 단계이냐를 의미하는 것이다.
CONNET BY PRIOR 절은 각 행들이 서로 어떻게 연결되어야 하는 지를 지정하는 부분이다.
SELECT LPAD(dname, LEVEL*6, '*') 부서명
FROM dept2
CONNECT BY dcode = PRIOR pdept
START WITH dcode = 0001 ;
* 특정 부서의 상위 부서를 찾아갈 때 아주 좋은 방법이다.
* CONNECT BY 절에는 서브 쿼리를 사용할 수 없다.
* START WITH 절은 검색을 시작할 조건을 적어준다.
수행 순서
1. START WITH 절에 시작 조건을 찾는다.
2. CONNECT BY 절에 연결조건을 찾는다.
3. WHERE 절의 조건을 검색한다.
대량의 데이터가 있을 경우 시간이 오래 걸릴 수 있으므로 START WITH, CONNECT BY, WHERE 절의 컬럼에는 반드시 인덱스가 설정되어 있어야 한다.
계층형 쿼리 연습문제 1:
emp2 테이블과 dept2 테이블을 사용하여 사원명과 부서와 직급을 합쳐서 출력하되 부서와 직급별로 계층형 쿼리를 사용하여 출력하세요.
단, 직급이 없는 경우 '사원' 으로 출력하세요.
정렬은 ORDER SIBLINGS BY 절을 이용한다.
계층형 쿼리 연습문제 2:
emp2 테이블에서 일지매 기술부장 아래에 속한 부하직원만 계층쿼리로 조회해서 출력하세요.
단, 직급이 없는 사람은 사원으로 표시하세요.
SELECT LPAD(e.name||' '||d.dname||' '||NVL(e.position, '사원'), LEVEL*18, '-') "이름과 직급",
SYS_CONNECT_BY_PATH(e.name, '-') 전체경로
FROM emp2 e, (
SELECT dname, dcode, pdept
FROM dept2
) d
WHERE e.deptno = d.dcode
CONNECT BY PRIOR e.empno = e.pempno
START WITH empno = '19966102' ;
전체 경로는 SYS_CONNECT_BY_PATH (컬럼명, 구분자) 로 표시한다.
계층형 쿼리 연습문제 3:
emp2 테이블에서 박지리 사원의 상사들을 계층 쿼리로 출력하세요.
SELECT LPAD(e.name||' '||d.dname||' '||NVL(e.position, '사원'), LEVEL*19, '-') "이름과 직급"
FROM emp2 e, (
SELECT dname, dcode, pdept
FROM dept2
) d
WHERE e.deptno = d.dcode
CONNECT BY e.empno = PRIOR e.pempno
START WITH empno = '20000334'
계층형 쿼리 연습문제 4:
emp2 테이블에서 사원명과 상사명을 함께 출력하세요.
SELECT name 사원명, PRIOR name "상사명"
FROM emp2
START WITH pempno IS NULL
CONNECT BY PRIOR empno = pempno ;
계층형 쿼리 연습문제 5
emp2 테이블과 dept2 테이블을 조회하여 사번과 사원명 , 부서, 직급 ,부하직원 수를 출력하세요.
SELECT e.empno 사번,
(e.name||' '||d.dname||' '||NVL(e.position, '사원')) "이름 부서 직급",
(
SELECT COUNT(*)
FROM emp2
START WITH empno = e.empno
CONNECT BY PRIOR empno = pempno
) -1 부하직원수
FROM emp2 e, (
SELECT dname, dcode
FROM dept2
) d
WHERE e.deptno = d.dcode
ORDER BY 3 DESC ;
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저