代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->
create or replace package body PKG_LOAD_DIM_TIME
as
/************************************
** 是否合理自然日
** 如果是合理自然日则返回1,否则返回0
************************************/
function F_Is_Day_ID
(
p_TIME_ID DIW.DW_DIM_TIME.DAY_CODE%type
) return number
is
v_Date date;
begin
v_Date := to_date(p_TIME_ID, 'YYYYMMDD');
return 1;
exception
when others then
return 0;
end F_Is_Day_ID;
/************************************
** 加载时间维度数据
************************************/
procedure P_BUILD_DIM_TIME
(
p_START_DATE DIW.DW_DIM_TIME.DAY_CODE%type, --开始日期
p_END_DATE DIW.DW_DIM_TIME.DAY_CODE%type --结束日期
)
is
v_START_DATE date; --开始日期
v_END_DATE date; --结束日期
v_DATE date;
v_YEAR varchar2(4);
v_QUARTER varchar2(1);
v_MONTH varchar2(2);
v_TEN_DAY varchar2(1);
v_WEEK varchar2(2);
v_WEEK_YEAR varchar2(4);
v_DAY varchar2(2);
begin
--BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行开始...');
execute immediate 'TRUNCATE TABLE DIW.ODS_DIM_TIME';
if F_Is_Day_ID(p_START_DATE)=1 and F_Is_Day_ID(p_END_DATE)=1
then
v_START_DATE := TO_DATE(p_START_DATE,'YYYYMMDD');
v_END_DATE := TO_DATE(p_END_DATE,'YYYYMMDD');
if v_END_DATE - v_START_DATE >= 0 then
--开始生成
for i in 0..v_END_DATE - v_START_DATE loop
v_DATE := v_START_DATE+i;
v_YEAR := TO_CHAR(v_DATE,'YYYY');
v_QUARTER := TO_CHAR(v_DATE,'Q');
v_MONTH := TO_CHAR(v_DATE,'MM');
v_TEN_DAY := case
when TO_CHAR(v_DATE,'DD')<'11' then '1'
when TO_CHAR(v_DATE,'DD')<'21' then '2'
when TO_CHAR(v_DATE,'DD')<'32' then '3'
end;
--周方案一:星期被年分开,实际是7天分段不是自然周
--v_WEEK := TO_CHAR(v_DATE,'WW');
--v_WEEK_YEAR := v_YEAR;
--周方案二：本星期星期四所在年
--v_WEEK := TO_CHAR(v_DATE,'IW');
--select TO_CHAR(NEXT_DAY(v_DATE-7,2)+3,'YYYY')
into v_WEEK_YEAR from dual;
----v_WEEK_YEAR := TO_CHAR(NEXT_DAY(v_DATE-7,2)+3,'YYYY');--奇怪的错误？？
--周方案三：自然周，周被年分,没找到函数支持。
select
LPAD(TO_CHAR(TRUNC((v_DATE
-to_date(v_YEAR||'0101','yyyymmdd')
+DECODE(TO_NUMBER(to_CHAR(to_date(v_YEAR||'0101','yyyymmdd'),'D')),1,7,
TO_NUMBER(to_CHAR(to_date(v_YEAR||'0101','yyyymmdd'),'D'))-1)-1)/7+1)),2,'0')
INTO v_WEEK
from dual;
v_WEEK_YEAR := v_YEAR;
v_DAY := TO_CHAR(v_DATE,'DD');
insert into DIW.DW_DIM_TIME
(
DAY_CODE,
DAY_LONG_DESC,
DAY_MEDIUM_DESC,
DAY_SHORT_DESC,
WEEK_CODE,
WEEK_LONG_DESC,
WEEK_MEDIUM_DESC,
WEEK_SHORT_DESC,
TEN_DAY_CODE,
TEN_DAY_LONG_DESC,
TEN_DAY_MEDIUM_DESC,
TEN_DAY_SHORT_DESC,
MONTH_CODE,
MONTH_LONG_DESC,
MONTH_MEDIUM_DESC,
MONTH_SHORT_DESC,
QUARTER_CODE,
QUARTER_LONG_DESC,
QUARTER_MEDIUM_DESC,
QUARTER_SHORT_DESC,
HALF_YEAR_CODE,
HALF_LONG_DESC,
HALF_MEDIUM_DESC,
HALF_SHORT_DESC,
YEAR_CODE,
YEAR_LONG_DESC,
YEAR_MEDIUM_DESC,
YEAR_SHORT_DESC,
ALL_TIME_CODE,
ALL_TIME_DESC,
DAY_TIMESPAN,
DAY_END_DATE,
WEEK_TIMESPAN,
WEEK_END_DATE,
TEN_DAY_TIMESPAN,
TEN_DAY_END_DATE,
MONTH_TIMESPAN,
MONTH_END_DATE,
QUARTER_TIMESPAN,
QUARTER_END_DATE,
HALF_YEAR_TIMESPAN,
HALF_YEAR_END_DATE,
YEAR_TIMESPAN,
YEAR_END_DATE
)
values
(
v_YEAR||v_MONTH||v_DAY,
v_YEAR||'年'||v_MONTH||'月'||v_DAY||'日',
/*v_MONTH||'月'||*/v_DAY||'日',
v_YEAR||'-'||v_MONTH||'-'||v_DAY,
v_WEEK_YEAR||'W'||v_WEEK,
v_WEEK_YEAR||'年第'||v_WEEK||'周',
'第'||v_WEEK||'周',
v_WEEK_YEAR||'-W'||v_WEEK,
v_YEAR||v_MONTH||'X'||v_TEN_DAY,
v_YEAR||'年'||v_MONTH||'月'||decode(v_TEN_DAY,'1','上','2','中','下')||'旬',
decode(v_TEN_DAY,'1','上','2','中','下')||'旬',
v_YEAR||'-'||v_MONTH||'-X'||v_TEN_DAY,
v_YEAR||v_MONTH,
v_YEAR||'年'||v_MONTH||'月',
v_MONTH||'月',
v_YEAR||'-'||v_MONTH,
v_YEAR||'Q'||v_QUARTER,
v_YEAR||'年第'||v_QUARTER||'季度',
'第'||v_QUARTER||'季度',
v_YEAR||'-'||'Q'||v_QUARTER,
v_YEAR||'H'||decode(v_QUARTER,'1','1','2','1','2'),
v_YEAR||'年'||decode(v_QUARTER,'1','上','2','上','下')||'半年',
decode(v_QUARTER,'1','上','2','上','下')||'半年',
v_YEAR||'-'||'H'||decode(v_QUARTER,'1','1','2','1','2'),
v_YEAR,
v_YEAR||'年',
v_YEAR||'年',
v_YEAR,
'ALL',
'ALL_TIME',
1,
v_YEAR||v_MONTH||v_DAY,
case
when
TO_CHAR(NEXT_DAY(v_DATE-7,2),'YYYY')<v_WEEK_YEAR
then
NEXT_DAY(v_DATE,2)-TO_DATE(v_WEEK_YEAR||'0101','YYYYMMDD')
when
TO_CHAR(NEXT_DAY(v_DATE,2),'YYYY')>v_WEEK_YEAR
then
TO_DATE(v_WEEK_YEAR||'1231','YYYYMMDD')-NEXT_DAY(v_DATE-7,2)+1
else 7
end,
case
when
TO_CHAR(NEXT_DAY(v_DATE,2),'YYYY')>v_WEEK_YEAR
then v_WEEK_YEAR||'1231'
else TO_CHAR(NEXT_DAY(v_DATE,2)-1,'YYYYMMDD')
end,
decode(v_TEN_DAY,'3',LAST_DAY(v_DATE)-TO_DATE(v_YEAR||v_MONTH||'21','YYYYMMDD')+1,10),
decode(v_TEN_DAY,'3',TO_CHAR(LAST_DAY(v_DATE),'YYYYMMDD'),'2',v_YEAR||v_MONTH||'20',v_YEAR||v_MONTH||'10'),
TO_NUMBER(TO_CHAR(LAST_DAY(v_DATE),'DD')),
TO_CHAR(LAST_DAY(v_DATE),'YYYYMMDD'),
decode(v_QUARTER,'1',TO_DATE(v_YEAR||'0331','YYYYMMDD')-TO_DATE(v_YEAR||'0101','YYYYMMDD')+1,'2',91,92),
decode(v_QUARTER,'1',v_YEAR||'0331','2',v_YEAR||'0630','3',v_YEAR||'0930',v_YEAR||'1231'),
decode(v_QUARTER,'3',184,'4',184,TO_DATE(v_YEAR||'0630','YYYYMMDD')-TO_DATE(v_YEAR||'0101','YYYYMMDD')+1),
decode(v_QUARTER,'1',v_YEAR||'0630','2',v_YEAR||'0630',v_YEAR||'1231'),
TO_DATE(v_YEAR||'1231','YYYYMMDD')-TO_DATE(v_YEAR||'0101','YYYYMMDD')+1,
v_YEAR||'1231'
)
;
end loop;
commit;
end if;
end if;
--BICODE.PKG_ETL_CONTROLLER.P_UPDATE_ETL_TIME_INFO('DW','DIM_TIME');
--BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行结束...');
exception
when others then
rollback;
--BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('异常','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行失败...');
end P_BUILD_DIM_TIME;
end PKG_LOAD_DIM_TIME;
|
