[노드 제거]

CONNECT BY 절이나 WHERE 절에 조건을 기술하면 조건을 만족하지 않는 노드를 제거할 수 있다.

 

아래 쿼리는 CONNECT BY 절에 EMPNO <> 7698 조건을 기술했다.

CONNECT BY 절에 조건을 기술하면 조건을 만족하지 않는 노드와 해당 노드의 모든 하위 노드가 제거된다.

계층 전개 시점에 노드가 제거되기 때문에 하위 노드까지 제거되는 것이다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO

AND EMPNO <> 7698;

 

아래 쿼리는 WHERE 절에 EMPNO <> 7698 조건을 기술했다.

WHERE 절에 조건을 기술하면 조건을 만족하지 않는 노드만 제거된다.

CONNECT BY 절이 수행된 후 WHERE 절이 수행되기 때문이다.

 

SELECT LEVE AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP

WHERE EMPNO <> 7698

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

[다중 루트 노드]

계층 쿼리 절은 1개 이상의 루트 노드를 가질 수 있다.

 

아래 쿼리는 JOB이 MANAGER인 행으로 루트 노드를 생성하고, 순방향으로 계층을 전개한다.

JONES, BLAKE, CLARK이 루트 노드로 생성된다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP

START WITH JOB = 'MANAGER'

CONNECT BY MGR = PRIOR EMPNO;

 

아래 쿼리는 20번 부서에서 부하가 없는 사원으로 루트 노드를 생성하고, 역방향으로 계층을 전개한다.

SMITH와 ADAMS가 루트 노드로 생성된다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP A

START WITH DEPTNO = 20

AND NOT EXISTS (SELECT 1 FROM EMP X WHERE X.MGR = A.EMPNO)

CONNECT BY EMPNO = PRIOR MGR;

 

[다중 속성 순환 관계]

순환 관계는 1개 이상의 속성을 관계 속성으로 가질 수 있다.

 

예제를 위해 아래와 같이 테이블을 생성

DROP TABLE EMP_C PURGE;

 

CREATE TABLE EMP_C AS

SELECT 1 AS COMPNO, A.*, 1 AS PCOMPNO FROM EMP A UNION ALL

SELECT 2 AS COMPNO, A.*, 2 AS PCOMPNO FROM EMP A;

 

아래는 EMP_C 테이블을 조회한 결과다. EMP_C 테이블은 여러 회사의 사원 정보를 통합 관리하는 테이블이다.

고유 식별자가 COMPNO(회사번호) + EMPNO이므로 순환 관계를 상속받는 외래 식별자는 PCOMPNO (부모회사번호) + MGR다.

 

SELECT COMPNO, EMPNO, ENAME, MGR, PCOMPNO FROM EMP_C;

 

아래 쿼리는 COMPNO가 1인 회사의 사원 정보를 조회한다.

CONNECT Y 절에 순환 관계 속성을 모두 기술해야 의도한 결과를 얻을 수 있다.

 

SELECT LEVEL AS LV, COMPNO, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, MGR, PCOMPNO

FROM EMP_C

START WITH COMPNO = 1

AND MGR IS NULL

CONNECT BY PCOMPNO = PRIOR COMPNO

AND MGR = PRIOR EMPNO;

 

아래 쿼리는 의도하지 않은 결과가 반환된다.

CONNECT BY 절에 PCOMPNO = PRIOR COMPNO 조건을 기술하지 않으면 COMPNO가 1이 아닌 회사의 사원으로 계층이 전개된다.

 

SELECT LEVEL AS LV, COMPNO, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, MGR, PCOMPNO

FROM EMP_C

START WITH COMPNO = 1

AND MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

예제를 위해 아래와 같이 테이블을 생성하자.

EMP_H 테이블은 순환 관계를 가지는 EMP 테이블의 월별 이력 테이블이다.

 

DROP TABLE EMP_H PURGE;

 

CRAETE TABLE EMP_H AS

SELECT '205001' AS YM, A.* FROM EMP A UNION ALL

SELECT '205002' AS YM, A.* FROM EMP A;

 

아래는 EMP_H 테이블을 조회한 결과다.

EMP_H 테이블은 다중 속성 순환 관계를 가지지는 않는다.

(순환 관계를 가지는 테이블의 월별 이력 테이블에 대한 관계는 모호한 면이 있다. 굳이 따지면 식별자를 부분 상속했다고 볼 수 있다. EMP_H 엔티티의 경우 EMPNO 속성만 MGR 속성으로 상속된 것이다.)

 

SELECT YM, EMPNO, ENAME, MGR FROM EMP_H;

 

아래 쿼리는 2050년 1월의 사원 이력을 조회한다.

동일한 이력 시점의 계층을 전개하기 위해서는 CONNECT BY 절에 YM = '205001' 조건을 기술해야 한다.

 

SELECT LEVEL AS LV, YM, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP_H

START WITH YM = '205001'

AND MGR IS NULL

CONNECT BY YM = '205001'

AND MGR = PRIOR EMPNO;

 

월별 이력에 대한 계층 쿼리는 아래와 같이 인라인 뷰를 사용하는 편이 명시적이다.

 

SELECT LEVEL AS LV, YM, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM (SELECT * FROM EMP_H WHERE YM = '205001')

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

[계층 쿼리와 조인]

계층 쿼리는 세 가지 방식으로 조인을 수행할 수 있다.

 

아래 쿼리는 계층을 전개한 후 조인을 수행한다. 인라인 뷰를 사용한 조인과 동일하다.

SELECT A.LV, A.EMPNO, A.ENAME, B.DNAME

FROM (SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, DEPTNO, ROWNUM AS RN

FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO) A

, DEPT B

WHERE B.DEPTNO = A.DEPTNO

ORDER BY A.RN;

 

아래 쿼리는 조인을 수행한 후 계층을 전개한다.

B.LOC = 'NEW YORK' 조건처럼 계층을 전개할 대상을 먼저 정의해야 할 때 사용할 수 있다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, DEPTNO, DNAME

FROM (SELECT A.*, B.DNAME

FROM EMP A, DEPT B

WHERE B.DEPTNO = A.DEPTNO

AND B.LOC = 'NEW YORK')

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

WHERE 절에 조인 조건을 기술하면 조인을 수행한 후 계층을 전개한다.

위 쿼리처럼 인라인 뷰를 사용하는 편이 명시적이다.

 

SELECT LEVEL AS LV, A.EMPNO, LPAD (' ', LEVEL - 1, ' ') || A.ENAME AS ENAME

, A.DEPTNO, B.DNAME

FROM EMP A, DEPT B

WHERE B.DEPTNO = A.DEPTNO

START WITH A.MGR IS NULL

CONNECT BY A.MGR = PRIOR A.DEPTNO;

 

ANSI 조인 문법도 조인을 수행한 후 계층을 전개한다.

 

SELECT LEVEL AS LV, A.EMPNO, LPAD (' ', LEVEL - 1, ' ') || A.ENAME AS ENAME

, A.DEPTNO, B.DNAME

FROM EMP A

JOIN DEPT B

ON B.DEPTNO = A.DEPTNO

START WITH A.MGR IS NULL

CONNECT BY A.MGR = PRIOR A.EMPNO;

 

아래 쿼리는 계층 전개 시점에 조인을 수행한다.

START WITH 절과 CONNECT BY 절에 조인 조건을 기술해야 한다.

B.LOC = 'DALLAS' 조건처럼 계층 전개 중 노드를 제한해야 할 때 사용할 수 있다.

 

SELECT A.*, B.DNAME

FROM EMP A, DEPT B

START WITH A.MGR IS NULL

AND B.DEPTNO = A.DEPTNO

CONNECT BY A.MGR = PRIOR A.EMPNO

AND B.DEPTNO = A.DEPTNO

AND B.LOC = 'DALLAS';

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

+ Recent posts