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

图解使用ORACLE存储过程创建时间维表 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 USERS PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 第 1 页 共 9 页 Superromeo原创文档 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; 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 '日'; 第 2 页 共 9 页 Superromeo原创文档 接着，定义函数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 第 3 页 共 9 页 Superromeo原创文档 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是本年的第几天 */ 第 4 页 共 9 页 Superromeo原创文档 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<=week_num then result:=1; else 第 5 页 共 9 页 Superromeo原创文档 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) 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, 第 6 页 共 9 页 Superromeo原创文档 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 THEN ROLLBACK; END; 第 7 页 共 9 页 Superromeo原创文档 说明:START_DAY为要创建的维表的起始日期，END_DAY为要创建的维表的结束日期。 接下来，在TOAD内调用该存储过程，具体如下图: 执行存储过程后，得到的维表如下图所示: 第 8 页 共 9 页 Superromeo原创文档 说明:2012年1月1日是星期日，按照我们中国的计算方式，是2012年的第1周 第 9 页 共 9 页