ORACLE 日历表

引用自 https://www.cnblogs.com/xiaobaidejiucuoben/p/14630923.html

创建表

create table Dim_OA_Time
(
FDATE Date
,FDATENAME varchar2(64)
,FYEAR number
,YEARNAME  nvarchar2(20)
,FQUARTER number
,QUARTERNAME nvarchar2(20)
,FMONTH number
,MONTHNAME  nvarchar2(20)
,dayname  nvarchar2(20)
,Weekname nvarchar2(20)
,fWeek number
,Weekday  nvarchar2(20)
,Yearfirstdate  Date
,Yearlastdate Date
,Quarterfirstdate Date
,Quarterlastdate Date 
,Monthfirstdate Date
,Monthlastdate Date
,HBDayName nvarchar2(30)
,HBMonthName nvarchar2(64)
,TBmonthName nvarchar2(64)
,is_work Varchar2(2)
)

 

创建存储过程

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)) 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;

 

创建数据

 Begin PF_ETL_CREATE_DIM_OA_TIME; End;
 
 
 
 Select * FROM Dim_OA_Time Where fyear=2025 Order By fdate ;

Select 日期,to_number(to_char(日期, 'd')) 周几
,(case when to_number(to_char(日期, 'd'))=1 or to_number(to_char(日期, 'd'))=7 Then 1 else 0 end) 是否工作日
FROM (
select 年初 + level - 1 as 日期 from (
select to_date('2025-01-01','yyyy-MM-dd') as 年初,to_date('2025-12-31','yyyy-MM-dd') as 年末 from dual
) connect by level <= (年末 - 年初) + 1
)

 

从开始日期获取 N个工作日后的日期
CREATE OR REPLACE Function fn_getHoliday
(INDATE IN DATE, N IN NUMBER)
Return Date
Is
Result Date;
v_count Number;
v_index Number:=0;
CURRENT_DATE DATE := INDATE+1;//从下一天开始
WORKDAY_COUNT NUMBER := 0;
TVALUE_SQL Varchar2(300):='';
Begin

WHILE  v_index < N Loop   /*WORKDAY_COUNT < N Or*/

dbms_output.put_line('date'|| to_char(CURRENT_DATE,'yyyyMMdd'));
dbms_output.put_line('v_index'|| v_index);
TVALUE_SQL :='Select Count(*) From Dim_OA_Time Where is_work=''1'' And fdatename='''||to_char(CURRENT_DATE,'yyyy-MM-dd')||'''';
dbms_output.put_line('v_index'|| TVALUE_SQL);
EXECUTE IMMEDIATE TVALUE_SQL INTO v_count;
/*下面的语句执行一直返回0 ，不知道具体原因*/
--Select Count(*) Into v_count From Dim_OA_Time Where is_work='1' And fdatename=to_char(CURRENT_DATE,'yyyy-MM-dd');
dbms_output.put_line('is_work'|| v_count);
dbms_output.put_line('v_index'|| v_index);
IF v_count > 0 Then
Result:= CURRENT_DATE;
CURRENT_DATE := CURRENT_DATE + 1;
WORKDAY_COUNT := WORKDAY_COUNT + 1;
v_index := v_index +1;
ELSE
CURRENT_DATE := CURRENT_DATE + 1;
END IF;


END LOOP;


Return(Result) ;
end fn_getHoliday;

Select fn_getHoliday(to_date('2025-09-28','yyyy-MM-dd'),6) FROM dual;--2025/10/11
Select fn_getHoliday(to_date('2025-09-28','yyyy-MM-dd'),7) FROM dual;--2025/10/13