BI分析中，经常需要将事实表与时间维度表关联起来，按年/月/日来逐层展示，常用的做法是创建一张日历表，结构类似如下：
create table T_BAS_CALENDAR ( d_year NUMBER(4) not null, d_month NUMBER(2) not null, d_day NUMBER(2) not null ); comment on table T_BAS_CALENDAR is '日历表'; comment on column T_BAS_CALENDAR.d_year is '年'; comment on column T_BAS_CALENDAR.d_month is '月'; comment on column T_BAS_CALENDAR.d_day is '日'; alter table T_BAS_CALENDAR add constraint PK_BAS_CALENDAR primary key (D_YEAR, D_MONTH, D_DAY);
但是如何向这张表批量插入日历数据，方法就很多了，下面是仅用SQL语言生成日历的参考方法：
1 create or replace procedure P_IMPORT_CALENDAR(p_year_start number, 2 p_year_end number) is 3 cmonth integer; 4 cyear integer; 5 cday integer; 6 day_first integer; 7 day_last integer; 8 begin 9 --生成从p_year_start到p_year_end的所有日历 created by yjmyzz@126.com 2015-04-27 10 11 --firstly,delete history records 12 delete from T_BAS_CALENDAR where d_year between p_year_start and p_year_end; 13 for cyear in p_year_start .. p_year_end loop 14 for cmonth in 1 .. 12 loop 15 --get first-day of Month 16 select to_number(cyear || lpad(cmonth, 2, '0') || '01', '99999999') 17 into day_first 18 from dual; 19 --last-day of Month 20 select to_number(to_char(add_months(to_date(day_first, 'yyyyMMdd'), 1) - 1, 21 'yyyyMMdd'), 22 '99999999') 23 into day_last 24 from dual; 25 for cday in day_first .. day_last loop 26 --insert to table 27 INSERT INTO T_BAS_CALENDAR 28 (D_YEAR, D_MONTH, D_DAY) 29 VALUES 30 (CYEAR, CMONTH, SUBSTR(cday, 7)); 31 end loop; 32 end loop; 33 end loop; 34 commit; 35 end P_IMPORT_CALENDAR;
