Oracle 创建时间维度表并更新是否工作日字段
create or replace PROCEDURE PF_ETL_CREATE_DIM_OA_TIME is /*PF_ETL_CREATE_DIM_OA_TIME: 生成OA时间维数据并更新工作日字段(designer:guo) begin_date: 起始时间20160101 end_date:结束时间20161231 exec PF_ETL_CREATE_DIM_OA_TIME delete from Dim_OA_Time where fyear>=2022; select * from Dim_OA_Time select distinct fyear from Dim_OA_Time order by fyear */ begin_date nvarchar2(20); end_date nvarchar2(20); dDate date; f_year int; f_yearname nvarchar2(20); f_quarter int; f_quarter2 int; f_quartername nvarchar2(20); f_month int; f_monthname nvarchar2(20); f_datename nvarchar2(20); f_week int; f_weekname varchar2(20); f_weekday varchar2(20); f_yearfirstday date; f_yearlastday date; f_quarterfirstday date; f_quarterlastday date; f_monthfirstday date; f_monthlastday date; f_NextDate date; f_nextDayName nvarchar2(30); f_NextmonthName nvarchar2(64); f_TBmonthName nvarchar2(64); f_DateStr varchar2(64); adddays int; end_year varchar2(64); begin_year varchar2(64); BEGIN SELECT to_char(extract(year from sysdate)) into begin_year from dual; delete from Dim_OA_Time where fyear>=to_number(begin_year); begin_date :=begin_year||'0101'; SELECT to_char(extract(year from sysdate)+5) into end_year from dual; end_date := end_year||'1231'; adddays := 1 ; dDate := to_date(begin_date,'yyyymmdd'); WHILE (dDate <= to_date(end_date,'yyyymmdd')) loop f_year :=to_number(to_char(dDate, 'yyyy')); f_yearname := to_char(dDate,'yyyy')|| '年'; f_NextDate := dDate+1; f_DateStr := to_char(dDate,'yyyy-mm-dd'); f_quarter := f_year*100+to_number(to_char(dDate, 'q')); f_quartername := f_yearname || to_char(dDate, 'q') || '季度'; f_month := f_year*100+ to_number(to_char(dDate, 'mm')); f_monthname := f_yearname || to_char(dDate, 'mm')||'月'; f_datename := f_monthname||to_char(dDate, 'dd')||'日'; f_nextDayName := to_char(f_NextDate,'yyyy')||'年'||to_char(f_NextDate, 'mm')||'月'||to_char(f_NextDate, 'dd')||'日'; --f_weekname :=f_yearname || subStr('000'|| to_char(dDate,'fmww'), -2)||'周'; f_weekname :=f_yearname || subStr('000'|| to_char(dDate,'IW'), -2)||'周'; f_week := f_year*100+ to_number(to_char(dDate,'IW')); --201501 -- f_weekday := to_char(dDate, 'day'); --星期几 f_weekday := to_char(dDate, 'day','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'''); --星期几 f_yearfirstday := to_date(to_char(f_year)||'0101', 'yyyymmdd'); f_yearlastday := to_date(to_char(f_year)||'1231', 'yyyymmdd'); f_monthfirstday := to_date(to_char(dDate, 'yyyy')||to_char(dDate, 'mm')||'01','yyyymmdd'); f_monthlastday := ADD_MONTHS(f_monthfirstday,1)-1; f_NextmonthName := to_char(ADD_MONTHS(f_monthfirstday,1),'yyyy')||'年'||to_char(ADD_MONTHS(f_monthfirstday,1), 'mm')||'月'; f_TBmonthName := to_char(f_year+1)||'年'||to_char(f_NextDate, 'mm')||'月'; f_quarter2 := to_number(to_char(dDate, 'q')); f_quarterfirstday := ADD_MONTHS(f_yearfirstday,(f_quarter2-1)*3); f_quarterlastday := ADD_MONTHS(f_yearfirstday,(f_quarter2)*3)-1; insert into Dim_OA_Time(FDATE,FDATENAME,FYEAR,YEARNAME,FQUARTER, QUARTERNAME,FMONTH,MONTHNAME,dayname,Weekname,fWeek,Weekday ,Yearfirstdate,Yearlastdate,Quarterfirstdate,Quarterlastdate,Monthfirstdate,Monthlastdate,HBDayName,HBMonthName,TBmonthName) values(dDate,f_DateStr,f_year,f_yearname,f_quarter,f_quartername,f_month,f_monthname,f_datename,f_weekname,f_week,f_weekday ,f_yearfirstday,f_yearlastday,f_quarterfirstday,f_quarterlastday,f_monthfirstday,f_monthlastday,f_nextDayName,f_NextmonthName,f_TBmonthName); dDate := dDate + adddays; END loop; --更新周末 update dim_oa_time set is_work ='0' where weekday in ('星期六','星期日'); update dim_oa_time set is_work ='1' where weekday not in ('星期六','星期日');
--更新不是周末的休息日 merge into dim_oa_time using (select distinct to_date(date_num,'yyyy-mm-dd hh24:mi:ss') as date_num, is_rest from WORKTIME_SPECIALDAY where IS_REST !=0 and ORG_ACCOUNT_ID='670869647114347' ) table2 on (dim_oa_time.fdate = table2.date_num) when matched then update set dim_oa_time.is_work = '0'; --更新是周末的工作日 merge into dim_oa_time using (select distinct to_date(date_num,'yyyy-mm-dd hh24:mi:ss') as date_num, is_rest from WORKTIME_SPECIALDAY where IS_REST =0 and ORG_ACCOUNT_ID='670869647114347') table2 on (dim_oa_time.fdate = table2.date_num) when matched then update set dim_oa_time.is_work = '1'; end;