Oracle生成日期维度

标签：date -- number sys 维度日期 Oracle end

一、创建一张表用于保存日期维度数据

日期维度表

create table t_date
(
    sys_date number primary key not null ,--系统日期
    year number not null ,--年份
    year_start number not null ,--年初日期
    year_end number not null ,--年末日期
    last_year number not null ,--上年年份
    last_year_start number not null ,--上年年初日期
    last_year_end number not null ,--上年年末日期
    quarter number not null ,--季度
    quarter_start number not null ,--季初日期
    quarter_end number not null ,--季末日期
    last_quarter_start number not null ,--上季季初日期
    last_quarter_end number not null ,--上季季末日期
    next_quarter_start number not null ,--下季季初日期
    next_quarter_end number not null ,--下季季末日期
    month number not null ,--月份
    month_start number not null ,--月初日期
    month_end number not null ,--月末日期
    last_month_start number not null ,--上月月初日期
    last_month_end number not null ,--上月月末日期
    next_month_start number not null ,--下月月初日期
    next_month_end number not null ,--下月月末日期
    year_week number not null ,--周年数(本年度第几周)
    month_week number not null ,--月周数(本月第几周)
    day_week number not null --周数(星期几)
);

二、创建相关日期计算函数

计算年初日期

create or replace function f_get_year_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算年初日期*/
    begin
        return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy'),'yyyyMMdd'));
    end;

计算年末日期

create or replace function f_get_year_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算年末日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy'),12)-1,'yyyyMMdd'));
    end;

计算上年年初日期

create or replace function f_get_last_year_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算上年年初日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy'),-12),'yyyyMMdd'));
    end;

计算上年年末日期

create or replace function f_get_last_year_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算上年年末日期*/
    begin
        return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy')-1,'yyyyMMdd'));
    end;

计算季度

create or replace function f_get_quarter
(
    sys_date in number --系统日期
)
return number
as
    /*计算季度*/
    begin
        return to_number(to_char(to_date(sys_date,'yyyyMMdd'),'Q'));
    end;

计算季初日期

create or replace function f_get_quarter_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算季初日期*/
    begin
        return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),'yyyyMMdd'));
    end;

计算季末日期

create or replace function f_get_quarter_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算季末日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),3)-1,'yyyyMMdd'));
    end;

计算上季季初日期

create or replace function f_get_last_quarter_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算上季季初日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),-3),'yyyyMMdd'));
    end;

计算上季季末日期

create or replace function f_get_last_quarter_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算上季季末日期*/
    begin
        return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'Q')-1,'yyyyMMdd'));
    end;

计算下季季初日期

create or replace function f_get_next_quarter_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算下季季初日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),3),'yyyyMMdd'));
    end;

计算下季季末日期

create or replace function f_get_next_quarter_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算下季季末日期*/
    begin
        return f_get_quarter_end(f_get_next_quarter_start(sys_date));
    end;

计算月初日期

create or replace function f_get_month_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算月初日期*/
    begin
        return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),'yyyyMMdd'));
    end;

计算月末日期

create or replace function f_get_month_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算月末日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),1)-1,'yyyyMMdd'));
    end;

计算上月月初日期

create or replace function f_get_last_month_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算上月月初日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),-1),'yyyyMMdd'));
    end;

计算上月月末日期

create or replace function f_get_last_month_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算上月月末日期*/
    begin
        return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'MM')-1,'yyyyMMdd'));
    end;

计算下月月初日期

create or replace function f_get_next_month_start
(
    sys_date in number --系统日期
)
return number
as
    /*计算下月月初日期*/
    begin
        return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),1),'yyyyMMdd'));
    end;

计算下月月末日期

create or replace function f_get_next_month_end
(
    sys_date in number --系统日期
)
return number
as
    /*计算下月月末日期*/
    begin
        return f_get_month_end(f_get_next_month_start(sys_date));
    end;

计算本周第一天日期

create or replace function f_get_monday
(
    sys_date in number --系统日期
)
return number
as
    /*计算本周第一天日期(星期一)*/
    v_sunday number;
    begin
        v_sunday:=to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'D'),'yyyyMMdd'));
        if sys_date=v_sunday then /*如果当前日期是星期日,则本周第一天日期为当前日期-6*/
            return to_number(to_char(to_date(sys_date,'yyyyMMdd')-6,'yyyyMMdd'));
        else
            return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'D')+1,'yyyyMMdd'));
        end if;
    end;

计算两个日期之间的天数差

create or replace function f_get_days
(
    start_date in number ,--起始日期
    end_date in number --结束日期
)
return number
as
    /*计算两个日期间的天数差*/
    begin
        return abs(to_date(end_date,'yyyyMMdd')-to_date(start_date,'yyyyMMdd'));
    end;

计算年周数(当前日期在本年度的第几周)

create or replace function f_get_year_week
(
    sys_date in number --系统日期
)
return number
as
    /*计算年周数(本年度第几周)*/
    begin
        return f_get_days(f_get_monday(f_get_year_start(sys_date)),f_get_monday(sys_date))/7+1;
    end;

计算月周数(当前日期在本月的第几周)

create or replace function f_get_month_week
(
    sys_date in number --系统日期
)
return number
as
    /*计算月周数(本月第几周)*/
    begin
        return f_get_days(f_get_monday(f_get_month_start(sys_date)),f_get_monday(sys_date))/7+1;
    end;

计算周数(当前日期是星期几)

create or replace function f_get_day_week
(
    sys_date in number --系统日期
)
return number
as
    /*计算周数(星期几)*/
    begin
        return f_get_days(f_get_monday(sys_date),sys_date)+1;
    end;

三、创建存储过程，用于生成日期维度数据

存储过程

create or replace procedure sp_generate_date
(
    start_date in number ,--起始日期
    end_date in number --结束日期
)
as
    v_date date;--日期类型变量
    v_sys_date number;--系统日期
    v_year number;--年份
    v_year_start number;--年初日期
    v_year_end number;--年末日期
    v_last_year number;--上年年份
    v_last_year_start number;--上年年初日期
    v_last_year_end number;--上年年末日期
    v_quarter number;--季度
    v_quarter_start number;--季初日期
    v_quarter_end number;--季末日期
    v_last_quarter_start number;--上季季初日期
    v_last_quarter_end number;--上季季末日期
    v_next_quarter_start number;--下季季初日期
    v_next_quarter_end number;--下季季末日期
    v_month number;--月份
    v_month_start number;--月初日期
    v_month_end number;--月末日期
    v_last_month_start number;--上月月初日期
    v_last_month_end number;--上月月末日期
    v_next_month_start number;--下月月初日期
    v_next_month_end number;--下月月末日期
    v_year_week number;--年周数(本年度第几周)
    v_month_week number;--月周数(本月第几周)
    v_day_week number;--周数(星期几)
begin
    v_date:=to_date(start_date,'yyyyMMdd');
    while (v_date<=to_date(end_date,'yyyyMMdd')) loop
        v_sys_date:=to_number(to_char(v_date,'yyyyMMdd'));
        v_year:=to_number(substr(v_sys_date,1,4));
        v_year_start:=F_GET_YEAR_START(v_sys_date);
        v_year_end:=F_GET_YEAR_END(v_sys_date);
        v_last_year:=to_number(substr(F_GET_LAST_YEAR_START(v_sys_date),1,4));
        v_last_year_start:=F_GET_LAST_YEAR_START(v_sys_date);
        v_last_year_end:=F_GET_LAST_YEAR_END(v_sys_date);
        v_quarter:=F_GET_QUARTER(v_sys_date);
        v_quarter_start:=F_GET_QUARTER_START(v_sys_date);
        v_quarter_end:=F_GET_QUARTER_END(v_sys_date);
        v_last_quarter_start:=F_GET_LAST_QUARTER_START(v_sys_date);
        v_last_quarter_end:=F_GET_LAST_QUARTER_END(v_sys_date);
        v_next_quarter_start:=F_GET_NEXT_QUARTER_START(v_sys_date);
        v_next_quarter_end:=F_GET_NEXT_QUARTER_END(v_sys_date);
        v_month:=to_number(substr(v_sys_date,5,2));
        v_month_start:=F_GET_MONTH_START(v_sys_date);
        v_month_end:=F_GET_MONTH_END(v_sys_date);
        v_last_month_start:=F_GET_LAST_MONTH_START(v_sys_date);
        v_last_month_end:=F_GET_LAST_MONTH_END(v_sys_date);
        v_next_month_start:=F_GET_NEXT_MONTH_START(v_sys_date);
        v_next_month_end:=F_GET_NEXT_MONTH_END(v_sys_date);
        v_year_week:=F_GET_YEAR_WEEK(v_sys_date);
        v_month_week:=F_GET_MONTH_WEEK(v_sys_date);
        v_day_week:=F_GET_DAY_WEEK(v_sys_date);

        insert into T_DATE(sys_date, year, year_start, year_end, last_year, last_year_start, last_year_end, quarter,
                             quarter_start, quarter_end, last_quarter_start, last_quarter_end, next_quarter_start,
                             next_quarter_end, month, month_start, month_end, last_month_start, last_month_end,
                             next_month_start, next_month_end, year_week, month_week, day_week)
        VALUES (v_sys_date,v_year,v_year_start,v_year_end,v_last_year,v_last_year_start,v_last_year_end,v_quarter,
                v_quarter_start,v_quarter_end,v_last_quarter_start,v_last_quarter_end,v_next_quarter_start,
                v_next_quarter_end,v_month,v_month_start,v_month_end,v_last_month_start,v_last_month_end,
                v_next_month_start,v_next_month_end,v_year_week,v_month_week,v_day_week);

        v_date:=v_date+1;
    end loop;
    commit ;
end;

四、调用存储过程，生成日期维度数据

调用存储过程

call SP_GENERATE_DATE(20000101,29991231);

标签：date,--,number,sys,维度,日期,Oracle,end
From： https://www.cnblogs.com/liusx-blogs/p/16848440.html

相关文章

赞助商

阅读排行