[Oracle] 오라클 고급쿼리 – 계층적 쿼리 (level)

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