Oracle 创建时间维度表并更新是否工作日字段
特价活动:>>>> 畅云管家新购、续费8折优惠，畅捷通T+cloud、好会计、易代账、好业财、好生意云产品8折优惠。
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;
