1、)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
2、_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,
3、用于计算某一日期在当月的第几周,定义如下: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
4、startday:=substr(date1,1,6)| select to_char(to_date(startday,yyyymmdd),day) into xingqi from dual; select (case xingqi when 星期一 then 7星期二 then 6星期三 then 5星期四 then 4星期五 then 3星期六 then 2星期日 then 1 end ) into week_num from dual; select to_date(date1,)-to_date(startday,)+1 into day_num from dual; if day
5、_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,) AS DAY_DATE, substr(DATE_CODE,1,4) AS DAY_YEAR, to_char(to_date(DATE_CODE,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, ) + 1, ); end loop;EXCEPTION WHEN OTHERS THEN ROLLBACK;END;说明:START_DAY为要创建的维表的起始日期,END_DAY为要创建的维表的结束日期。接下来,在T
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1