图解使用ORACLE存储过程创建时间维表

1、superromeo原创文档在使用oracle的过程中，可能需要判断某一天是某个月的第几周，某一天是某一年的第几周。本人使用自定义函数，调用oracle存储过程创建时间维表，解决了该问题。首先，在数据库内创建维度表的表结构。建表语句如下：create table time_dim( day_key char(8) not null, day_date date, day_year char(4), day_quarter char(1), day_month char(2), week_inmonth char(1), week_inyear char(2), day_inmonth char

2、(2) )tablespace userspctused 0pctfree 10initrans 1maxtrans 255storage ( initial 64k minextents 1 maxextents unlimited pctincrease 0 buffer_pool default )logging nocompress nocachenoparallelmonitoring;comment on table time_dim is '时间维表'comment on column time_dim.day_key is '时间'comment

3、 on column time_dim.day_date is '日期'comment on column time_dim.day_year is '年份'comment on column time_dim.day_quarter is '季度'comment on column time_dim.day_month is '月份'comment on column time_dim.week_inmonth is '月份的第几周'comment on column time_dim.week_inyear i

4、s '年份的第几周'comment on column time_dim.day_inmonth is '日'接着，定义函数get_week_month，用于计算某一日期在当月的第几周，定义如下：create or replace function get_week_month(date1 in char) return integer is result integer; startday char(8); xingqi char(9); /* 本月第一天是星期几 */ week_num number; /* 本月的第一周一共有几天 */ day_num nu

5、mber; /* date1是这个月的第几天 */ day_num2 number;begin if substr(date1,7,2)='01' then result:=1; else startday:=substr(date1,1,6)|'01' select to_char(to_date(startday,'yyyymmdd'),'day') into xingqi from dual; select (case xingqi when '星期一' then 7 when '星期二' t

6、hen 6 when '星期三' then 5 when '星期四' then 4 when '星期五' then 3 when '星期六' then 2 when '星期日' then 1 end ) into week_num from dual; select to_date(date1,'yyyymmdd')-to_date(startday,'yyyymmdd')+1 into day_num from dual; if day_num<=week_num then

7、result:=1; else day_num2:=day_num-week_num; select ceil(day_num2/7)+1 into result from dual; end if; end if; return(result); end;接着，定义函数get_week_year，用于计算某一日期在当年的第几周，定义如下：create or replace function get_week_year(date1 in char) return integer is result integer; startday char(8); xingqi char(9); /* 本年

8、第一天是星期几 */ week_num number; /* 本年的第一周一共有几天 */ day_num number; /* date1是本年的第几天 */ day_num2 number;begin if substr(date1,5,4)='0101' then result:=1; else startday:=substr(date1,1,4)|'0101' select to_char(to_date(startday,'yyyymmdd'),'day') into xingqi from dual; select

9、(case xingqi when '星期一' then 7 when '星期二' then 6 when '星期三' then 5 when '星期四' then 4 when '星期五' then 3 when '星期六' then 2 when '星期日' then 1 end ) into week_num from dual; select to_date(date1,'yyyymmdd')-to_date(startday,'yyyymmdd

10、9;)+1 into day_num from dual; if day_num<=week_num then result:=1; else day_num2:=day_num-week_num; select ceil(day_num2/7)+1 into result from dual; end if; end if; return(result); end;在pl/sql内编译以上两个函数后，在pl/sql内编译如下的存储过程：create or replace procedure creaetetimedim(start_day in char,end_day in char

11、) is date_code varchar2(8);begin date_code:= start_day; while(to_date(end_day,'yyyymmdd')-to_date(date_code,'yyyymmdd')>=0 ) loop insert into time_dim (day_key, day_date, day_year, day_quarter, day_month, week_inmonth, week_inyear, day_inmonth ) select date_code as day_key, to_dat

12、e(date_code,'yyyymmdd') as day_date, substr(date_code,1,4) as day_year, to_char(to_date(date_code,'yyyymmdd'),'q') as day_quarter, substr(date_code,5,2) as day_month, get_week_month(date_code) as week_inmonth, get_week_year(date_code) as week_inyear, substr(date_code,7,2) as day_inmonth from dual; commit; date_code := to_char(to_date(date_code, 'yyyymmdd') + 1, 'yyyymmdd&