oracle时间维度表创建

在这里插入代码片create table TIME_DIMENSION
(
the_date NUMBER not null,
date_name NVARCHAR2(15),
the_year NUMBER,
year_name NVARCHAR2(10),
the_quarter VARCHAR2(10),
quarter_name NVARCHAR2(10),
the_month NUMBER,
month_name NVARCHAR2(10),
the_week NUMBER,
week_name NVARCHAR2(10),
week_day NVARCHAR2(10),
the_half number,
half_name NVARCHAR2(10)
)

/*
update TIME_DIMENSION set the_half = case when the_quarter like ‘%Q1’ or the_quarter like ‘%Q2’ then 1 else 2 end

update TIME_DIMENSION set half_name = case when the_half = 1 then year_name || ‘上半年’ else year_name || ‘下半年’ end

select * from TIME_DIMENSION order by the_date

delete TIME_DIMENSION

drop PROCEDURE SP_CREATE_TIME_DIMENSION;
*/

CREATE OR REPLACE PROCEDURE SP_CREATE_TIME_DIMENSION(begin_date in varchar2,
end_date in varchar2) is

/*SP_CREATE_TIME_DIMENSION: 生成时间维数据
begin_date: 起始时间
end_date:结束时间
*/
dDate date;
v_the_date number;
v_the_year varchar2(4);
v_the_quarter varchar2(2);
v_the_month varchar2(10);
v_the_month2 varchar2(2);
v_the_week varchar2(2);
v_the_day varchar2(10);
v_the_day2 varchar2(2);
v_week_day nvarchar2(10);
v_the_half nvarchar2(10);
v_half_year nvarchar2(10);

adddays int;
BEGIN
adddays := 1 ;
dDate := to_date(begin_date,‘yyyymmdd’);

WHILE (dDate <= to_date(end_date,‘yyyymmdd’))
loop
v_the_date := to_number(to_char(dDate,‘yyyymmdd’));–key值
v_the_year:= to_char(dDate, ‘yyyy’);–年
v_the_quarter := to_char(dDate, ‘q’);–季度
v_the_month:=to_char(dDate, ‘mm’);–月份(字符型)
v_the_month2:=to_number(to_char(dDate, ‘mm’));–月份(数字型)
v_the_day:=to_char(dDate, ‘dd’);–日(字符型)
v_the_day2:=to_char(dDate, ‘dd’);
v_the_week:= to_char(dDate,‘fmww’);–年的第几周
v_week_day := to_char(dDate, ‘day’); --星期几
v_the_half:= case when to_char(dDate, ‘q’) in (1,2) then 1 else 2 end;
v_half_year:= case when to_char(dDate, ‘q’) in (1,2) then ‘上半年’ else ‘下半年’ end;

insert into time_dimension(the_date,date_name,the_year,year_name,
the_quarter,quarter_name,the_month,
month_name,the_week,week_name,week_day,the_half,half_name)
values(v_the_date,v_the_year||‘年’||v_the_month2||‘月’||v_the_day2||‘日’,v_the_year,v_the_year||‘年’,
v_the_year||‘Q’||v_the_quarter,v_the_year||‘年’||v_the_quarter||‘季度’,to_number(v_the_year||v_the_month),
v_the_year||‘年’||v_the_month2||‘月’,v_the_week,‘第’||v_the_week||‘周’,
v_week_day,v_the_half,v_the_year||v_half_year);

dDate := dDate + adddays;
END loop;
end SP_CREATE_TIME_DIMENSION;