오라클 달력 및 날짜 테이블

오라클에서 통계 관련 쿼리를 작성하다보면,

기간성 정보 기준으로 데이터를 도출해야할 경우가 있다. 예를 들자면 (일간, 월간, 년간)

하지만 영업일이 없었던 경우 0 원으로 표시해야하거나 주말같은 경우는 뭔가 표시를 해주어야 하는데,

이게 필요할 때 찾으면 없거나 보통 귀찮은일이 아님.

해서 테이블부터 데이터 등록 PL/SQL 까지 같이 올림. 그냥 바로 돌리시면 됩니다.^^

CREATE TABLE DATE_STANDARD
(	
  YEAR_ID CHAR(4 BYTE), 
	MONTH_ID CHAR(2 BYTE), 
	DAY_ID CHAR(2 BYTE), 
	DATE_ID VARCHAR2(8 BYTE), 
	WEEK_NO CHAR(1 BYTE), 
	WEEK_TXT VARCHAR2(5 BYTE)
)  

CREATE INDEX IDX_DATE_STANDARD ON DATE_STANDARD (YEAR_ID, MONTH_ID, DAY_ID, DATE_ID) ;
COMMIT;

SET SERVEROUTPUT ON;
DECLARE
  i NUMBER := 1;
  yyyy CHAR(4) := '2019';
  mm CHAR(2);
BEGIN
  
  FOR i IN 1..12 LOOP
  
    mm := TO_CHAR(LPAD(i, 2, 0));
    
    INSERT INTO DATE_STANDARD
    SELECT
      SUBSTR(DS.DATE_ID, 0, 4) AS YEAR_ID,
      SUBSTR(SUBSTR(DS.DATE_ID, -4), 1, 2) AS MONTH_ID,
      SUBSTR(DS.DATE_ID, -2) AS DAY_ID,
      DS.DATE_ID,
      TO_CHAR(TO_DATE(DS.DATE_ID), 'd') AS WEEK_NO,
      DECODE( TO_CHAR(TO_DATE(DS.DATE_ID), 'd') , 1, '일', 2, '월', 3, '화', 4, '수', 5, '목', 6, '금', 7, '토') AS WEEK_TXT    
    FROM (
      SELECT      
        TO_CHAR(TO_DATE(yyyy || mm ||'01', 'YYYYMMDD')+LEVEL - 1, 'YYYYMMDD') AS DATE_ID
      FROM DUAL
      CONNECT BY LEVEL <= (LAST_DAY(TO_DATE(yyyy || mm ||'01', 'YYYYMMDD')) - TO_DATE(yyyy || mm ||'01', 'YYYYMMDD') + 1)
    ) DS;
    
    DBMS_OUTPUT.PUT_LINE(mm || ' 월 데이터 등록 완료');
    
  END LOOP;  
  
  COMMIT;
  
END;