BI分析中，经常需要将事实表与时间维度表关联起来，按年/月/日来逐层展示，常用的做法是创建一张日历表，结构类似如下：
createtableT_BAS_CALENDAR
(
d_yearNUMBER(4)notnull,
d_monthNUMBER(2)notnull,
d_dayNUMBER(2)notnull);
commentontableT_BAS_CALENDARis'日历表';
commentoncolumnT_BAS_CALENDAR.d_yearis'年';
commentoncolumnT_BAS_CALENDAR.d_monthis'月';
commentoncolumnT_BAS_CALENDAR.d_dayis'日';altertableT_BAS_CALENDARaddconstraintPK_BAS_CALENDARprimarykey(D_YEAR,D_MONTH,D_DAY);
但是如何向这张表批量插入日历数据，方法就很多了，下面是仅用SQL语言生成日历的参考方法：
1createorreplaceprocedureP_IMPORT_CALENDAR(p_year_startnumber,
2p_year_endnumber)is
3cmonthinteger;
4cyearinteger;
5cdayinteger;
6day_firstinteger;
7day_lastinteger;
8begin
9--生成从p_year_start到p_year_end的所有日历createdbyyjmyzz@-04-27
10
11--firstly,deletehistoryrecords
12deletefromT_BAS_CALENDARwhered_yearbetweenp_year_startandp_year_end;
13forcyearinp_year_start..p_year_endloop
14forcmonthin1..12loop
15--getfirst-dayofMonth
16selectto_number(cyear||lpad(cmonth,2,'0')||'01','99999999')
17intoday_first
18fromdual;
19--last-dayofMonth
20selectto_number(to_char(add_months(to_date(day_first,'yyyyMMdd'),1)-1,
21'yyyyMMdd'),
22'99999999')
23intoday_last
24fromdual;
25forcdayinday_first..day_lastloop
26--inserttotable
27INSERTINTOT_BAS_CALENDAR
28(D_YEAR,D_MONTH,D_DAY)
29VALUES
30(CYEAR,CMONTH,SUBSTR(cday,7));
31endloop;
32endloop;
33endloop;
34commit;
35endP_IMPORT_CALENDAR;
