오라클 순번 채번 CONNECT BY LEVEL 활용하기

안녕하세요.

오라클 디비 이용 시 알아두면 유용한 쿼리가 [CONNECT BY LEVEL]입니다.

 

Connect By Start With는 계층 쿼리로 상하 관계를 질의하는 데 사용하는데요.

LEVEL은 순위를 의미합니다.

이를 응용한 CONNECT BY LEVEL은 연속된 숫자를 조회할 때 활용하는데요.

예제를 통해 보시면 금방 이해가 되실 겁니다.

 

 

1~10까지 연속된 숫자 조회

 

   SELECT LEVEL AS NO

     FROM DUAL

CONNECT BY LEVEL <=10

 

 

2020년 1월부터 12월까지 출력

   SELECT '2020 년 '||LPAD(LEVEL, 2, 0)||'월' AS NO
    FROM DUAL
CONNECT BY LEVEL <=12

 

 

 

특정 날짜 구간 조회하기

검색조건의 From ~ To 사이의 날짜 리스트를 구할수도 있습니다.

ex) From : 2020년 07월 1일  / To : 2020년 07월 17일

 

▼ 쿼리

 SELECT TO_DATE('20200701', 'YYYYMMDD') + (LEVEL-1) AS DT
   FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('20200717', 'YYYYMMDD') - TO_DATE('20200701', 'YYYYMMDD')) + 1

---------------------------------------------------------------------------------------------

WITH WD AS
(
    SELECT TO_DATE('20200701', 'YYYYMMDD') AS FROM_DT   -- 시작일자
         , TO_DATE('20200717', 'YYYYMMDD') AS TO_DT     -- 종료일자    
      FROM DUAL
)
SELECT DT, DT2
  FROM ( 
     SELECT TO_CHAR(FROM_DT + (LEVEL-1), 'YYYYMMDD') AS DT
          , TO_CHAR(FROM_DT + (LEVEL-1), 'YYYY-MM-DD') AS DT2
       FROM WD
    CONNECT BY LEVEL <= (TO_DT - FROM_DT) + 1
  )

 

▼ 결과

 

 

데이터 복제

대량의 테스트 데이터를 만들거나 동일 데이터를 여러 건 만들 때 유용할 수 있습니다.

관계형 데이터 베이스에서는 연결(조인) 고리에 의해 데이터를 확장해서 만들 수 있습니다.

한 가지 공식은 다음과 같이 카테시안 곱만큼 데이터(행, 레코드, 듀플)가 생성이 됩니다.

 

▶ 1 * N = N 만큼의 행(레코드)

▶ N * M = N * M만큼의 행(레코드)

 

연결(조인) 고리를 무시하면 N*M 만큼의 데이가 생성이 되어 복제가 됩니다.

 

* EMP 데이터를 연결(조인) 고리를 주지 않고 LEVEL을 2로 하여 EMP데이터를 2배 복제하였습니다.