建时间维度表（oracle）

第一步:新建数据库表如下：

-- 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;