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

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) )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 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 is 年份的第几周;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 number; /* 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 星期二 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)+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;接着，定义函数get_week_year，用于计算某一日期在当年的第几周，定义如下：create or replace function get_week_year(date1 in CHAR) return integer is result integer; startday char(8); xingqi char(9); /* 本年第一天是星期几 */ 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 (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)+1 into day_num from dual; if day_num=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_date(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); end loop; EXCEPTION WHEN OTHERS T