oracle:如何用sql生成日历

菩提树下的杨过

发布于 2018-01-19 10:59:57

1.5K0

发布于 2018-01-19 10:59:57

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;