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사용가능
'Database > Oracle' 카테고리의 다른 글
| Date 포멧.. (0) | 2009.02.11 |
|---|---|
| 트리거 예제 (0) | 2009.02.11 |
| group by (0) | 2009.02.11 |
| 오라클 페이징 (서브쿼리) (0) | 2009.02.11 |
| 오라클 원격 접속하기 (0) | 2009.02.11 |