0. 维度表

在这里插入图片描述

1. 创建表

-- Create table
create table DWD_CALENDAR
(site                VARCHAR2(40),factory             VARCHAR2(40),period_date         DATE,period              VARCHAR2(5),shift_start_timekey VARCHAR2(40),shift_end_timekey   VARCHAR2(40),date_start_timekey  VARCHAR2(15),date_end_timekey    VARCHAR2(15),shift_timekey       VARCHAR2(40),shift_name          VARCHAR2(20),date_timekey        VARCHAR2(15),week_timekey        VARCHAR2(40),month_timekey       VARCHAR2(40),quarter_timekey     VARCHAR2(21),year_timekey        VARCHAR2(16),interface_time      DATE
)
tablespace EDW_COM_DATpctfree 10initrans 1maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited);
-- Create/Recreate indexes 
create index IDX_DWD_CALENDAR_1 on DWD_CALENDAR (SHIFT_TIMEKEY)tablespace EDW_COM_IDXpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited)nologging;
create index IDX_DWD_CALENDAR_2 on DWD_CALENDAR (DATE_TIMEKEY, WEEK_TIMEKEY, MONTH_TIMEKEY)tablespace EDW_COM_IDXpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited)nologging;
create index IDX_DWD_CALENDAR_3 on DWD_CALENDAR (WEEK_TIMEKEY)tablespace EDW_COM_IDXpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited)nologging;
create index IDX_DWD_CALENDAR_4 on DWD_CALENDAR (MONTH_TIMEKEY)tablespace EDW_COM_IDXpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited)nologging;
create index IDX_DWD_CALENDAR_5 on DWD_CALENDAR (PERIOD_DATE, FACTORY, PERIOD)tablespace EDW_COM_IDXpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited)nologging;
-- Grant/Revoke object privileges 
grant select on DWD_CALENDAR to EDWREAD;
grant select on DWD_CALENDAR to EDWTEAM;

2. 存储过程

CREATE OR REPLACE PROCEDURE P_dwd_calendar (PVVI_SITE           IN     VARCHAR2,PVVI_FACTORY        IN     VARCHAR2,PVVI_YEAR           IN     VARCHAR2,PVVO_RETURN_VALUE      OUT VARCHAR2)
IS--=================================================================================--DESCRIPTION : calendar Information I/F--=================================================================================--DATE             NAME             VERSION(x.xx)       DESCRIPTION--2017-11-07                        0.01                Initial Release--=================================================================================--                               VARIALBLE DECLARATION--=================================================================================--SYSTEM VARIALBLE--------------------------------------------------------------L_EXP_USER                EXCEPTION;LVV_PROCEDURE_NAME        VARCHAR2 (30);LVV_RESULT_COUNT          VARCHAR2 (20);LVN_DML_COUNT             NUMBER;LVV_MESSAGE               VARCHAR2 (500);LVV_START_TIMEKEY         VARCHAR2 (40);LVV_END_TIMEKEY           VARCHAR2 (40);LVD_INTERFACE_TIME        DATE;LVV_ETL_LOG_HEAD          VARCHAR2 (500);LVV_FIRST_START_TIMEKEY   VARCHAR2 (40);LVV_LAST_END_TIMEKEY      VARCHAR2 (40);--CUSTOMIZED VARIALBLE----------------------------------------------------------lvd_start_date            DATE;lvd_end_date              DATE;lvv_week                  VARCHAR2 (40);lvv_factory               VARCHAR2 (40);cursor cur_dayweek_list isselect f.site,f.factory,'D' period,TO_DATE(to_char(wwm, 'YYYYMMDD') || ' 080000','YYYYMMDD HH24MISS') period_date,to_char(wwm, 'yyyymmdd') || ' 080000' shift_start_timekey,to_char(wwm, 'yyyymmdd') || ' 200000' shift_end_timekey,to_char(wwm, 'yyyymmdd') d