Oracle 資料倉儲簡單時間維度表的建立

create table ELMP_TRANS_DATE(c_date date,c_week varchar2(12),c_month varchar2(12),c_quarter varchar2(15),c_year varchar2(12));

alter table ELMP_TRANS_DATE add primary key(c_date);

insert into ELMP_TRANS_DATE
  select c_date,
         to_char(c_date, 'YYYY') || '年第' || to_char(c_date, 'WW') || '周' c_week,
         to_char(c_date, 'YYYY') || '年第' || to_char(c_date, 'MM') || '月' c_month,
         to_char(c_date, 'YYYY') || '年第' || to_char(c_date, 'Q') || '季度' c_quarter,
         to_char(c_date, 'YYYY') || '年' c_year
    from (select to_date('2010-01-01', 'yyyy-mm-dd') + level - 1 c_date
            from dual
          connect by rownum <= to_date('2040-12-31', 'yyyy-mm-dd') -
                     to_date('2010-01-01', 'yyyy-mm-dd') + 1)
   order by 1;