3. 오라클 - 레벨링(LEVEL)

실무에서 부서 조직간의 계층 구조를 레벨링하여 볼 수 있도록 해달라는 요청이 들어왔다. 팀장님께서 쿼리를 호로록 짜서 보여주시고, 이건 꼭 알아야하는 거라며 강조하셨는데 처음보는 START WITH ~ 와 CONNECT BY PRIOR의 등장에 이해하지 못한체 어리둥절했다😭 그래서 쉬운 예제를 찾아 정리해보기로 했다. 

 

레벨링을 하기 위해서는 계층형 쿼리와 기본적인 용어부터 알고 있으면 이해하기 쉬워진다. 

 

1. 계층형 쿼리

계층 구조를 가진 데이터를 쿼리하고 분석하는데 사용하는 SQL쿼리 → 트리 구조, 그래프 구조와 같은 데이터를 표현하고, 각 노드 사이의 관계를 검색하고 조작하는데 유용하다. 

EX ) 조직의 부서 계층, 각 부서의 부모-자식 관계 확인 / 제품의 카테고리 : 카테고리, 하위카테고리, 제품 그룹 분석 / 지리적 계층 : 지리 정보를 계층적으로 표현, 지역-하위지역 

 

 

2. 용어 

노드(NODE) : 항목 하나하나 

루트(ROOT) : 트리 구조에서 가장 위에 있는 노드, 조직도에서의 최상위 노드 

부모(PARENT) : 트리 구조에서 상위의 있는 노드

자식(CHILD) : 부모 아래 있는 노드

리프(LEAF) : 가장 하위에 있는 노드 

 

 

3. 예제 

▶ 조직도를 만들 테이블을 생성

CREATE TABLE COMPAY_TEST (
   vNAME VARCHAR(20) -- 이름
  ,NAME_CODE VARCHAR(20) -- 이름코드
  ,PRNT_CODE VARCHAR(20) -- 상위코드 
);

 

▶ 데이터를 삽입 

INSERT INTO COMPAY_TEST VALUES('회장','001',NULL);
INSERT INTO COMPAY_TEST VALUES('부회장','002','001');
INSERT INTO COMPAY_TEST VALUES('경영본부','003','002');
INSERT INTO COMPAY_TEST VALUES('사업본부','004','002');
INSERT INTO COMPAY_TEST VALUES('영업본부','005','002');
INSERT INTO COMPAY_TEST VALUES('경영팀장','006','003');
INSERT INTO COMPAY_TEST VALUES('경영부장','007','006');
INSERT INTO COMPAY_TEST VALUES('경영과장','008','007');
INSERT INTO COMPAY_TEST VALUES('사업팀장','009','004');
INSERT INTO COMPAY_TEST VALUES('사업부장','010','009');
INSERT INTO COMPAY_TEST VALUES('사업과장','011','010');
INSERT INTO COMPAY_TEST VALUES('영업팀장','012','005');
INSERT INTO COMPAY_TEST VALUES('영업부장','013','012');
INSERT INTO COMPAY_TEST VALUES('영업과장','014','013');

COMMIT;

 

 

 

 

▶ SELECT 조회

SELECT * 
 FROM COMPAY_TEST;

 

 

 

 

▶계층적인 SELECT 쿼리 조회 

- LEVEL을 활용하여 계층적으로 표현할 수 있다. 

SELECT
  LEVEL
  , LPAD(' ', 2*(LEVEL-1)) || vNAME AS "계층화이름" -- 계층화 이름
  , NAME_CODE -- 이름 코드 
  , PRNT_CODE -- 상위코드
FROM COMPAY_TEST
START WITH PRNT_CODE IS NULL
CONNECT BY PRIOR NAME_CODE = PRNT_CODE;

 

 

1. LPAD는 지정한 길이만큼 왼족부터 특정문자로 채우는 함수 LPAD("값", "문자길이", ["채움문자"]) 

    → LEVEL에 따라서 총 문자길이를 지정한다. (회장의 경우 LEVEL이 1이고, 2*(LEVEL-1)의 결과가 0이므로 들여쓰기는 없다. 부회장의 경우 LEVEL이 2이고, 수식의 결과로 2를 가져오기 때문에 총문자의 길이는 2로 잡히고 ' ' (공백)을 넣은 후 vName인 부회장을 붙인다.) 

 

😭 LPAD 의 첫번째 인자가 처음엔 공백이라서 잘 이해가가지 않았는데 공백대신 - 를 넣고 SELECT해보니 어떻게 쓰였는지 이해가 갔다. 

 

 

2. START WITH 최상위 루트 노드를 지정, 최상위 루트 노드인 회장의 부모노드를 NULL이므로 PRNT_CODE IS NULL로 지정 

 → 다른 조건으로 지정하고 싶다면? WHERE 구문과 같이 컬럼 = '값'으로 지정 

START WITH NAME_CODE = '002'

 

 

3. CONNECT BY 부모와 자식노드를 찾아서 연결 

 

 

 

4. PRIOR 이전 레코드에 대한 참조를 만들어 계층적인 관계를 정의, 자식 노드 쪽에 붙여서 작성 

CONNECT BY PRIOR 자식(본인)레코드 = 부모(상위)레코드

 

 

5. LEVEL  트리 내에서 어떤 단계에 있는지 나타내는 값, START WITH와 CONNECT BY를 사용하여 부모자식을 연결한 후 LEVEL로 보기 쉽게 계층화

 

 

 

 

참고자료 

챗GPT

https://sanbonclass.tistory.com/m/14 

[ORACLE] 오라클 LEVEL 계층형쿼리 (START WITH,CONNECT BY 활용)