[Oracle] 계층형 쿼리(START WITH.. CONNECT BY~)

출처 : https://docu94.tistory.com/70

https://coding-factory.tistory.com/461

https://tiboy.tistory.com/563

 

SELECT [컬럼]...
FROM [테이블]
WHERE [조건]
START WITH [최상위 조건]
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태를 말한다.

예를 들면 특정회사의 부서, 특정학교의 학과 등이 있다.

계층형 쿼리는 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말한다.

 

START WITH절은 루트노트(행)을 명시한다.

EMP 테이블을 확인해보면 해당 사원의 관리자가 있다.

그 관리자가 최종 관리자일 수 있지만 그 관리자도 더 상위의 관리자가 있을 수도 있다.

하지만 결국엔 마지막 최종 관리자가 있을 것이고 그 최종 관리자는 더 이상 자신의 관리자는 없기에 관리자 컬럼은 NULL일 것이다.

 

START WITH

그래서 시작을 최종 관리자부터 해야 하기에

START WITH에서 관리자 컬럼이 NULL인 부분을 조건식으로 넣게 된다.

EMP에서 관리자 컬럼 MGR을 보면 ENAME = 'KING'이 NULL로 되어있다. 최종 관리자라는 의미이다.

CONNECT BY

CONNECT BY는 연결 고리를 가지고 목록을 가져온다.

먼저 START WITH에서 조건에 맞는 최상위 행을 가져온다.

다음으로 최상위 행을 관리자로 갖는 다음 계층 데이터를 가져와야 한다.

그럼 현재 찾아온 취상위 관리자의 EMPID를 추출해서 다음 행을 구해야 한다.

그 최상위 EMPID를 MGR로 갖는 행들을 찾아야 한다.

CONNECT BY PRIOR EMPID = MGR

연결하는 방식은 미리 구한 앞 행의 EMPID와 구해야 할 MGR이 같은 행들을 구한다.

그럼 이제 START WITH에서 구한 최상위 행과 그 행의 EMPID를 MGR로 갖는 행들을 갖게 되었다.

이제 다음 작업은 최상위 행으로 구한 두 번째 계층의 행들을 가지고 그 계층들의 EMPID를 MGR로 갖는 다음 계층의 행들을 구한다.

그렇게 쭉~ 긑까지 구하면 된다.

정리하자면,

연결 방식은 바로 전에(PRIOR) 구한 계층의 EMPID를 MGR로 갖는 데이터를 구한다.

CONNECT BY EMPID = PRIOR MGR

만약 PRIOR를 반대로 설정한다면 어떻게 될까?

연결 방식은 바로 전에(PRIOR) 구한 계층의 MGR을 EMPID로 갖는 데이터를 구한다.

START WITH로 먼저 최상위 행을 구했는데..

그 다음 구할 계층의 최상위 행의 MGR(최상위니 NULL)을 EMPID로 갖는 값은 없다.. NULL이니까

LEVEL

계층형 쿼리를 조회하면 현재 형이 계층의 몇 번째 레벨인지 표시해 준다.

계층형 쿼리에서만 사용하는 모조 컬럼이다. 실제 테이블에 있는 컬럼은 아니지만 SELECT 문에서 사용할 수 있는 컬럼이다.

최상층 레벨을 1로 해서 쭉- 2,3,4로 나온다.

가장 많이 응용하는 방법이 레벨별로 들여쓰기를 하여 좀 더 직관적으로 데이터를 표현하는 것이다.

 

레벨의사컬럼 활용 레벨별로 들여쓰기

SELECT 
LPAD(' ', 2*(LEVEL-1)) || DEPT_NAME AS DEPT_NAME, --레벨별 들여쓰기
DEP_CD,
PARENT_CD ,
LEVEL
FROM DEP 
START WITH PARENT_CD IS NULL --최 상위노드 설정,
CONNECT BY PRIOR DEP_CD = PARENT_CD;--부모노드와 자식노드 연결

레벨-1 * 공백(2)를 앞에 삽입하여 좀 더 계층형 데이터를 직관적으로 데이터를 만들었다.

ORDER BIBLINGS BY

order by로 sort할 경우 모든 데이터를 가지고 정렬을 하지만 order siblings by 는 각 계층별로 정렬을 한다.

그냥 정렬하면 계층구조가 흐트러지기 때문에 계층구조는 그대로 유지하면서 동일 부모를 가진 자식들끼리의 정렬 기준을 두는 것이다.

예

START WITH a.parent_id = '0'
CONNECT BY PRIOR a.menu_id = a.parent_id
ORDER SIBLINGS BY a.sort asc

부모컬럼인 parent_id로 정렬하고 그 다음 동일 부모인 자식들(sort)로 정렬이 된다.