[SQL] 계층형 질의, 계층형 쿼리

계층형 질의

-하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층(depth)을 표현하는 기법

ex) DEPT2 에서의 부서별 상하 관계

-PRIOR 의 위치에 따라 연결하는 데이터가 달라짐

SEELCT 컬럼명
FROM 테이블명
START WITH 시작조건                  -- 시작점을 지정하는 조건 전달
CONNECT BY [NOCYCLE] PRIOR 연결조건; -- 시작점 기준으로 연결 데이터를 찾아가는 조건

 

** START WITH : 데이터를 출력할 시작 지정하는 조건

** CONNECT BY PRIOR : 행을 이어나갈 조건

** NOCYCLE : 순환이 발생하면 무한 루프가 될 수 있기 때문에 이를 방지하고자 사용

  ㄴ A가 B를 가르키고 B가 다시 A를 가르킬 때 순환이 발생함

 

예제) DEPT2 테이블에 대해 각 부서의 레벨을 출력(최상위 부서가 1 레벨)

SELECT D.*, LEVEL
FROM DEPT2 D
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT;

DCODE	DNAME	PDEPT	AREA	LEVEL
0001	사장실		포항본사	1
1000	경영지원부	0001	서울지사	2
1001	재무관리팀	1000	서울지사	3
1002	총무팀	1000	서울지사	3
1003	기술부	0001	포항본사	2
1004	H/W 지원	1003	대전지사	3
1005	S/W 지원	1003	경기지사	3
1006	영업부	0001	포항본사	2
1007	영업기획팀	1006	포항본사	3
1008	영업1팀	1007	부산지사	4
1009	영업2팀	1007	경기지사	4
1010	영업3팀	1007	서울지사	4

 

 

예제2) CONNECT BY절에 전달 : 연결 조건이 추가되었으므로 모든 조건이 만족할 경우만 하위 레벨로 연결

SELECT D.*
FROM DEPT2 D
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT AND AREA = '서울지사';

DCODE	DNAME	PDEPT	AREA
0001	사장실		포항본사
1000	경영지원부	0001	서울지사
1001	재무관리팀	1000	서울지사
1002	총무팀	1000	서울지사

 

 

예제2-2) WHERE 절에 전달 : 모든 출력 결과 중 '서울지사' 데이터만 출력됨

SELECT D.*, LEVEL
FROM DEPT2 D
WHERE AREA = '서울지사'
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT;

DCODE	DNAME	PDEPT	AREA	LEVEL
1000	경영지원부	0001	서울지사	2
1001	재무관리팀	1000	서울지사	3
1002	총무팀	1000	서울지사	3
1010	영업3팀	1007	서울지사	4

 

 

---

 

계층형 질의 가상 컬럼

1) LEVEL : 각 DEPTH를 표현(시작지점 1)

2) CONNECT_BY_ISLEAF : LEAF NODE(최하위 노드) 여부(참:1, 거짓:0)

 

 

계층형 질의 가상 함수

1) CONNECT_BY_ROOT 컬럼명 : 루트노드의 해당하는 컬럼값

2) SYS_CONNECT_BY_PATH(컬럼, 구분자) : 이어지는 경로 출력

3) ORDER SIBLINGS BY 컬럼 : 같은 LEVEL일 경우 정렬 수행

4) CONNECT_BY_ISCYCLE : 계층형 쿼리의 결과에서 순환이 발생했는지 여부

 

 

 

예제) 계층형 질의절 가상 컬럼 및 함수의 사용

SELECT D.*, LEVEL,
       CONNECT_BY_ROOT DNAME AS ROOT_DNAME,
       SYS_CONNECT_BY_PATH(DNAME, '_') AS SYS_CONNECT
FROM DEPARTMENT D
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO = PDEPT
ORDER SIBLINGS BY DNAME;

DEPTNO	DNAME	PDEPT	BUILD	LEVEL	ROOT_DNAME	SYS_CONNECT
10	공과대학			1	공과대학	- 공과대학
200	자연과학부	10		2	공과대학	- 공과대학 -자연과학부
201	수학과	200	자연관	3	공과대학	-공과대학 -자연과학부 -수학과
202	통계학과	200	자연관	3	공과대학	-공과대학 -자연과학부 -통계학과
203	화학공학과	200	자연관	3	공과대학	-공과대학 -자연과학부 -화학공학과
100	컴퓨터공학과	10		2	공과대학	-공과대학 -컴퓨터공학부
102	빅데이터융합과	100	정보관	3	공과대학	-공과대학 -컴퓨터공학부 -빅데이터융합과
103	소프트웨어공학과	100	정보관	3	공과대학	-공과대학 -컴퓨터공학부 -소프트웨어공학과
101	검퓨터 공학과	100	정보관	3	공과대학	-공과대학 -컴퓨터공학부 -컴퓨터 공학과

-설명-

ORDER SIBLINGS BY를 사용하여 같은 레벨일 경우 DNAME 오름차순으로 정렬,

2레벨은 자연과학부 < 컴퓨터공학부 순서대로 출력되며,

자연과학부 내 3레벨은 사학과 < 통계학과 < 화학공학과 순서대로 리턴되었음

 

---

 

예제) NOCYCLE 옵션

<EMPLOYEES DATA>

EMPLOYEE_ID	DEPARTMENT_ID	MANAGER_ID	NAME	HIRE_DATE
1000	10	2000	일민우	2024/08/19 00:10:21
2000	10	1000	이민우	2024/08/19 00:10:22

 

 

<NOCYCLE 옵션 없이 - ERROR 발생> - 잘못된 예제

SELECT EMPLOYEE_ID, NAME, LEVEL
FROM EMPLOYEES2
START WITH EMPLOYEE_ID = 1000
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

-설명-

1000번 직원의 매니저는 2000번 사원인데, 2000번 사원도 1000번 직원이 매니저이므로 서로 순환구조를 가진다

이런 관계에서 NOCYCLE 없이는 CONNECT BY의 루프가 발생했다는 에러가 발생함

 

 

<NOCYCLE 옵션 사용> - 올바른 예제

SELECT EMPLOYEE_ID, NAME, LEVEL
       CONNECT_BY_ISCYCLE AS IS_CYCLE
FROM EMPLOYEES2
START WITH EMPLOYEE_ID = 1000
CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID;

EMPLOYEE_ID	NAME	LEVEL	IS_CYCLE
1000	일민우	1	0
2000	이민우	2	1