[Oracle] 계층형 쿼리 (Hierarchical Query)

💡계층형 쿼리

---

계층형 쿼리(Hierarchical Query)는 자기 참조를 하는 테이블에서 사용하며, 레코드의 관계가 서로 간에 상하 수직 구조인 경우에 사용한다.
계층형 쿼리는 오라클 전용 쿼리이다.

자바에서는 계층형 쿼리를 트리 구조로 이해할 수 있다.

 

계층 구조

SELECT * FROM tblSelf;
홍사장
    - 김부장
        -박과장
            -최대리
                -정사원
    - 이부장
        -하과장
            -황대리
        -신과장
            -허사원

이러한 테이블을 보고 레코드의 관계가 서로 상하 수직 구조라고 한다.
이런 경우에만 계층형 쿼리를 사용할 수 있다.

부품과 부모 부품의 정보를 동시에 가져오는 작업을 해보도록 하자.

 

self join 혹은 상관 서브 쿼리 사용

SELECT
	c1.name AS 부품명,
	c2.name AS 부모부품명
FROM tblComputer c1				--부품(자식)
	INNER JOIN tblComputer c2	--부모부품(부모)
		ON c1.pseq = c2.seq;

self join은 자식과 부모 관계만을 인식할 수 있는 반면에, 계층형 쿼리는 세대(단계) 별로 테이블을 확인할 수 있다.

계층형 쿼리를 실습해 보며 사용법을 익혀보도록 하자.

 

💡계층형 쿼리의 사용

1. start with 절 + connect by절

2. 계층형 쿼리에서만 사용 가능한 의사 컬럼들

  a. prior: 자기와 연관된 부모 레코드를 참조

  b. level: 세대수(depth, generation)

 

start with절 + connect by절

SELECT
	*
FROM tblComputer
	START WITH 조건		--루트 레코드를 찾는 조건
		CONNECT BY 조건	--현재 레코드와 부모 레코드를 연결하는 조건

start with절 + connect by절은 위와 같이 작성한다.

만약 where절을 사용할 경우 connect by절 다음에 작성하면 된다.

 

prior 예약어

SELECT
	*
FROM tblComputer
	START WITH seq = 1					--루트 레코드를 찾는 조건
		CONNECT BY pseq = PRIOR seq;	--현재 레코드와 부모 레코드를 연결하는 조건

계층형 쿼리는 테이블이 하나이기 때문에 본인의 부모 부품 번호와 부모 부품 번호의 기본키 역할을 하는 번호를 작성한다.
이때 부모 부품 번호의 기본키 역할을 하는 게 없으므로 prior 예약어를 붙여 가상 컬럼으로서 부모 테이블의 레코드를 참조하게 한다.

 

SELECT
	seq AS 번호,
	name AS 부품명,
	PRIOR name AS 부모부품명
FROM tblComputer
	START WITH seq = 1
		CONNECT BY pseq = PRIOR seq;

select절에서 prior 예약어를 붙였기 때문에 부모의 부품명을 가져온다.

 

level 예약어

SELECT
	seq AS 번호,
	lpad(' ', (LEVEL - 1) * 10) || name AS 부품명,
	PRIOR name AS 부모부품명,
	LEVEL
FROM tblComputer
	START WITH seq = 1
		CONNECT BY pseq = PRIOR seq;

lpad의 들여 쓰기 기능으로 계층구조를 표현하였다.

 

특정 세대만 확인하기

SELECT
	seq AS 번호,
	lpad(' ', (LEVEL - 1) * 10) || name AS 부품명,
	PRIOR name AS 부모부품명,
	LEVEL
FROM tblComputer
	START WITH seq = 2
		CONNECT BY pseq = PRIOR seq;

start with절의 값을 수정하여 특정 계층만 확인할 수 있다.

seq = 2로 수정하였더니 컴퓨터가 아니라 본체의 계층을 보여준다.

 

직속상사 확인하기

SELECT
	seq AS 번호,
	lpad(' ', (LEVEL - 1) * 10) || name 이름,
	PRIOR name AS 직속상사,
	LEVEL
FROM tblSelf
	START WITH seq = 1
		CONNECT BY super = PRIOR seq;

• prior: 부모 레코드 참조 > 직속 상사
• connect_by_root: 최상위 레코드 참조 > 홍사장
• connect_by_isleaf: 자식이 있는지를 검사(말단노드)
• sys_connect_by_path: 계층 구조를 출력

SELECT
	seq AS 번호,
	lpad(' ', (LEVEL - 1) * 10) || name 이름,
	PRIOR name AS 직속상사,
	connect_by_root name,
	connect_by_isleaf name
FROM tblSelf
	START WITH seq = 1
		CONNECT BY super = PRIOR seq;

 

정렬하기

SELECT
	seq AS 번호,
	lpad(' ', (LEVEL - 1) * 10) || name AS 부품명,
	PRIOR name AS 부모부품명,
	LEVEL
FROM tblComputer
	START WITH seq = 1
		CONNECT BY pseq = PRIOR seq
			--ORDER BY name ASC
			ORDER siblings BY name ASC;

계층을 정렬할 때에는 order by절에 siblings를 사용한다.