oracle_日期维度表插入数据

---------------------------------------天-------------------------------------------------------------
INSERT INTO DIM_PERIOD
SELECT TO_NUMBER(TO_CHAR(CURRDATE, 'YYYYMMDD')) AS PERIOD_ID,
       '天' AS  PERIOD_LEVEL,
    CURRDATE AS DAY_DATE,
    TO_NUMBER(TO_CHAR(CURRDATE, 'YYYYMMDD')) AS DAY_NUM,
    TO_CHAR(CURRDATE, 'YYYYMMDD') AS YMDAY,
    TO_CHAR(CURRDATE, 'YYYYMM') AS YMON,
    TO_CHAR(CURRDATE, 'YYYY') AS YEAR,
   -- TO_CHAR(CURRDATE, 'DDD') AS DAY_NBR_IN_YEAR,
    TO_CHAR(CURRDATE, 'DD') AS DAY_NBR_IN_MON,
    TO_CHAR(CURRDATE, 'W') AS WEEK_NBR_IN_MON,
    TO_CHAR(CURRDATE, 'WW') AS WEEK_NBR_IN_YEAR,
    TO_CHAR(CURRDATE, 'MM')  AS MON_NBR_IN_YEAR,
    TO_CHAR(CURRDATE, 'Q') AS  QTR_NBR_IN_YEAR,
    TO_CHAR(CURRDATE, 'YYYY')||'年' AS YEAR_NAME,
    TO_CHAR(CURRDATE, 'Q')||'季度'   AS QTR_NAME,
    TO_CHAR(CURRDATE, 'MM')||'月' AS MON_DESC,
       TO_CHAR(CURRDATE, 'Day') AS  WEEK_NAME, 
       CASE
         WHEN TO_NUMBER(TO_CHAR(CURRDATE, 'MM')) <= 6 THEN
          '上半年'
         ELSE
          '下半年'
       END AS  HF_YEAR_FLG,
   CASE
    WHEN TO_CHAR(CURRDATE,'YYYYMMDD')=TO_CHAR(LAST_DAY(CURRDATE),'YYYYMMDD') THEN
     '是'
    ELSE
     '不是'
   END AS  MON_LAST_DAY_FLAG,
   NULL,
   NULL,
   NULL,
   TO_CHAR(SYSDATE,'YYYYMMDD') AS UPD_DATE_SK,
   NULL,
   NULL,
   SYSDATE,
   SYSDATE,
   TO_CHAR(SYSDATE,'YYYYMMDD') AS INS_DATE_SK
FROM
(SELECT LEVEL N,
              
               TO_DATE('2011-12-31', 'YYYY-MM-DD') +
              
               NUMTODSINTERVAL(LEVEL, 'day') CURRDATE
       
          FROM DUAL
       
        CONNECT BY LEVEL <= 8000)
;
COMMIT;

------------------------------------------------月----------------------------------------------------

INSERT INTO DIM_PERIOD
SELECT DISTINCT TO_NUMBER(TO_CHAR(CURRDATE, 'YYYYMM')) AS PERIOD_ID,
       '月' AS  PERIOD_LEVEL,
    NULL,NULL,NULL,
    TO_CHAR(CURRDATE, 'YYYYMM') AS YMON,
    TO_CHAR(CURRDATE, 'YYYY') AS YEAR,
    NULL,NULL,NULL,
    TO_CHAR(CURRDATE, 'MM')  AS MON_NBR_IN_YEAR,
    TO_CHAR(CURRDATE, 'Q') AS  QTR_NBR_IN_YEAR,
    TO_CHAR(CURRDATE, 'YYYY')||'年' AS YEAR_NAME,
    TO_CHAR(CURRDATE, 'Q')||'季度'   AS QTR_NAME,
    TO_CHAR(CURRDATE, 'MM')||'月' AS MON_DESC,
    NULL,
       CASE
         WHEN TO_NUMBER(TO_CHAR(CURRDATE, 'MM')) <= 6 THEN
          '上半年'
         ELSE
          '下半年'
       END AS  HF_YEAR_FLG,
   NULL,
   NULL,
   NULL,
   NULL,
   TO_CHAR(SYSDATE,'YYYYMMDD') AS UPD_DATE_SK,
   NULL,
   NULL,
   SYSDATE,
   SYSDATE,
   TO_CHAR(SYSDATE,'YYYYMMDD') AS INS_DATE_SK
FROM
(SELECT LEVEL N,
              
               TO_DATE('2011-12-31', 'YYYY-MM-DD') +
              
               NUMTODSINTERVAL(LEVEL, 'day') CURRDATE
       
          FROM DUAL
       
        CONNECT BY LEVEL <= 8000)
;
COMMIT;

------------------------------------------------年----------------------------------------------------

INSERT INTO DIM_PERIOD
SELECT DISTINCT TO_NUMBER(TO_CHAR(CURRDATE, 'YYYY')) AS PERIOD_ID,
       '年' AS  PERIOD_LEVEL,
    NULL,NULL,NULL, NULL,
    TO_CHAR(CURRDATE, 'YYYY') AS YEAR,
    NULL,NULL,NULL, NULL,NULL,
    TO_CHAR(CURRDATE, 'YYYY')||'年' AS YEAR_NAME,
    NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,
   TO_CHAR(SYSDATE,'YYYYMMDD') AS UPD_DATE_SK,
   NULL,
   NULL,
   SYSDATE,
   SYSDATE,
   TO_CHAR(SYSDATE,'YYYYMMDD') AS INS_DATE_SK
FROM
(SELECT LEVEL N,
              
               TO_DATE('2011-12-31', 'YYYY-MM-DD') +
              
               NUMTODSINTERVAL(LEVEL, 'day') CURRDATE
       
          FROM DUAL
       
        CONNECT BY LEVEL <= 8000)
;
COMMIT;