[Oracle] 계층형 쿼리 쉬운 정리(START WITH CONNECT BY)

오라클 사용 시 계층형 쿼리를 종종 사용하게 된다.

LEVEL 어쩌구 CONNECT BY 어쩌구..

처음 마주하면 굉장히 복잡하고 어려워보인다.....

몰라서 검색해봐도 무슨 말인지 이해하기 어려운 설명들이 많다.

 

무조건 이해를 위주로 한 쉬운 설명을 시작 !

 

먼저 계층형 쿼리가 무엇인지 알아보자.

 

계층형 쿼리란?

테이블에 계층형 구조 즉, 수직적 구조가 존재할 때 이를 조회하기 위한 쿼리이다.

 

그림으로 쉬운 설명.

 

회사 조직도를 예시로 들어보자.

 

'회사'는 최상위계층이고, 회사를 중심으로 개발부, 경영지원부, 영업부 세 부서로 나뉜다.

계속해서 부서를 중심으로 하위 계층이 생성되어 있다고 가정하자.

이를 테이블로 나타내면 아래와 같다.

 

 

부서번호는 고유식별자로 Primary Key가 된다. 

 

 

START WITH

- 계층의 루트로 사용될 행을 지정한다.

- 서브 쿼리를 사용할 수 있다.

- 어떤 레코드를 최상위 레코드로 정할지 결정한다.

 

예시.

SELECT *
  FROM EMP_TB
 START WITH 부서번호 IS NULL;

부서번호가 NULL인 '회사'를 최상위 계층으로 정할 것이기 때문에 이렇게 코드가 짜여지게 된다.

 

 

 

 

CONNECT BY

- 연결고리를 만든다.

- PRIOR 연산자로 계층구조를 표현할 수 있다.

- 서브쿼리를 사용할 수 없다.

 

앞서 START WITH을 통해 최상위 행을 가져왔다.

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

최상위 행인 '회사' 데이터를 기준으로 생각하자. 

'회사'데이터의 '부서 번호'가 '상위 부서 번호'와 일치하는 데이터를 가져오면 된다.

따라서,

 

CONNECT BY PRIOR 부서번호 = 상위부서번호

이렇게 표현할 수 있다. 

 

만약, 반대로 PRIOR를 반대로 선언하면 어떻게 될까?

CONNECT BY PRIOR '상위부서번호' = '부서번호'

이렇게 선언하게 되면 '회사'데이터의 상위부서번호가 NULL 값이고,

NULL값을 부서번호로 갖는 데이터는 없기 때문에 출력결과가 나오지 않게 된다.

 

***CONNECT BY NOCYCLE PRIOR: NOCYCLE파라미터를 이용하여 무한 루프 방지

 

 

 

 

LEVEL

- 계층 구조 쿼리에서 수행 결과의 Depth를 표현하는 의사컬럼.

 

무슨 뜻이냐면.. 

현재 행이 계층의 몇번째 레벨인지 표시해주는 값이라는거다. 

또 계층형 쿼리에서만 존재하는 모조 컬럼이다.

최상위 계층이 1이고 그 아래로 2,3,4,5... 이어진다.

SELECT 부서번호, LEVEL, LPAD(' ' , 4*(LEVEL-1)) || 부서이름 AS CONNECT
  FROM EMP_TB
START WITH 상위번호 IS NULL
 CONNECT BY PRIOR 부서번호 = 상위부서번호;

 

LEVEL을 가지고 계층형 구조를 보기 쉽게 나타낼 수 있다.