图解使用ORACLE存储过程创建时间维表Word格式文档下载.doc
《图解使用ORACLE存储过程创建时间维表Word格式文档下载.doc》由会员分享,可在线阅读,更多相关《图解使用ORACLE存储过程创建时间维表Word格式文档下载.doc(9页珍藏版)》请在冰豆网上搜索。
)
TABLESPACEUSERS
PCTUSED0
PCTFREE10
INITRANS1
MAXTRANS255
STORAGE(
INITIAL64K
MINEXTENTS1
MAXEXTENTSUNLIMITED
PCTINCREASE0
BUFFER_POOLDEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
commentontableTIME_DIMis'
时间维表'
;
commentoncolumnTIME_DIM.DAY_KEYis'
时间'
commentoncolumnTIME_DIM.DAY_DATEis'
日期'
commentoncolumnTIME_DIM.DAY_YEARis'
年份'
commentoncolumnTIME_DIM.DAY_QUARTERis'
季度'
commentoncolumnTIME_DIM.DAY_MONTHis'
月份'
commentoncolumnTIME_DIM.WEEK_INMONTHis'
月份的第几周'
commentoncolumnTIME_DIM.WEEK_INYEARis'
年份的第几周'
commentoncolumnTIME_DIM.DAY_INMONTHis'
日'
接着,定义函数get_week_month,用于计算某一日期在当月的第几周,定义如下:
createorreplacefunctionget_week_month(date1inCHAR)returnintegeris
resultinteger;
startdaychar(8);
xingqichar(9);
/*本月第一天是星期几*/
week_numnumber;
/*本月的第一周一共有几天*/
day_numnumber;
/*date1是这个月的第几天*/
day_num2number;
begin
ifsubstr(date1,7,2)='
01'
then
result:
=1;
else
startday:
=substr(date1,1,6)||'
selectto_char(to_date(startday,'
yyyymmdd'
),'
day'
)intoxingqifromdual;
select(casexingqi
when'
星期一'
then7
星期二'
then6
星期三'
then5
星期四'
then4
星期五'
then3
星期六'
then2
星期日'
then1
end
)intoweek_num
fromdual;
selectto_date(date1,'
)-to_date(startday,'
)+1intoday_numfromdual;
ifday_num<
=week_numthen
result:
else
day_num2:
=day_num-week_num;
selectceil(day_num2/7)+1intoresultfromdual;
endif;
endif;
return(result);
end;
接着,定义函数get_week_year,用于计算某一日期在当年的第几周,定义如下:
createorreplacefunctionget_week_year(date1inCHAR)returnintegeris
/*本年第一天是星期几*/
/*本年的第一周一共有几天*/
/*date1是本年的第几天*/
ifsubstr(date1,5,4)='
0101'
=substr(date1,1,4)||'
在PL/SQL内编译以上两个函数后,在PL/SQL内编译如下的存储过程:
CREATEORREPLACEPROCEDURECREAETETIMEDIM(START_DAYINCHAR,END_DAYINCHAR)IS
DATE_CODEVARCHAR2(8);
BEGIN
DATE_CODE:
=START_DAY;
WHILE(TO_DATE(END_DAY,'
)-TO_DATE(DATE_CODE,'
)>
=0)loop
INSERTINTOTIME_DIM
(DAY_KEY,
DAY_DATE,
DAY_YEAR,
DAY_QUARTER,
DAY_MONTH,
WEEK_INMONTH,
WEEK_INYEAR,
DAY_INMONTH
)
selectDATE_CODEasDAY_KEY,
to_date(DATE_CODE,'
)ASDAY_DATE,
substr(DATE_CODE,1,4)ASDAY_YEAR,
to_char(to_date(DATE_CODE,'
q'
)ASDAY_QUARTER,
substr(DATE_CODE,5,2)ASDAY_MONTH,
get_week_month(DATE_CODE)ASWEEK_INMONTH,
get_week_year(DATE_CODE)ASWEEK_INYEAR,
substr(DATE_CODE,7,2)ASDAY_INMONTH
fromdual;
commit;
DATE_CODE:
=TO_CHAR(TO_DATE(DATE_CODE,'
)+1,'
);
endloop;
EXCEPTION
WHENOTHERSTHEN
ROLLBACK;
END;
说明:
START_DAY为要创建的维表的起始日期,END_DAY为要创建的维表的结束日期。
接下来,在T