第一步:建立資料庫表如下：
-- Create table
create table TIME_BY_DAY_DIMENSION
(
time_id NUMBER(10) not null,
the_date DATE,
the_day VARCHAR2(15),
the_month VARCHAR2(15),
the_year VARCHAR2(10),
day_of_month VARCHAR2(12),
week_of_year VARCHAR2(12),
month_of_year VARCHAR2(12),
quarter VARCHAR2(8),
time_name VARCHAR2(20)
)
;
-- Add comments to the table
comment on table TIME_BY_DAY_DIMENSION
is '時間次元表';
-- Add comments to the columns
comment on column TIME_BY_DAY_DIMENSION.time_id
is 'id';
comment on column TIME_BY_DAY_DIMENSION.the_date
is '日期';
comment on column TIME_BY_DAY_DIMENSION.the_day
is '星期幾';
comment on column TIME_BY_DAY_DIMENSION.the_month
is '月份';
comment on column TIME_BY_DAY_DIMENSION.the_year
is '年份';
comment on column TIME_BY_DAY_DIMENSION.day_of_month
is '日';
comment on column TIME_BY_DAY_DIMENSION.week_of_year
is '周';
comment on column TIME_BY_DAY_DIMENSION.month_of_year
is '月';
comment on column TIME_BY_DAY_DIMENSION.quarter
is '季度';
comment on column TIME_BY_DAY_DIMENSION.time_name
is '日期中文名';
-- Create/Recreate primary, unique and foreign key constraints
alter table TIME_BY_DAY_DIMENSION
add constraint PK_TIME_BY_DAY primary key (TIME_ID)
;
第二步,編寫存儲過程，往表裡面插入時間記錄
CREATE OR REPLACE PROCEDURE Create_time_by_day_dimension
IS
WeekString varchar(12);
dDate DATE;
sMonth varchar(20);
iYear varchar(20);
iDayOfMonth varchar(20);
iWeekOfYear varchar(20);
iMonthOfYear smallint;
iQUARTER varchar(20);
adddays int;
sTIME_NAME varchar(100);
i number;
BEGIN
adddays := 1 ;
dDate := to_date('01/01/1949','mm/dd/yyyy');
i:=14974;
WHILE (dDate < to_date('12/31/1989','mm/dd/yyyy'))
loop
i:=i+1;
WeekString := to_char(dDate, 'day'); --星期幾
iYear:= to_char(dDate, 'yyyy')||'年';--年
sMonth:=iYear||to_char(dDate, 'mm')||'月';--月份
iDayOfMonth:=to_char(dDate, 'dd')||'日';--日(字元型)
iWeekOfYear:= iYear||to_char(to_char(dDate,'fmww'),'00')||'周';--年的第幾周
iMonthOfYear:=to_number(to_char(dDate, 'mm'));--月(數字型)
iQUARTER:=iYear||to_char(dDate,'q')||'季度';--季度
sTIME_NAME:=to_char(dDate,'yyyy')||'年'||to_char(dDate,'MM')||'月'||to_char(dDate,'dd')||'日';
INSERT INTO time_by_day_dimension(TIME_ID,the_date, the_day, the_month, the_year, day_of_month, week_of_year,
month_of_year,QUARTER,TIME_NAME)
VALUES (i,dDate, WeekString, sMonth, iYear, iDayOfMonth, iWeekOfYear, iMonthOfYear,iQUARTER,sTIME_NAME);
dDate := dDate + adddays;
END loop;
end;