[Oracle] 연속으로 된 날짜 만들기 (CONNECT BY LEVEL 활용)

데이터베이스를 사용하다 보면 연속으로 된 날짜가 필요한 경우가 있습니다.

(월 단위, 주 단위 날짜 등)

 

오라클 DB에서 제공하는 CONNECT BY LEVEL 쿼리를 사용해 연속으로 된 날짜를 만들어 보겠습니다.

아래 내용을 보시면 알겠지만 CONNECT BY LEVEL 쿼리는 반복문처럼 활용할 수 있습니다.

---

내용 : 연속으로 된 날짜 생성

아래 사진과 같이 매주 금요일, 매월 1일 등을 생성할 수 있습니다.

'2021년의 매주 금요일, 매월 1일 날짜 데이터 생성

주요 사용 쿼리 및 함수 목록

• CONNECET BY LEVEL : 계층형 쿼리로 날짜 연산에 사용
• TO_DATE('문자열', '날짜포맷') : 문자열을 날짜로 변환 ex) TO_DATE('20210101', 'YYYMMDD')
• TO_CHAR('내용', '포맷') : 숫자/날짜 등을 문자열로 변환 ex) TO_CHAR(날짜데이터, 'YYYY-MM-DD')
• ADD_MONTHS(DATE, N) : 주어진 DATE에 N만큼의 MONTH를 더합니다.
• ex) ADD_MONTH(2021-01-01, 1) → 2021-02-01 출력 

 

CONNECT BY LEVEL 과 사칙연산 사용해보기

• CONNECT BY LEVEL <= 10 쿼리로 LEVEL 이 1부터 10까지 출력되고, 출력된 LEVEL은 사칙연산이 가능합니다.

-- CONNECT BY LEVEL 사칙연산 예제
SELECT LEVEL, LEVEL*2, LEVEL*100, LEVEL*LEVEL FROM DUAL
CONNECT BY LEVEL <= 10;

CONNECT BY LEVEL 사칙연산 결과 

매주 금요일 날짜 출력하기

• 오라클 DB에서 TO_DATE로 변환한 DATE TYPE은 +-로 사칙연산이 가능한 것을 응용합니다
• EX) (TO_DATE('2021-01-01', 'YYYYMMDD') + 1)을 하면 2021-01-02가 됩니다.
• 기타 TIP : 기본 날짜가 21/01/08 이런 식으로 나오는 이유는 오라클의 NLS_DATE_FORMAT 설정 때문입니다.
• 만약 날짜 형식을 변경하고 싶다면 TO_CHAR('데이터', '포맷') 함수를 이용하시면 됩니다 (아래 월 단위 출력에 예제 쿼리 有).

-- 2021-01-01 7일 이후 데이터를 출력하는 쿼리
SELECT (TO_DATE('20210101', 'YYYYMMDD') + (LEVEL*7)) AS PRINT_FRIDAY FROM DUAL
CONNECT BY LEVEL <= 12;

-- !TIP : 날짜가 21/01/08 형태로 나오는 이유
-- 오라클의 NLS_DATE_FORMAT이 RR/MM/DD 로 되어 있기 때문 입니다.
-- TO_CHAR로 변경하지 않은 기본 DATE는 NLS_DATE_FORMAT을 따릅니다.

SELECT SYSDATE FROM DUAL;
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

금요일 출력 결과

 

매월 1일 출력하기

• ADD_MONTHS(날짜, 숫자) 함수를 활용 숫자만큼 MONTH를 더해 2021년의 매월 1일을 출력합니다.
• 마지막에는 TO_CHAR(데이터, 포맷) 함수를 사용해 데이터 형태를 바꿔 줍니다.

-- ADD_MONTHS(날짜, 숫자)를 이용해 숫자만큼 MONTH를 더합니다.
-- 마지막에는 TO_CHAR로 데이터포맷을 'YYYY-MM-DD'로 변경합니다.
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('202101', 'YYYYMM'), LEVEL - 1), 'YYYY-MM-DD') AS MONTH_LIST FROM DUAL
CONNECT BY LEVEL <= 12;

매월 1일 출력하기

 

아래에 예제 쿼리 파일도 첨부드립니다.

 

CONNECT_BY_LEVEL_연습.sql

0.00MB

 

기타 궁금하신 사항은 편하게 댓글 남겨주세요.

 

감사합니다.