프로그래밍&IT/Oracle

[Oracle] 계층형 질의

sjoo 2021. 7. 27. 13:23

계층형 질의란?

 : 한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관계를 이루며 존재할 때, 이 관계에 따라 레코드를 hierarchical(상하위) 한 구조로 가져올 때 사용되는 SQL

 

 

 

회계팀, 인사팀, 재무팀 부모부서 => 경영지원부

국내영업팀, 해외영업팀, 영업지원팀 부모부서 => 영업본부

 

경영지원부 자식부서 => 회계팀, 인사팀, 재무팀

영업본부 자식부서 => 국내영업팀, 해외영업팀, 영업지원팀

 

먼저 위에 조직도에 맞춰 테이블을 생성한다.

CREATE TABLE TB_CMM_DEPT
( 
   DEPT_CD     VARCHAR2(10) NOT NULL PRIMARY KEY,
   PAR_DEPT_CD VARCHAR2(10),
   DEPT_NM     VARCHAR2(50)
 );

 

[TB_CMM_DEPT 테이블 컬럼명]

DEPT_CD 부서코드
PAR_DEPT_CD 상위부서코드
DEPT_NM 부서명

 

 

테이블을 생성한 후 데이터를 넣어준다.

 

※ START WITH

   :  계층 구조 전개의 시작 위치를 지정하는 구문으로 즉, 루트 데이터를 지정한다.

 

ex01. A회사가 최상위 레코드인 경우

SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD IS NULL;

 

ex02. 경영지원부가 최상위 레코드인 경우

SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD='DEPT_002';

 

※ CONNECT BY

   :  다음에 전개될 자식 데이터를 지정하는 구문

* PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정

  - PRIOR 자식= 부모 : 부모->자식 방향으로 전개하는 순방향 전개

  - PRIOR 부모 = 자식 : 자식->부모 방향으로 전개하는 역방향 전개

 

※ NOCYCLE

  : 동일한 데이터를 반복해서 읽는 CYCLE 형성 방지

 

※ ORDER SIBLINGS BY

  : 형제노드(동일한 LEVEL)의 데이터 사이에서 정렬 수행

 

 

 

1.  다음 SQL을 통해 최상위 ROW를 선택했다.

SELECT * FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD='DEPT_001';

 

PAR_DEPT_CD = 'DEPT_001'인 경영지원부와 영업본부가 선택된다.

이때, 최상위로 지정한 ROW에서 어떤 방향으로 전개할 것인가를 지정하려고 한다.

 

 

 

1. 부모행 방향으로 전개 (Bottom-Up)

 

  SELECT * FROM TB_CMM_DEPT
  START WITH PAR_DEPT_CD='DEPT_001'
  CONNECT BY  PRIOR PAR_DEPT_CD = DEPT_CD;

[결과 데이터]

 

 

 

 

2. 자식행 방향으로 전개 (Top-Down)

 

 

  SELECT * FROM TB_CMM_DEPT
  START WITH PAR_DEPT_CD='DEPT_001'
  CONNECT BY PAR_DEPT_CD = PRIOR DEPT_CD;

 

[결과 데이터]

 

 

※ LEVEL

: 계층구조 쿼리해서 수행 결과의 DEPTH를 표현하는 의사컬럼(가짜컬럼)이다.

SELECT LEVEL,
         DEPT_CD,
         PAR_DEPT_CD,
         DEPT_NM
  FROM TB_CMM_DEPT
  START WITH PAR_DEPT_CD='DEPT_001'
  CONNECT BY PAR_DEPT_CD = PRIOR DEPT_CD;

 

 

 

 

 

[참고]

https://tragramming.tistory.com/82