[ Database ] SQLD DB 계층형 질의

 

 

 

계층형 질의란 말 그대로 계층형 데이터를 다루는 쿼리를 수행하는 것이다.

​

그럼 계층형 데이터란 무엇일까?

​

​

계층형 데이터란 동일한 테이블에 계층적으로 상위와 하위 데이터가 포함되어진 데이터들을 말한다.

 

예를 들어 사원 테이블에는 사원들의 데이터가 있지만

 

분명 사원마다 직급이 있거나 관리자(직속상관)이 있기 마련이다.

​

​

먼저, 계층형 질의에 대해 알아보기 앞서 아래의 용어를 숙지하자.

​루트 노드 ( 루트 데이터 ) : 가장 최상위의 데이터

리프 노드 ( 리프 데이터 ) : 가장 최하위의 데이터

데이터를 전개하다 : 데이터를 찾아나가다

 

 

본론으로 들어가 아래와 같은 사원 테이블이 있다고 가정해보자.

 

사원 테이블을 보면 '진남'이 관리하는 사원은 '진홍'이며,

 

'진홍'은 '동현'을, '동현'은 '경래'와 '주용'을 관리하는 것을 알 수 있다.

​

'경래'와 '주용'은 하위 사원이 존재하지 않으며 '진남'은 최상위 사원이기에 직속상관이 존재하지 않음을 알 수 있다.

​

이를 정리해보면

 

이제 이런 계층형 테이블인 사원 테이블을 가지고 계층형 질의에 대해 알아보자

 

 

먼저 계층형 질의의 형태는 아래와 같다.

 

* [ ] 안의 구문은 필수요소가 아님을 뜻함

SELECT ...
FROM ...
WHERE 조건
START WITH 조건
CONNECT BY [ NOCYCLE ] 조건
[ ORDER SIBLINGS BY 컬럼명1, 컬럼명2 ... ] ;

 

 

각각 어떤 의미가 있는지 알아보자면

 

 

그리고 계층형 질의에서 주로 사용되는 가상 컬럼을 알아보자면

 

 

이를 위에서 보여준 사원 테이블에 적용해 순방향 쿼리를 작성해보면

SELECT LEVEL, 사원이름, 직속상관, 직급
FROM 사원
START WITH 직속상관 IS NULL
CONNECT BY PRIOR 사원이름 = 직속상관 ;

 

사원 테이블을 유심히 보면, '사원이름'컬럼과 '직속상관'컬럼은 계층형 데이터라는 사실을 알 수 있다.

​

'START WITH 직속상관' 을 줘서 '직속상관'컬럼이 부모컬럼임을 알 수 있고 IS NULL 조건을 통해

​

직속상관이 없는 사원부터 데이터 전개가 시작되어 진다.

​

또한 'CONNECT BY PRIOR 사원이름 = 직속상관' 을 통해

 

직속상관(부모)에서 사원이름(자식) 뱡향(순방향)으로 데이터 전개가 시작되어 진다.

​

아래와 같은 결과가 나온다.

 

 

그럼 이번에는 반대로 특정 사원으로부터 최상위 방향으로 찾아가는 역방향 쿼리를 작성해보자.

SELECT LEVEL, 사원이름, 직속상관, 직급
FROM 사원
START WITH 사원이름 = '경래'
CONNECT BY PRIOR 직속상관 = 사원이름 ;

 

이전 쿼리에서는 데이터 전개를 진남(최상위) 부터 시작했지만,

 

이번에는 'START WITH 사원이름 = 경래'를 통해 최하위부터 시작한다.

​

'CONNECT BY PRIOR 직속상관 = 사원이름'을 통해

 

사원이름(자식)에서 직속상관(부모) 뱡향(역방향)으로 데이터 전개가 시작되어 진다.

​

결과는 아래와 같다.

 

 

마지막으로 계층형 질의에서 사용되어지는 함수들을 살펴보자.

​

ORACLE은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해 아래와 같은 2가지 함수를 제공한다.

 

 

이를 적용해 쿼리를 작성해보면

SELECT 사원이름
            ,  직급
            ,  직속상관
            ,  SYS_CONNECT_BY_PATH ( 사원이름, '/' ) 속해있는경로
            ,  CONNECT_BY_ROOT ( 사원이름 ) 최상위사원
FROM   사원
START WITH 직속상관 IS NULL
CONNECT BY PRIOR 사원이름 = 직속상관 ;

 

 

위 쿼리는 '직속상관'이 NULL인 데이터부터 시작해,

 

직속상관(부모)에서 사원이름(자식) 쪽으로 순방향 데이터전개하는 쿼리이다.

​

결과는 아래와 같다.