계층구조 쿼리(Hierarchical Queries)

1. 문법

START WITH

– 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
– 서브쿼리를 사용할 수도 있다.

CONNECT BY

– 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
– PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
– CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
– CONNECT BY 자식컬럼 = PRIOR 부모컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
– CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
– 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

– LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

– ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

– 첫째 START WITH 절
– 둘째 CONNECT BY 절
– 세째 WHERE 절 순서로 풀리게 되어있다.

-- LEVEL컬럼으로 depth를 알수 있다.
-- JONES의 관리자는 KING 이며, SCOTT의 관리자는 JONES 이다.
-- 상/하의 계층 구조를 쉽게 조회 할 수 있다.
SELECT LEVEL, empno, ename, mgr
  FROM emp
 START WITH job = 'PRESIDENT'
CONNECT BY PRIOR  empno = mgr;
 
 
LEVEL      EMPNO  ENAME        MGR
------ -------- --------    -------
     1       7839   KING
     2       7566   JONES      7839
     3       7788   SCOTT      7566
     4       7876   ADAMS      7788
     3       7902   FORD       7566
     4       7369   SMITH      7902

출처 : http://oracleclub.com/lecture/1300

2. 계층구조 내에서 정렬을 수행하고자 할 때

-- 계층구조를 그대로 유지한 채 이름으로 정렬함(같은 하위계층의 동일 레벨인 경우)
SELECT LEVEL, empno, ename, mgr
  FROM emp
 START WITH job = 'PRESIDENT'
CONNECT BY mgr = PRIOR  empno
order siblings by ename ;

3. 추가 내용

Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.

예를 들면, 아래와 같이 직원 테이블이 있다고 생각 하자.

직원 직속상사 직급

——————–

철수 순희 대리

순희 영희 과장

길동 순희 대리

영희 개똥 부장

개똥 사장

기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴connect by 를 사용한다면 표현이 가능하다.

재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야한다는 점도 무시 할수 없다.

이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.

기본형식

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원, 직급

from 직원

start with 직원 = ‘개똥’

connect by 직속상사 = prior 직원

직원 직급

————-

개똥 사장

영희 부장

순희 과장

철수 대리

길동 대리

start with

select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.

정의 : start with <조건>

where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.

ex) start with 직원 =’개똥’and 직원 =’순희’

start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.

예를 들면 start with 직원 =’개똥’ or 직원 =’순희’ 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.

(한번은 개똥의 하위에서, 그리고 한 번은 루트로서)

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원, 직급

from 직원

start with 직원 = ‘개똥’ or 직원 =’순희’

connect by 직속상사 = prior 직원 직원 직급

————-

순희 과장

철수 대리

길동 대리

개똥 사장

영희 부장

순희 과장

철수 대리

길동 대리

같은 결과셋이 여러 번 만들어지는 것을 방지하기 위해서는 이러한 조건을 사용해서는 안 된다.


음 쿼리의 예제에서 직원 =’개똥’이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한
목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여
직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.


러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의
값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원, 직급

from 직원

start with 직속상사 is null

connect by 직속상사 = prior 직원

직원 직급

————-

개똥 사장

영희 부장

순희 과장

철수 대리

길동 대리

Connect by Prior

connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.

현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.

connect by 직속상사 = prior 직원

이는 “방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라”라는 의미이다.

쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.

다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데, 결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원, 직급

from 직원

start with 직원 =’철수’

connect by prior 직속상사 = 직원

직원 직급

————-

철수 대리

순희 과장

영희 부장

개똥 사장


쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 그 이유는 ” 방금 전 행의 직속상사 값이 현재
행의 직원 값인 모든 행을 찾아라”라고 선언했기 때문이다. 이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지
않고, 전혀 다른 결과가 얻어짐을 알 수 있다.

prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원, prior 직원 상사,직급

from 직원

start with 직원 =’철수’

connect by prior 직속상사 = 직원

직원 상사 직급

——————-

철수 대리

순희 철수 과장

영희 순희 부장

개똥 영희 사장

여기서는 직원과 직속상사의 이름을 동시에 선택하였는데, 사실 두 값은 같은 행에 존재하는 것이 아니기 때문에 평범한 방법으로는 이와 같은 결과를 얻을 수 없다. 그래서 예제에서는 두 행을 동시 접근하여 각각 값을 얻어낸 것이다.

Level

level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.

계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.

select 직원,level

from 직원

직원 level

———–

철수 0

순희 0

길동 0

영희 0

개똥 0

한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원,직급,level

from 직원

start with 직속상사 is null

connect by prior 직원 = 직속상사

직원 직급 level

——————-

개똥 사장 1

영희 부장 2

순희 과장 3

철수 대리 4

길동 대리 4

트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다.

level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원

또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.

select lpad(‘ ‘,(level-1)*2,’ ‘)||직원 직원,직급,level

from 직원

start with 직속상사 is null

connect by prior 직원 = 직속상사 and level <=3

직원 직급 level

——————-

개똥 사장 1

영희 부장 2

순희 과장 3

철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다.

level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다. 어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.

Oracle – 오라클 고급쿼리 – 계층적 쿼리 ( connect by ).

출처 : http://gainax.kr/?p=122

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다