时间维表的存储过程

一、功能描述

创建一个存储过程将日期的年、月、日、季度存进一张表，作为一张时间维表使用。

作用一：业务月份不是自然月可以将业务月存进表里

作用二：手工填报的报表是月表，但是统一筛选器为日期或者想要计算同比和环比就把月表关联时间维表变成日表

二、建立存储过程

1、声明存储过程

create or replace procedure procedureName(pareA in paraType,pareB in paraType) is

CREATE OR REPLACE PROCEDURE SP_DIM_DATE(SPERIOD  IN VARCHAR2, EPERIOD  IN VARCHAR2) IS
--SP_DIM_DATE是存储过程名称
--SPERIOD是VARCAHR2类型的参数，作为时间维表开始日期
--EPERIOD是VARCHAR2类型的参数，作为时间维表的结束日期

2、注释。说明存储过程名称、功能、该存储过程的信息

/*
-----------------------------------------------------------------------------
名称：SP_DIM_DATE
功能：时间维表
版本号        编辑时间           编辑人            修改记录
1.0.1         2021/11/15      CMH              1.建立此存储过程
-----------------------------------------------------------------------------
*/

3、参数定义。

V_BEG_PERIOD VARCHAR2(8);   --维表的开始时间
V_END_PERIOD VARCHAR2(8);   --维表的结束时间
V_CUR_PERIOD VARCHAR2(8);   --时间指针
V_JOBNAME  VARCHAR2(200);   --存储过程名称
V_START_TIME DATE;          --存储过程开始运行的时间
V_END_TIME DATE;            --存储过程结束运行的时间
V_EXE_TIME NUMBER;          --存储过程运行的时间
V_ERROR_INFO NVARCHAR2(2000);  --存储过程运行过程中的错误信息

4、存储过程主体的定义

BEGIN
  /*初始化参数*/
  V_JOBNAME := 'SP_DIM_DATE' ;
  V_START_TIME := SYSDATE;
  /*初始化期间参数*/
  V_BEG_PERIOD := NVL(SPERIOD ,TO_CHAR(TRUNC(SYSDATE-1),'YYYYMMDD'));
  V_END_PERIOD := NVL(EPRIOD ,V_BEG_PERIOD);
  V_CUR_PERIOD := V_BEG_PERIOD;

/*循环体*/
WHILE V_CUR_PERIOD <= V_END_PERIOD LOOP
  ......
  SQL语句
  ......
  COMMIT;
  V_CUR_PERIOD := TO_CHAR(TRUNC(TO_DATE(V_CUR_PERIOD,'YYYYMMDD')+1),'YYYYMMDD');
END LOOP;

  /*将相关信息插入日志表LOG_HIS，可以查询运行成功与否还有运行耗时*/
  V_END_TIME := SYSDATE;
  V_EXE_TIME := (V_END_TIME -V_START_TIME)*24*60*60
  INSERT INTO LOG_HIS(JOB_NAME,SPERIOD,EPERIOD,CPERIOD,START_TIME,END_TIME,EXE_TIME,STATUS)VALUES
  (V_JOBNAME ,V_BEG_PERIOD ,V_END_PERIOD ,V_CUR_PERIOD ,V_START_TIME ,V_END_TIME ,V_EXE_TIME,'1');
  COMMIT;
  /*以上是运行成功的日志表信息*/

/*异常处理*/
EXCEPTION
  WHEN OTHERS THEN
  --回滚事务
  ROLLBACK;
  --获取失败信息 及耗时
  V_ERROR_INFO := SUBSTR(SQLEERM,1,2000);
  V_END_TIME :=SYSDATE;
  V_EXE_TIME :=(V_END_TIME -V_START_TIME)*24*60*60;
  INSERT INTO LOG_HIS(JOB_NAME,SPERIOD,EPERIOD,CPERIOD,START_TIME,END_TIME,EXE_TIME,STATUS,ERROR_INFO,OTHER_INFO)VALUES
  (V_JOBNAME, V_BEG_PERIOD, V_END_PERIOD,V_CUR_PERIOD, V_START_TIME, V_END_TIME,V_EXE_TIME, '0',V_ERROR_INFO,NULL);
  COMMIT;
END SP_DIM_DATE;

5、SQL创建时间维表DIM_DATE

delete from dim_date where id=V_CUR_PERIOD;
insert into dim_date(id,fdate,month_of_year,BUSS_MONTH,quarter_of_year,year,month)
select 
V_CUR_PERIOD id  --字符类型日期20211115
,to_date(V_CUR_PERIOD,'yyyy-mm-dd') as fdate  --时间类型日期2021/11/15
,to_char(to_date(V_CUR_PERIOD,'yyyy-mm-dd'),'yyyymm') as MONTH_OF_YEAR  --年月202111
,(case when substr(V_CUR_PERIOD,7,2)<='27' then substr(V_CUR_PERIOD,1,6) else to_char(add_months(to_date(V_CUR_PERIOD,'yyyymmdd'),1),'yyyymm') end) BUSS_MONTH
/*28号-31号算到下一个月,即上月28-当月27算是同一个业务月*/
,to_char(to_date(V_CUR_PERIOD,'yyyy-mm-dd'),'q') as QUARTER_OF_YEAR  --当年季度，1-4季度
,to_char(to_date(V_CUR_PERIOD,'yyyy-mm-dd'),'yyyy') as YEAR  --年份2021
,EXTRACT(MONTH FROM to_date(V_CUR_PERIOD,'yyyy-mm-dd')) as month  --当年月份,1-12月份
from dual

以上SQL可以插入循环体内