[Oracle]오라클 계층형 쿼리

오늘은 오라클에서 쓸 수 있는 계층형 쿼리에 대해

정리를 해보도록 하겠습니다

 

계층형 쿼리는 말 그대로 값들이 계층을 가지는 쿼리입니다.

우리 일상생활에서도 계층형 구조는 많이 볼 수 있습니다.

가족에서도 할아버지, 할머니 아래에 부모님과 삼촌,이모,고모 등이 있고,

그 아래에 나와 형제/자매, 사촌들이 있는 것도 

하나의 계층형 구조입니다.

 

회사와 같은 조직에서도 대표가 있고, 

그 아래 임원이 있고, 

임원이 관리하는 조직이 따로 있고 그런 것들과 같은 것입니다.

 

이러한 계층을 관리하고 조회하고 할 수 있는 쿼리를 정리를 해보도록 하겠습니다.

 

728x90

 

1. 데이터 준비

우선 데이터를 준비해야 합니다.

계층형 쿼리를 하기 위한 임시로 쓸 테이블을 만들어 보도록 하겠습니다.

 

CREATE TABLE EMP_TEST (
    EMPNO NUMBER PRIMARY KEY,
    ENAME VARCHAR2(20),
    MGR   NUMBER  --상위코드
);

 

위처럼 테이블을 만들었습니다.

그냥 임시로 쓸 테이블을 만든 것입니다.

 

 

그리고 임시로 쓸 데이터를 입력해보도록 하겠습니다.

아래처럼 임시로 쓸 데이터를 입력하였습니다.

 

INSERT INTO EMP_TEST VALUES (100, 'CEO', NULL);
INSERT INTO EMP_TEST VALUES (101, 'CTO', 100);
INSERT INTO EMP_TEST VALUES (102, 'CFO', 100);
INSERT INTO EMP_TEST VALUES (201, 'KIM', 101);
INSERT INTO EMP_TEST VALUES (202, 'HONG', 101);
INSERT INTO EMP_TEST VALUES (301, 'YUN', 201);
INSERT INTO EMP_TEST VALUES (302, 'PAK', 201);
INSERT INTO EMP_TEST VALUES (401, 'LEE', 202);

 

 

위처럼 데이터를 입력하였습니다.

 

이제 입력한 데이터를 확인해보도록 하겠습니다.

그냥 아래처럼 간단하게 조회를 해보도록 하겠습니다.

 

SELECT * FROM EMP_TEST
;

 

위처럼 해서 데이터를 한번 조회해보도록 하겠습니다.

그냥 데이터를 조회하는 것입니다.

위처럼 조회를 하면 결과는 아래처럼 나옵니다

 

 

위처럼 insert 한 것이 정상적으로 들어가져있는 것을

확인할 수 있습니다.

 

 

 

반응형

 

2. 계층형 데이터 확인하기

이제 위에서 만든 데이터를 바탕으로

계층구조를 확인해보도록 하겠습니다.

자신의 부모는 무엇이며, 나는 어디에 속해있는지,

내 레벨, 등급?위치?는 어디인지 확인해보도록 하겠습니다.

 

 

SELECT LEVEL, EMPNO, ENAME, MGR
FROM EMP_TEST
START WITH MGR IS NULL --최초 상위 노드
CONNECT BY PRIOR EMPNO = MGR  --상위노드와 현재 노드값 비교용
;

 

위처럼 쿼리를 작성하였습니다.

맨 최상위 부모 부터 해서

나와 부모를 찾을 것까지 되어있습니다.

위처럼 해서 하면 현재 나의 부모의 사번이 자신인 것들이 누구인지

그런 것들을 확인하는 것입니다.

 

 

위처럼 쿼리를 작성하고 실행을 하게 되면

결과는 아래처럼 나오게 됩니다.

 

위처럼 나오게 됩니다.

내 현재 레벨, 등급은 어디이며, 내 부모는 누구인지 확인할 수 있습니다.

 

SMALL

 

3. 계층 상세 확인하기

이번에는 계층에 대해서 더 상세적으로 확인해보도록 하겠습니다.

현재 내 레빌은 무엇이며, 내가 어떤 계층을 타고 내려와있는지 등을

확인할 수 있는 방법입니다.

 

 

쿼리를 아래처럼 작성하였습니다.

SELECT LEVEL, EMPNO, ENAME,
       SYS_CONNECT_BY_PATH(ENAME, ' > ') AS PATH --루트부터 현재까지 표시
FROM EMP_TEST
START WITH MGR IS NULL --최초 상위 노드
CONNECT BY PRIOR EMPNO = MGR --상위노드와 현재 노드값 비교용 
ORDER SIBLINGS BY ENAME --같은 부모를 가진 노드들끼리 정렬
;

 

 

위처럼 작성을 하면 됩니다.

주석을 참고해주시면 되십니다.

루트부터 현재 자신까지의 계층은 어떻게 되어있는지도 확인을 할 수 있는 것입니다.

 

 

 

 

위처럼 쿼리를 작성하였고,

실행을 하게 되면 결과는 아래처럼 나오게 됩니다.

 

 

 

위처럼 결과를 확인할 수 있습니다.

현재 자신의 계층은 어떻게 되어있으며,

내 상위 계층 리스트까지 볼 수가 있는 것입니다.

 

 

 

 

오늘은 오라클에서 사용할 수 있는

계층형구조에 대해 정리를 해보았습니다.

 

계층형구조는 자주 사용되지는 않을 수 있지만

그렇다고 많이 사용안되는 것도 아닙니다.

위에 얘기한 것처럼 가족도, 조직도, 카테고리 등등

여러가지 용도로 쓸 수가 있습니다.

그렇기 때문에 알고 있으면 좋을 것 같습니다

 

 

 

 

이상으로 Oracle에서 사용할 수 있는 계층형 구조에 대한 정리였습니다~

 

 

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.