[복습] SQL | Oracle 부서의 계층을 표시하기, 계층형 질의

계층형 질의

계층형 질의란 각 행들의 연결고리를 통하여 행과 행 사이의 계층(depth)를 표현하는 기법이다.

 

dept2파일을 열어보면

 

PDEPT는 상위부서인데 그렇게되면 아래와 같이 연결하여 해석할 수 있는 게 계층형 질의이다.

-- 사장실
----경영지원부
------재무관리팀
------총무팀
----기술부
----영업부

먼저, 문법은 다음과 같다.

 

문법만 보면 잘 이해가 안가서 예시가 필요하다.

 

사장실은 PDEPT가 NULL이므로 START에 NULL인 PDEPT부터 시작하겠다고 명시한 후,

CONNECT는 DCODE와 PDEPT가 같은 조건을 이어가달라고 하는 것이다.

여기서 PRIOR 위치가 중요한데 

START WITH에 PDEPT부터 시작하라고 하였으니 

다음 레벨을 찾기 위한 조건이 PDEPT = 0001이다. 로 전달되기 때문에 PRIOR이 DCODE 앞에 붙어야 한다.

DCODE가 상수화 되어 찾게 된다.

풀어 쓰자면, PDEPT가 IS NULL인 사장실부터 시작하고,

PDEPT=0001인 DCODE를 다름 레벨로 연결해달라고 하여 경영지원부와 영업부가 불려오는 것이다.

 

SQLD에 PRIOR가 어디 붙는지 문제가 나오니 잘 이해하여야겠다.

 

 

연습문제를 하나 풀어보자,

DEPT2테이블에서 부서의 상하관계를 들여쓰기를 반영하여 표현하여라.

 

이렇게 표현할 수 있다.

 

 

연습문제를 하나 더 풀어보자.

DEPARTMENT 테이블을 사용하여 위 문제와 같게 해결하여라.

 

 

간혹 하위부터(트리구조가 아닌) 부모를 찾아가는 형식으로 출력하려면, 즉 DEPT2에서 영업1팀의 상위부서는 어디지? 라는 구조를 파악하려면 다음과 같이 찾아갈 수 있다.

 

이 때 START는 영업1팀이 되고, PRIOR는 DCODE에서 PDEPT를 찾아가는 형식으로 확인하면 된다.

 

여러 조건이 붙는 경우를 예시로 살펴보자.

 

먼저 시작점을 제한하기 위한 조건이다.

공과대학을 시작으로 하는 학과만 보겠다고 하면 위와 같이 조건을 주어 볼 수 있다.

 

다음은 추가조건이 붙는 경우이다.

서울지사에 있는 부서들만 보겠다고 할 경우 위와 같이 조건을 줄 수 있다.

 

다음은 시험에 나오는 범위이다.

 

계층형 질의 가상 컬럼인 LEVEL, CONNECT_BY_ISLEAF 두 가지이다.

LEVEL은 위에서도 봤지만 CONNECT_BY_ISLEAF와 함께 이제 설명하겠다.

 

1) LEVEL: 각 DEPTH를 표현(시작점부터 1)

2) CONNECT_BY_ISLEAF: LEAF NODE(최하위노드)여부 표시. (참: 1, 거짓: 0)

이렇게 보면 한번에 이해가 될 듯 하다.

 

 

다음은 계층형 질의 가상함수이다.

1) CONNECT_BY_ROOT 컬럼명: 루트 노드에 해당하는 컬럼값

2) SYS_CONNECT_BY_PATH(컬럼, 구분자): 이어지는 경로 출력

3) ORDER SIBLINGS BY 컬럼: 같은 LEVEL일 경우 정렬

 

이것도 예시로 확인하면 될 것이다.