오라클 계층적 쿼리 CONNECT BY PRIOR

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 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.
(한번은 개똥의 하위에서, 그리고 한 번은 루트로서)

 

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 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.



Order siblings by

계층적쿼리에서 order by로 정렬을 하려고 하면 트리를 만든 다음에 다시 정렬을 하기 때문에 계층적인 순서가 엉커버린다.
이러한 결과를 피하기 위해서 9i 부터 siblings by 절을 사용하면 각 계층내의 데이터만을 정렬시킬수 있다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
order by 직원

   직원     직급
-------------
      길동   대리
      철수   대리
    순희     과장
  영희       부장
개똥         사장

-> 계층을 만든후에 직원이름을 기준으로 정렬된다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
order siblings by 직원


   직원     직급
-------------
개똥       사장
  영희     부장
    순희   과장
      길동 대리
      철수 대리

  -> 길동과 철수가 같은 level이기 때문에 정렬이 된다.


Sys_connect_by_path()

9i부터 사용되는 함수로써 계층적인 쿼리를 실행할 때 이 함수를 사용하면 루트에서 끝 노드까지 전체 경로를 리턴할 수 있게 해준다.

형식 : Sys_connect_by_path( <열>,<분리문자> )

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급,Sys_connect_by_path(직원,'-') 경로
  from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
order siblings by 직원

   직원       직급               경로
----------------------------------
개똥         사장     -개똥
  영희       부장     -개똥-영희
    순희     과장     -개똥-영희-순희
      길동   대리     -개똥-영희-순희-길동
      철수   대리     -개똥-영희-순희-철수