-- 고급주제
-- 노드제거 : 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
LEVEL 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 * FROM EMP;
SELECT
LEVEL AS LV
,EMPNO
,LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
, CONNECT_BY_ISLEAF AS LF
FROM EMP
START WITH JOB = 'MANAGER'
CONNECT BY MGR = PRIOR EMPNO;
-- 아래 쿼리는 201번 부서에서 부하가 없는 사원으로 루트 노드를 생성하고, 역방향으로 계층을 전개한다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
FROM EMP A
START WITH A.DEPTNO = 20 AND NOT EXISTS(SELECT 'X' FROM EMP X WHERE X.MGR = A.EMPNO)
CONNECT BY A.EMPNO = PRIOR A.MGR;
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
, DEPTNO
, CONNECT_BY_ISLEAF AS LF
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
-- 다중 속성 순환 관계
-- 순환 관계는 1개 이상의 속성을 관계 속성으로 가질수 있다.
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
);
SELECT * FROM EMP_C;
-- EMP_C 테이블은 여러 회사의 사원 정보를 통합 관리하는 테이블이다.
-- 고유식별자가 compno(회사번호) + empno 이므로 순환 관계를 상속 받는 외래 식별자는 pcompno + mgr이다.
-- 아래 쿼리는 compno가 1인 회사의 사원 정보를 조회한다. CONNECT BY 절에 순환 관계 속성을 모두 기술해야 의도한 결과를 얻을 수 있다.
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 = PROIR 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 테이블의 월별 이력 테이블이다.
-- EMP_H 테이블은 다중 속성 순환 관계를 가지지는 않는다.
-- * 순환 관계를 가지는 테이블의 월별 이력 테이블에 대한 관계는 모호한 면이 있다.
-- * 굳이 따지자면 식별자를 부분 상속 했다고 불 수 있다.
-- * EMP_H 엔터티의 경우 EMPNO 속성만 MGR 속성으로 상속된 것이다.
CREATE TABLE EMP_H AS
SELECT '205001' AS YM, A.* FROM EMP A
UNION ALL
SELECT '205002' AS YM, A.* FROM EMP A;
SELECT * 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;
-- 계층 쿼리와 조인
-- 계층 쿼리는 세가지 방식으로 조인을 실시 할 수 있다.
-- 1. 계층을 전개한 후 조인을 수행 ( 인라인 뷰를 이용한 조인과 동일하다)
-- FROM 절에서 인라인뷰를 사용하여 ( 계층 쿼리를 전개한 후 ) 조인을 수행한다.
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;
-- 2. 조인을 수행한 후 계층을 전개 한다. B.LOC = 'NEW YORK' 조건처럼 계층을 전개할 대상을 먼저 정의해야 사용할 수 있다.
-- FROM 절에서 JOIN 을 수행 한후 해당 결과를 가지고 계층 쿼리를 수행한다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD( ' ', LEVEL -1, ' ') || ENAME AS ENAME
, DEPTNO
, DNAME
FROM
(
SELECT
A.*
, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.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, ' ') || 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.EMPNO;
-- ANSI 조인 문법도 조인을 수행 한 후 계층을 전개한다
SELECT
LEVEL AS LV
, A.EMPNO
, LPAD(' ', LEVEL -1, ' ') || 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
LEVEL AS LV
, A.EMPNO
, LPAD(' ', LEVEL -1, ' ') || A.ENAME AS ENAME
, A.DEPTNO
, B.DNAME
FROM EMP A, DEPT B
START WITH A.MGR IS NULL AND B.DEPTNO = A.DEPTNO
CONNECT BY A.MGR = A.EMPNO AND B.DEPTNO = A.DEPTNO AND B.LOC = 'DALLAS';
-- 활용 예제
-- 순번 생성 : 계층 쿼리를 사용하면 순번을 가진 테이블을 생성 할 수 있다. 행 복제시 해당 기법을 활용 할 수 있다.
-- 아래 쿼리는 1 ~ 100까지의 순번을 반환한다 START WITH 절을 생략했기 때문에 DUAL 테이블의 전체 행이 루트 노드로 생성된다.
-- DUAL 테이블이 1행이므로 루트 노드가 1행으로 생성되고, 1행과 1행을 카티션 곱한 결과는 1행이므로 LEVEL <= 100 조건을 만족할때까지 1행씩 LEVEL이 증가한다.
SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 100;
-- XMLTABLE 함수를 사용해도 동일한 결과를 얻을 수 있다.
SELECT ROWNUM AS RN FROM XMLTABLE(' 1 to 100 ');
-- 변경이력 : 계층 쿼리를 응용하면 값의 변경이력을 확인 할 수 있다.
DROP TABLE T1 PURGE;
CREATE TABLE T1 (
YM VARCHAR2(6) -- 연월
, BF VARCHAR(4) -- 변경전 코드
, AF VARCHAR(4) -- 변경후 코드
);
INSERT INTO T1 VALUES('205001', 'A', 'B');
INSERT INTO T1 VALUES('205001', 'I', 'J');
INSERT INTO T1 VALUES('205001', 'X', 'Y');
INSERT INTO T1 VALUES('205004', 'B', 'C');
INSERT INTO T1 VALUES('205004', 'J', 'K');
INSERT INTO T1 VALUES('205007', 'C', 'D');
COMMIT;
-- A 코드는 2050/01에 B코드로, B코드는 2050/04에 C코드로, C코드는 2050/07에 D코드로 변경되었다.
-- 아래 쿼리는 코드의 최종 변경 코드를 조회한다. START WITH절이 생략되어 전체 행이 루트 노드로 생성된다.
-- CONNECT_BY_ROOT 연산자로 루트 노드를 조회하고, CONNECT_BY_ISLEAF 슈도 칼럼으로 리프노드만 조회했다
SELECT
BF
, AF
, YM
, LF
, LL
FROM
(
SELECT
YM
, CONNECT_BY_ROOT BF AS BF
, AF
, CONNECT_BY_ISLEAF AS LF
, SYS_CONNECT_BY_PATH(AF,',') AS LL
FROM T1
CONNECT BY BF = PRIOR AF
)
WHERE LF = 1;
-- 아래 쿼리는 최초 코드의 변경 정보를 조회한다.
-- 최초 코드로 루트 노드를 생성하기 위해 START WITH 절에서 변경전 코드가 존재하지 않는 행을 조회한 후
-- 순방향으로 계층을 전개 했다.
SELECT
BF, CD, YM, CN
FROM
(
SELECT
CONNECT_BY_ROOT BF AS BF
, SUBSTR(SYS_CONNECT_BY_PATH(AF, ','),2) AS CD
, SUBSTR(SYS_CONNECT_BY_PATH(YM, ','),2) AS YM
, LEVEL AS CN
, CONNECT_BY_ISLEAF AS LF
FROM T1 A
START WITH NOT EXISTS (SELECT 'X' FROM T1 X WHERE X.AF = A.BF)
CONNECT BY BF = PRIOR AF
)
WHERE LF = 1
ORDER BY 1;
-- 아래 쿼리는 최종 코드의 변경 정보를 조회한다. 최종 코드로 루트 노드를 생성하기 위해
-- START WITH 절에서 변경 후 코드가 존재하지 않는 행을 조회한 후 계층을 전개했다.
SELECT
AF, CD, YM, CN
FROM
(
SELECT
CONNECT_BY_ROOT AF AS AF
, SUBSTR(SYS_CONNECT_BY_PATH(BF, ','),2) AS CD
, SUBSTR(SYS_CONNECT_BY_PATH(YM, ','),2) AS YM
, LEVEL AS CN
, CONNECT_BY_ISLEAF AS LF
FROM
T1 A
START WITH NOT EXISTS(SELECT 1 FROM T1 X WHERE X.BF = A.AF) -- X가 항상 현재를 기준으로 A가 미래 또는 과거를 의미하는거 같음
CONNECT BY AF = PRIOR BF
)
WHERE LF = 1
ORDER BY 1;
-- 생성순서 : 계층 쿼리를 사용하면 순차적으로 계산되는 계정의 생성 순서를 결정 할 수 있다. -- 무슨말인지 잘모르겠음....
-- 예제를 위한 테이블을 생성하자
DROP TABLE T1 PURGE;
CREATE TABLE T1(
CD VARCHAR2(1) -- 계정
, C1 VARCHAR2(1) -- 계산계정1
, C2 VARCHAR2(1) -- 계산계정1
, C3 VARCHAR2(1) -- 계산계정1
, C4 VARCHAR2(1) -- 계산계정1
);
INSERT INTO T1 VALUES('A', 'B', 'C', 'D', 'E');
INSERT INTO T1 VALUES('B', 'F', 'G', 'H', NULL);
INSERT INTO T1 VALUES('C', 'I', 'J', NULL, NULL);
INSERT INTO T1 VALUES('D', 'K', NULL, NULL, NULL);
INSERT INTO T1 VALUES('E', 'B', 'C', NULL, NULL);
INSERT INTO T1 VALUES('F', 'C', 'D', NULL, NULL);
COMMIT;
-- 누적연산 : 재귀 서브 쿼리 팩토링을 사용하면 상위 노드의 값을 누적 연산할 수 있다.
-- 아래 쿼리는 상위 노드의 sal을 하위 노드로 누적 집계한다.
WITH W1(EMPNO, ENAME, MGR, SAL, LV, C1)
AS (
SELECT
EMPNO
, ENAME
, MGR
, SAL
, 1 AS LV
, SAL AS C1
FROM EMP
WHERE MGR IS NULL
UNION ALL
SELECT
C.EMPNO
, C.ENAME
, C.MGR
, C.SAL
, P.LV + 1 AS LV
, P.C1 + C.SAL
FROM W1 P, EMP C
WHERE C.MGR = P.EMPNO
)
SEARCH DEPTH FIRST BY EMPNO SET SO
SELECT LV, EMPNO, LPAD(' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SAL, C1 FROM W1 ORDER BY SO;
-- 계층 쿼리절은 부모 노드의 값만 참조할 수 있기 때문에 누적 연산이 불가능 하다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD( ' ', LEVEL - 1, ' ') || ENAME AS ENAME
, MGR
, SAL
, SAL + PRIOR SAL AS C1
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;