계층형 질의

계층형 질의?

 계층형 데이터란 말 그대로 계층적인 구조를 가지고 있는 데이터 입니다 상위와 하위데이터가 포함된 데이터를 의미하는데 동일 테이블 내에서 이러한 계층적인 데이터가 있는 경우 조직적으로 상위 하위 관계가 설정이 되기 떄문에 이런 테이블을 조회하기 위해선 계층형 쿼리(Hierarchical Query)를 사용합니다 

 

 

 위와 같은 계층구조는 트리의 형태로 관계를 표현하는 것은 가능하지만 SQL 문으로 관계를 표현하는 것이 불가능하다고 보는게 맞습니다 그러나 PL/SQL 루틴과 CONNECT BY를 사용하면 위와 같은 계층형 구조를 SQL 을 통해 표현할 수 있습니다.

 

계층형 질의 문법

 

1. START WITH 절 : 계층구조의 시작 위치를 지정 
2. CONNECT BY절 : 다음에 전개될 테이블을 지정하는 구문 
3. PRIOR : CONNECT BY 절 안에서 사용이 되며  현재 읽을 칼럼을 지정한다 
4. PRIOR 자식 = 부모 : 여기서 자식과 부모는 각각 계층형 데이터 구조에서 하위데이터 상위데이터를 나타냅니다 그런경우 내가 만약 PRIOR 자식=부모 형태로 썼다면 이것은 자식에서 부모 ->자식으로 전개되는 순방향 전개입니다
5. PRIOR 부모 =자식 : 위에 상황과 반대 형태로 자식-> 부모 순으로 전개되는 역방향 전개 입니다.
6. NOCYCLE : 데이터를 전개 하면서 동일한 데이터가 전개중에 다시 나타났다면 이러한 경우를 사이클이 형성되었다 라고 합니다  이런 사이클이 발생하면 런타임 에러가 발생하는데 NOCYCLE 명령어를 수행하면 사이클이 발생했을떄 프로그램전개를 끝냅니다 
7. ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 시작한다.
8. WHERE : 모든 전개가 끝나면 조건에 만족하는 데이터만 추출한다
9. 계층형 질의에서 사용하는 가상 칼럼 

가상 칼럼	설명
LEVEL	루트데이터면 1 아래로 내려가면 +1씩 증가
CONNECT_BY_ISLEAF	전개 과정에서 LEAF 데이터면 1 아니면 0
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 갖는데 해당 데이터가 조상으로 존재하면 1 아니면 0 (사이클 조건을 사용했을 떄만 사용가능)

계층형 질의를 간단하게 파악해보기 위해 위와 같은 테이블을 만들어 보겠습니다 그리고 이 데이터에 SAMPLE 데이터를 삽입합니다 

위와 같이 sample 데이터를 삽입햅 보겠습니다 DEP_CD 는 부서 코드, PARENT_CD 상위 부서코드  DEPT_NAME은 부서명 입니다 이렇게 삽입을 하면 아래와 같은 형태가 됩니다 

부서코드가 101인 총괄개발구는 루트노드 이기 때문에 부모 노드의 값이 NULL 이 됩니다 그리고 그 아래로 부서코드가 102,103,104인 값을 자식 으로 가지게 되는데 이런 순서로 계층을 가다보면

                     

이러한 구조를 가지게 된다 

그렇다면 이제 이 구조를 계층형 쿼리를 통해  조회 한다면 

해당 코드는 아래와 같은 결과를 반환합니다

그렇다면 코드를 한번 분석해 봅시다 상위 부서코드가 없다는 것은 가장 상위 레벨의 노드라는 말과 같습니다 따라서 상위 노드가 없는 노드를 시작점으로 잡고 

CONNECT BY PRIOR DEP_CD = PARENT_CD 를 통해 부모에서 자식으로 계층형 쿼리와 계층 쿠리에서 제공해주는 LEVEL 칼럼을 조회한 결과와 위의 그래프가 동일한 것을 확인 할 수 있습니다