[Oracle] 오라클 CONNECT BY 사용법 (계층형 쿼리)

오라클 SQL에서 CONNECT BY 절은 부서 조직도, 메뉴 트리 등 계층적 구조를 만들 때 주로 사용하는 방법이다. 그리고 CONNECT BY 절과 LEVEL을 사용하면 순차적인 데이터를 생성할 수가 있어서, 특정 값만큼 행을 늘리거나 시작일자와 종료일자 사이의 날짜를 채워서 행을 늘리는 등의 가상의 데이터를 만들 때 유용하게 사용할 수 있다. CONNECT BY 절의 사용법은 광범위하기 때문에, 아래는 실무에서 자주 사용하는 예시 몇 가지를 사용하여 설명하였다.

 

목차 계층형 쿼리 조회 하는 법 컬럼의 수치값만큼 행을 늘리는 방법 시작일자와 종료일자 사이 날짜 가져오기

 

계층형 쿼리 조회 하는 법

SELECT empno
     , ename
     , mgr
     , job
     , LEVEL
  FROM emp
CONNECT BY PRIOR empno = mgr
  START WITH mgr IS NULL

 

 

CONNECT BY PRIOR (상위코드) = (하위코드)

상위코드와 하위코드 위치가 바뀌면 역참조가 된다. CONNECT BY PRIOR (하위코드) = (상위코드) 

역참조로 계층을 만들 때는 START 위치를 가장 하위코드로 지정해야 한다.

 

START WITH (시작위치) IS NULL

mgr IS NULL은 KING(7839)부터 계층을 시작하라는 의미이다.

mgr = 7839로 시작위치를 지정하면 JONES(7566)부터 계층이 만들어진다.

 

SELECT empno
     , LPAD(' ', LEVEL * 2) || ename AS ename
     , mgr
     , job
     , LEVEL
  FROM emp
CONNECT BY PRIOR empno = mgr
  START WITH mgr IS NULL

 

 

첫 번째 예시를 조금 더 이해하기 쉽도록 LEVEL 만큼 들여 쓰기를 한 예제이다.

 

위의 결과를 보면 계층이 눈에 쉽게 보인다. 계층쿼리는 실무에서 많이 사용을 하지는 않지만 개념을 이해하고 있으면,  계층쿼리를 응용하여 쉽게 해결할 수 있는 상황이 많이 있다.

 

SELECT *
  FROM emp a
 WHERE a.empno IN (SELECT aa.empno
                     FROM emp aa
                  CONNECT BY PRIOR aa.empno = aa.mgr
                    START WITH aa.mgr = 7566)

 

 

위의 예제는 특정 직원의 하위에 있는 모든 직원을 조회하는 예제이다.

계층쿼리를 이용하면 하위 데이터를 쉽게 조회할 수 있다.

 

컬럼의 수치값만큼 행을 늘리는 방법

 SELECT LEVEL AS seq
   FROM dual
CONNECT BY LEVEL <= 10

 

 

위의 예제는 10번을 반복하여 1~10까지의 데이터 행을 생성하였다.

CONNECT BY는 특정 횟수만큼 반복하여 데이터를 생성할 수 있다.

 

WITH emp_tmp AS
(
 SELECT 7369 empno, 'SMITH' ename, 3 work_days FROM dual UNION ALL
 SELECT 7788 empno, 'SCOTT' ename, 5 work_days FROM dual UNION ALL
 SELECT 7902 empno, 'FORD'  ename, 2 work_days FROM dual
)

SELECT a.empno
     , a.ename
     , a.work_days
     , b.seq
  FROM emp_tmp a
 INNER JOIN (SELECT LEVEL AS seq FROM dual CONNECT BY LEVEL <= 10) b
    ON a.work_days >= b.seq
 ORDER BY a.empno, b.seq

 

 

바로 위의 예제를 응용하여, 1~10까지 데이터가 있는 인라인 뷰를 만든 다음 실제 테이블의 칼럼 값과 조인하면, 칼럼의 수치 값만큼 행을 증가시킬 수 있다.

 

위의 예제는 실무에서 종종 사용하는 방법이므로 이해하고 있으면 많은 도움이 될 것이다.

 

시작일자와 종료일자 사이 날짜 가져오기

SELECT TO_DATE('20240501','YYYYMMDD') + (LEVEL - 1) AS date_seq
  FROM dual
CONNECT BY LEVEL <= TO_DATE('20240515','YYYYMMDD') - TO_DATE('20240501','YYYYMMDD')+1

 

 

시작일자와 종료일자를 지정하여 날짜 뷰를 만들 때도 많이 사용한다. 날짜만 들어있는 테이블을 생성하여 사용하면 성능이 좋겠지만, 날짜 테이블이 없을 경우 임시로 날짜 뷰를 만들어서 사용할 수 있다.

 

WITH emp_tmp AS
(
 SELECT 7369 empno, 'SMITH' ename, TO_DATE('20240506', 'YYYYMMDD') fromdate, TO_DATE('20240508', 'YYYYMMDD') todate FROM dual UNION ALL
 SELECT 7788 empno, 'SCOTT' ename, TO_DATE('20240503', 'YYYYMMDD') fromdate, TO_DATE('20240507', 'YYYYMMDD') todate FROM dual UNION ALL
 SELECT 7902 empno, 'FORD'  ename, TO_DATE('20240509', 'YYYYMMDD') fromdate, TO_DATE('20240510', 'YYYYMMDD') todate FROM dual
)

SELECT b.date_seq
    , a.empno
    , a.ename
    , a.fromdate
    , a.todate
 FROM emp_tmp a
INNER JOIN (SELECT TO_DATE('20240501', 'YYYYMMDD') + (LEVEL - 1) AS date_seq
              FROM dual
           CONNECT BY LEVEL <= TO_DATE('20240515', 'YYYYMMDD') - TO_DATE('20240501', 'YYYYMMDD') + 1) b
   ON b.date_seq BETWEEN a.fromdate AND a.todate
ORDER BY b.date_seq, a.empno

 

 

바로 위의 예제를 응용하여 시작일자와 종료일자가 있는 데이터와 조인하면, 해당 기간의 구만만큼 날짜를 채워서 행을 증가시킬 수 있다.

 

위의 예제도 실무에서 많이 사용하는 예제이니 이해하고 있으면 언젠가 한 번쯤은 사용할 날이 있을 것이다.

 

WITH emp_tmp AS
(
 SELECT 7369 empno, 'SMITH' ename, TO_DATE('20240506', 'YYYYMMDD') fromdate, TO_DATE('20240508', 'YYYYMMDD') todate FROM dual UNION ALL
 SELECT 7788 empno, 'SCOTT' ename, TO_DATE('20240503', 'YYYYMMDD') fromdate, TO_DATE('20240507', 'YYYYMMDD') todate FROM dual UNION ALL
 SELECT 7902 empno, 'FORD'  ename, TO_DATE('20240509', 'YYYYMMDD') fromdate, TO_DATE('20240510', 'YYYYMMDD') todate FROM dual
)

SELECT b.date_seq
    , a.empno
    , a.ename
    , a.fromdate
    , a.todate
 FROM emp_tmp a
    , (SELECT TO_DATE('20240501', 'YYYYMMDD') + (LEVEL - 1) AS date_seq
         FROM dual
      CONNECT BY LEVEL <= TO_DATE('20240515', 'YYYYMMDD') - TO_DATE('20240501', 'YYYYMMDD') + 1) b
WHERE b.date_seq BETWEEN a.fromdate(+) AND a.todate(+)
ORDER BY b.date_seq, a.empno

 

 

날짜 뷰와 아우터 조인을 하면 데이터가 존재하지 않는 날짜까지 함께 조회할 수 있다. 위의 예제는 달력 같은 화면을 만들 때 사용할 수 있는 방법이다.

 

아래의 포스팅에 조금 더 많은 예제가 있으니 참고하기 바란다.

[Oracle] 오라클 콤마를 열, 행으로 분리하는 방법

[Oracle] 시작일자~종료일자 기간내 모든 날짜(일자) 구하기

[Oracle] 문자열 구분자를 행으로 분리 (Split)