데이터베이스 계층형 쿼리

계층형 쿼리

데이터베이스에서 데이터는 입력 순서로 저장된다.

하지만 각 데이터는 일종의 트리 구조의 계층형으로 구성되어 있다. 아래의 트리 구조를 참고하자.

각 사원은 상급 관리자에 대한 정보를 포함하는데, smith의 경우 상급 관리자는 ford이다. 그리고 ford의 경우 jones를, jones는 king을 상급 관리자로 두고 있다. 이를 데이터베이스에서 표현하기 위해서는 어떻게 할까?

이를 위해서 사용하는 것이  계층형 쿼리이다. 계층형 쿼리를 위해서는 계층의 시작 지점과 해당 위치에서 어떤 위치로 연결되는지를 표현해야 한다. 이를 위해서 다음과 같이 표현한다.

select 칼럼명 from 테이블명 start with 조건 connect by prior 조건;

start with는 계층의 시작 지점을 나타내기 위한 조건을 입력하고, connect에서는 다음 계층을

어떻게 결정할 것인가를 나타내는 조건을 입력한다. 그리고 계층형 쿼리는 하향식과 상향식 두 가지가 존재한다.

하향식

먼저 하향식에 대해서 살펴보도록 하자. 이를 위해서는 먼저 가장 상위 계층을 start로 두고, 아래 방향으로 진행하도록 선언해야 한다.

따라서 아래 예시에서는 king을 시작지점으로 두고 다음 계층을 현재 계층을 mgr로 두고 있는 행으로 넘어가도록 선언하였다.

상향식

다음은 상향식이다. 이는 하향식과 반대로 기입하면 된다. 하지만 출력 시 하향식은 전체 트리 구조가 다 출력되지만, 상향식에서는 한 줄기만 출력됨을 기억하자. 현재 리프 노드와 그와 연결된 노드를 출력하기 위해서 사용한다고 생각하면 될 듯하다.

이를 위해서 가장 말단인 리프노드의 smith를 시작지점으로 넣자. 그리고 윗 방향을 가리키도록 현재 계층의 mgr을 empno로 가지고 있는 행 데이터를 검색한다.

level을 사용

지금까지 계층형 쿼리를 살펴보았는데, 다소 보기가 불편하다. 각 줄기를 순서대로 출력하곤 있지만 이를 좀 더 시각화해서 보고 싶다. 이를 위해서 level이라는 값을 사용하여 시각화 해보도록 하겠다. 시각화를 위해서는 앞에서 배웠던 lpad를 이용할 것이다. lpad가 기억이 나지 않는다면, 아래 문법을 참고하고 넘어가자.

*lpad

lpad(칼럼명, 출력할 전체 문자열 길이, 패딩으로 사용할 단어)

level은 계층을 표현하는 값이라고 생각하면 된다. 하향식의 경우 트리 구조에서 루트에 해당하는 부분이 1이 되며, 아래로 내려갈수록 1씩 증가한다.

따라서 전체 문자열의 길이를 ‘출력할 단어의 개수 + 해당 레벨 * 3 - 3’로 정의하면, 각 계층은 (level-1) * 3 만큼 패딩을 위한 공간을 가지게 된다. 그리고 이 부분을 원하는 형태로 입력하면 되는데, 우리는 공백을 사용하겠다.

확인해보면, 다음처럼 연결된 부분끼리 출력이 이루어지고 있다. 이를 통해 좀 더 시각화하여 계층을 나타낼 수 있다.

노드와 브랜치 제거

다음은 트리 구조에서 일부를 제거하는 방법이다. 노드를 제거하는 것은 단순하게 하나의 행 데이터만을 제거하는 작업이다. 반면에 브랜치를 제거할 경우에는 하위 계층의 행 데이터까지 다 제거한다.

먼저 노드를 제거해보도록 하겠다. 제거할 노드에 대한 조건을 아래와 같이 where절에 입력하고 출력한다.

ford라는 행 데이터가 삭제되고 나머지는 그대로 출력되고 있다. 그리고 ford의 하위 데이터인 smith역시 jones의 하위 데이터로 표현되고 있다.

다음은 브랜치 제거이다. 이번엔 where 절이 아닌 마지막에 and 절을 통해서 삭제한다. 이번에도 ford를 제거해보겠다.

노드만을 제거할 때는 ford 하나만 삭제되었지만, 이 때는 ford 아래 있던 smith까지 삭제되는 것을 볼 수 있다.