-- 계층 쿼리
-- 계층 쿼리를 사용하면 순환 관계를 가진 데이터를 조회 할 수 있다.
-- 순환 관계를 가진 데이터는 계층 구조로 표현할 수 있다.
-- 부모 노드 : 현재 노드의 직전 상위 노드
-- 자식 노드 : 현재 노드의 직후 하위 노드
-- * 하나의 자식 노드는 하나의 부모 노드만 가질 수 있다.
-- 노드는 부모노드와 자식노드의 존재 여부에 따라
-- 루트노드, 브랜치 노드, 리프노드 등으로 구분 된다
-- 루트노드 : 부모 노드가 존재하지 않는 노드
-- 브랜치 노드 : 부모 노드와 자식 노드가 존재하는 노드
-- 리프 노드 : 자식 노드가 존재하지 않는 노드
-- 순환 관계는 깊이에 따라 레벨이 부여 된다. 루트 노드의 레벨은 1이고, 계층이 전재 될수록 레벨이 1씩 증가한다.
-- emp 테이블의 empno 열과 mgr 열은 순환관계를 가진다. ( emp 테이블의 루트 노드는 KING으로 부모노드가 없으므로 mgr열이 널이다)
select empno, ename, mgr from emp;
--순환 관계를 가진 테이블은 셀프 조인을 통해 부모 노드나 자식 노드를 조회 할 수 있다.
SELECT B.EMPNO, B.ENAME, B.MGR FROM EMP A, EMP B WHERE A.ENAME = 'JONES' AND B.MGR = A.EMPNO;
-- 아래쿼리는 JONES의 자식노드의 자식 노드를 조회한다. MGR이 JONES의 자식노드인 SCOTT과 FORD의 EMPNO인 행을 조회하면된다.
-- A는 최고 상위 부모의 테이블, B는 A의 자식 테이블, C는 B의 자식 테이블 값을 갖는다.
SELECT C.EMPNO, C.ENAME, C.MGR FROM EMP A, EMP B, EMP C WHERE A.ENAME = 'JONES' AND B.MGR = A.EMPNO AND C.MGR = B.EMPNO;
-- SMITH의 부모 노드를 조회한다.
-- A는 현재 테이블, B는 부모 테이블
SELECT B.EMPNO, B.ENAME, B.MGR FROM EMP A, EMP B WHERE A.ENAME = 'SMITH' AND A.MGR = B.EMPNO;
-- 아래 쿼리는 SMITH의 부모노드의 부모노드를 조회한다.
---- A는 현재 테이블, B는 부모 테이블, C는 부모의 부모 테이블
SELECT C.EMPNO, C.ENAME, C.MGR FROM EMP A, EMP B, EMP C WHERE A.ENAME = 'SMITH' AND A.MGR = B.EMPNO AND B.MGR = C.EMPNO;
-- 지금까지 깊은 레벨의 노드를 조회하기 위해서는 셀프 조인을 반복 해야한다. 이를 위해 오라클 데이터베이스는
-- 순환 관계를 가진 데이터를 조회 할 수 있는 계층 쿼리 절과 재귀 서브 쿼리 팩토링 기능을 제공한다.
-- 계층 쿼리 절
-- 기본 문법
-- WHERE 절 다음에 기술하며, FROM 절이 수행된 후에 수행된다.
-- START WITH 절과 CONNECT BY 절로 구성되며, START WITH 절이 수행된 후 CONNECT BY 절이 수행된다 START WITH 절은 생략이 가능하다.
-- START WITH 절 : 루트 노드를 생성하며 1번만 수행
-- CONNECT BY 절 : 루트 노드의 하위 노드를 생성하며 조회 경과가 없을 떄 까지 반복 수행
-- 계층 쿼리 절에서 사용할 수 있는 연산자, 슈도 칼럼, 함수다.
-- 연산자 : PRIOR : 직전 상위 노드의 값을 반환
-- CONNECT_BY_ROOT : 루트 노드의 값을 반환
-- 슈도 칼럼 : LEVEL : 현재 레벨을 반환
-- CONNECT_BY_ISLEAF : 리프 노드인 경우 1, 아니면 0을 반환
-- CONNECT_BY_ISCYCLE : 루프가 발생한 경우1, 아니면 0을 반환
-- 함수 : SYS_CONNECT_BY_PATH : 루트 노드에서 현재 노드까지의 경로를 반환
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
, PRIOR EMPNO AS EMPNO_P
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
--SYS_CONNECT_BY_PATH 함수 : 루트 노드에서 현재 노드까지의 COLUMN을 CHAR로 구분하여 연결한 값을 반환한다.
--SYS_CONNECT_BY_PATH(COLUMN, CHAR) COLUMN안에 CAHR가 포함되어 있으면 ORA-30004에러, 연결한 값의 길이가 4000보다 길면 ORA-01489 에러가 발생한다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
, CONNECT_BY_ROOT ENAME AS RT -- 루트 노드의 값을 반환
, CONNECT_BY_ISLEAF AS LF -- 리프 노드인 경우 1, 아니면 0을 반환
, SYS_CONNECT_BY_PATH (ENAME, ',') AS PT
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
-- 동작 원리 - 계층 쿼리 절은 START WITHJ 절로 루트 노드를 생성한 후
-- 결과가 없을 때 까지 CONNECT 절을 반복 수행하여 하위 노드를 생성한다.
-- 1단계 (LEVEL) : START WITH 절을 수행하여 루트 노드를 생성한다 [START WITH 절은 WHERE 절과 유사하게 동작한다.]
SELECT
1 AS LV
, EMPNO
, ENAME
, MGR
FROM EMP
WHERE MGR IS NULL; -- START WITH MGR IS NULL
-- 다음 예제를 위하여 1단계 수행 결과를 임시 테이블에 저장하자.
DROP TABLE T1 PURGE;
CREATE TABLE T1 AS SELECT 1 AS LV, EMPNO, ENAME, MGR FROM EMP WHERE MGR IS NULL;
SELECT * FROM T1;
-- 2단계(LEVEL 2)
-- CONNECT BY 절로 임시 테이블이 저장된 1단계 결과와 EMP 테이블을 조인한다. CONNECT BY 절도 WHERE절과 유사하게 동작한다.
SELECT
2 AS LV
, C.EMPNO
, C.ENAME
, C.MGR
FROM T1 P, EMP C
WHERE
P.LV = 1
AND C.MGR = P.EMPNO;
-- 다음 예제를 위해 2단계 수행 결과를 임시 테이블에 저장하자
INSERT INTO T1 SELECT 2 AS LV, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C WHERE P.LV = 1 AND C.MGR = P.EMPNO;
SELECT * FROM T1;
-- 3단계 CONNECT BY 절로 임시 테이블이 저장된 2단계 결과와 EMP 테이블을 조인한다.
SELECT
3 AS LV
, C.EMPNO
, C.ENAME
, C.MGR
FROM T1 P, EMP C
WHERE
P.LV = 2
AND P.EMPNO = C.MGR;
-- 다음 예제를 위하여 3단계 수행 결과를 임시 테이블에 저장하자
INSERT INTO T1
SELECT 3, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C WHERE P.LV = 2 AND C.MGR = P.EMPNO;
SELECT * FROM T1;
-- 4단계 CONNECT BY 절로 임시 테이블에 저장된 3단게 결과와 EMP 테이블을 조인한다
SELECT
4 AS LV
, C.EMPNO
, C.ENAME
, C.MGR
FROM T1 P, EMP C
WHERE
P.LV = 3
AND P.EMPNO = C.MGR;
-- 다음 예제를 위하여 4단계 수행 결과를 임시 테이블에 저장하자
INSERT INTO T1
SELECT 4, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C WHERE P.LV = 3 AND C.MGR = P.EMPNO;
-- 5단게 CONNECT BY 절로 임시 테이블에 저장된 4단계 결과와 EMP 테이블을 조인한다.
SELECT
5 AS LV
, C.EMPNO
, C.ENAME
, C.MGR
FROM T1 P , EMP C
WHERE
P.LV = 4
AND C.MGR = P.EMPNO;
-- 결과가 반환되지 않으면 CONNECT BY 절의 수행을 멈추고 수행 결과가 저장된 임시 테이블을 조회하여 결과를 반환한다.
SELECT * FROM T1;
-- 전개방향 : 순환 관계를 순방향 또는 역방향으로 전개할 수 있다.
-- 순방향 전개와 역방향 전개는 데이터 모델 상의 전개 방향이 반대일 뿐 동작원리는 동일하다
-- 순방향 전개 : 전개방향 : 부모 -> 자식 , START WITH 절 : 부모 노드 조회 , CONNECT BY 절 : PK에 PRIOR 기술
-- 역방향 전개 : 전개방향 : 자식 -> 부모 , START WITH 절 : 자식 노드 조회 , CONNECT BY 절 : FK에 PRIOR 기술
-- 순방향 전개는 START WITH 절로 부모 노드를 조회하고, CONNECT BY 절을 통해 자식 노드로 계층을 전개한다
-- PK(EMPNO)에 PRIOR 연산자를 기술하여 현재 노드의 MGR가 부모 노드의 EMPNO 행을 조회한다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
-- 역방향 전개는 START WITH 절로 자식 노드를 조회하고, CONNECT BY 절을 통해 부모 노드로 계층을 전개 한다
-- FK(MGR)에 PRIROR 연산자를 기술하여 현재 노드의 EMPNO가 자식노드의 MGR인 행을 조회한다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
FROM EMP
START WITH ENAME = 'ADAMS'
CONNECT BY EMPNO = PRIOR MGR;
-- 계층 정렬
-- 계층 쿼리 절은 형제 노드의 정렬을 위해 SIBLINGS 키워드를 제공한다
-- 계층 쿼리절에 ORDER BY 절을 사용하면 계층 구조와 무관하게 행이 정렬 된다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD( ' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
, SAL
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO
ORDER BY SAL;
-- ORDER BY 절에 SIBLINGS 키워드를 사용하면 형제 노드 내에서만 행이 정렬되기 때문에 계층 구조를 유지한 채로 행을 정렬 할 수 있다
SELECT
LEVEL AS LV
, EMPNO
, LPAD( ' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
, SAL
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO
ORDER SIBLINGS BY SAL;
-- 루프 처리
-- 부모 노드가 현재 노드의 자식 노드로 연결되면 루프가 발생한다.
-- 계층 쿼리 절은 루프 처리를 위해 NOCYCLE 키워드와 CONNECT_BY_ISCYCLE 슈도 칼럼을 제공한다
-- 예제를 위해 아래와 같은 테이블을 생성하자. NVL 함수로 MGR이 널이 KING의 MGR을 SCOTT의 EMPNO인 7788 로 변경하였다
CREATE TABLE EMP_L AS SELECT EMPNO, ENAME, NVL(MGR, 7788) AS MGR FROM EMP;
SELECT * FROM EMP_L;
-- KING -> JONES -> SCOTT -> KING 구간에서 루프가 발생한다.
-- 계층 전개시 루프가 발생하면 ORA-01436 : CONNECT BY 의 루프가 발생되었습니다 라고 에러가 발생한다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD(' ', LEVEL -1, ' ') || ENAME AS ENAME
, MGR
FROM EMP_L
START WITH EMPNO = 7839
CONNECT BY MGR = PRIOR EMPNO;
-- CONNECT BY 절에 NOCYCLE 키워드를 기술하면 루프가 발생한 노드를 전개하지 않는다, IC열이 1인 행이 루프가 발생한 행이다.
-- SCOTT의 하위 노드들의 루프가 발생하여 전개되지 않았다.
SELECT
LEVEL AS LV
, EMPNO
, LPAD ( ' ' , LEVEL-1, ' ') || ENAME AS ENAME
, MGR
, CONNECT_BY_ISCYCLE AS IC
FROM EMP_L
START WITH EMPNO = 7839
CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
-- 재귀 서브 쿼리 팩토링
-- 재귀 서브 쿼리 팩토링은 ANSI 표준 SQL 문법이다. 계층 쿼리 절보다 복잡하지만 다양한 기능을 사용할 수 있다.
-- 기본문법
-- 재귀 서브 쿼리 팩토링은 WITH절을 사용한다
-- WITH절은 서브쿼리, SEARCH절 CYCLE절로 구성된다.
-- WITH QUERY_NAME ( [ C_ALIAS [, C_ALIAS]...]) AS (SUBQUERY) [EARCH_CLAUSE] [ CYCLE_CLAUSE ]
-- 서브쿼리는 UNION ALL 연산자로 구성된다. UNION ALL 연산자의 상단 쿼리가 START WITH절
-- 하단 쿼리가 CONNECT BY 절의 역할을 수행 한다.
WITH W1 ( EMPNO, ENAME, MGR, LV )
AS (
SELECT EMPNO, ENAME, MGR, 1 AS LV
FROM EMP
WHERE MGR IS NULL -- START WITH 절
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV
FROM W1 P, EMP C
WHERE C.MGR = P.EMPNO -- CONNECT BY 절
)
SELECT LV, EMPNO, LPAD( ' ', LV -1, ' ') || ENAME AS ENAME, MGR FROM W1;
-- 아래 쿼리는 역방향 계층을 전개한다.
WITH W1 ( EMPNO, ENAME, MGR, LV)
AS (
SELECT EMPNO, ENAME, MGR, 1 AS LV FROM EMP
WHERE ENAME = 'ADAMS'
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV
FROM W1 P, EMP C
WHERE C.EMPNO = P.MGR
)
SELECT LV, EMPNO, LPAD(' ', LV - 1, ' ') || ENAME AS ENAME, MGR FROM W1;
--재귀 서브 쿼리 팩토링은 계층 정보를 조회하기 위한 연산자, 슈도 칼럼, 함수를 제공하지 않는다.
WITH W1 (EMPNO, ENAME, MGR, LV, EMPNO_P, RT, PT)
AS (
SELECT
EMPNO, ENAME, MGR
, 1 AS LV --LEVEL
, NULL AS EMPNO_P --PRIOR
, ENAME AS RT --CONNECT_BY_ROOT
, ENAME AS PT --SYS_CONNECT_BY_PATH
FROM EMP
WHERE MGR IS NULL
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR
, P.LV + 1 AS LV -- LEVEL
, P.EMPNO AS EMPNO_P -- PRIOR
, P.RT -- CONNECT_BY_ROOT
, P.PT || ',' || C.ENAME AS PT -- SYS_CONNECT_BY_PATH
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, EMPNO_P, RT, PT
, CASE WHEN LV - LEAD(LV) OVER (ORDER BY SO) <0
THEN 0
ELSE 1
END AS LF -- CONNECT_BY_ISLEAF
FROM W1
ORDER BY SO;
-- 계층 정렬
-- 재귀 서브 쿼리 팽토링은 계층 정렬을 위한 SEARCH 절을 제공한다
-- BREADTH 방식과 DEPTH 방식을 사용할 수 있으며, FIRST BY 뒤에 기순된 C_ALIAS에 따라 행이 정렬 된다. ORDERING_COLUMN은 정렬 순번이 반환될 열을 지정한다.
-- SEARCH { DEPTH || VREADTH } FIRST BY C_ALIAS [ , C_ALIAS]... SET ORDERING_COLUM
-- BREADTH : 자식 행을 반환하기 전에 형제 행을 반환(기본값)
-- DEPTH : 형제 행을 반환하기 전에 자식 행을 반환(= 계층 쿼리절)
WITH W1(EMPNO, ENAME, MGR, LV)
AS (
SELECT EMPNO, ENAME, MGR, 1 AS LV FROM EMP WHERE MGR IS NULL
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR, P.LV+1 AS LV FROM W1 P, EMP C
WHERE C.MGR = P.EMPNO
)
SEARCH BREADTH FIRST BY EMPNO SET SO
SELECT LV, EMPNO, LPAD(' ', LV -1, ' ') || ENAME AS ENAME, MGR, SO FROM W1
ORDER BY SO;
-- DEPTH 방식은 깊이를 기준으로 계층을 탐색한다.
WITH W1(EMPNO, ENAME, MGR, LV)
AS (
SELECT EMPNO, ENAME, MGR, 1 AS LV FROM EMP WHERE MGR IS NULL
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR, P.LV+1 AS LV 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, SO FROM W1
ORDER BY SO;
-- 루프처리 - 재귀 서브 쿼리 팩토링은 루프 처리를 위한 CYCLE 절을 제공한다
-- CYCLE절은 상위 노드에 동일한 C_ALIAS 값이 존재하면 루프가 발생한 것으로 인식한다.
-- CYCLE C_ALIAS [ , C_ALIAS ] ... SET CYCLE_MARK_C_ALIAS TO CYCLE_VALUE DEFAULT NO_CYCLE_VALUE
-- C_ALIAS [, C_ALIAS] : 루프 여부를 확인할 열
-- CYCLE_MARK_C_ALIAS : 루프 여부를 반환할 열
-- CYCLE_VALUE : 루프가 발생한 경우 반환할 값
-- NO_CYCLE_VALUE : 루프가 발생하지 않은 경우 반환할 값
--아래는 CYCLE 절을 사용한 쿼리이다 상위노드 (LV = 1) 에 현재 (LV = 4) 의 EMPNO인 7839 와 동일한 EMPNO가 존재하기 떄문에
-- 루프가 발생한 것으로 인식한다. 계층 쿼리 절고 ㅏ달리 루프가 발생한 노드까지 반환된다. IC열이 1인 행이 루프가 발생한 행이다
WITH W1 (EMPNO, ENAME, MGR, LV)
AS (
SELECT EMPNO, ENAME, MGR, 1 AS LV FROM EMP_L WHERE EMPNO = 7839
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR, P.LV +1 AS LV
FROM W1 P, EMP_L C
WHERE C.MGR = P.EMPNO
)
SEARCH DEPTH FIRST BY EMPNO SET SO
CYCLE EMPNO SET IC TO '1' DEFAULT '0'
SELECT LV, EMPNO, LPAD(' ' , LV-1, ' ') || ENAME AS ENAME, MGR, IC FROM W1
ORDER BY SO;
-- 재귀 서브 쿼리 팩토링은 계층 전개와 무관한 열로도 루프 여부를 확인할 수 있다.
-- DEPTNO열로 루프 여부를 확인한다. SCOTT과 FORD는 상위 노드인 JONES의 DEPTNO가 20이므로 둘다 루프가 발생한것으로 인식된다
WITH W1(EMPNO, ENAME, MGR, DEPTNO, LV)
AS (
SELECT EMPNO, ENAME, MGR, DEPTNO, 1 AS LV FROM EMP WHERE MGR IS NULL
UNION ALL
SELECT C.EMPNO, C.ENAME, C.MGR, C.DEPTNO, P.LV + 1 AS LV
FROM W1 P , EMP C
WHERE C.MGR = P.EMPNO
)
SEARCH DEPTH FIRST BY EMPNO SET SO
CYCLE DEPTNO SET IC TO '1' DEFAULT '0'
SELECT LV, EMPNO, LPAD(' ', LV -1, ' ') || ENAME AS ENAME, MGR, DEPTNO, IC FROM W1
ORDER BY SO;