Oracle 计算工时除去节假日 (不精确到每天工作时间)

--两时间跨天的情况下，按开始时间到当天24点 、当天0点到结束时间的规则计算,返回小时数
--前提条件：DIM_oa_TIME 包含每一天，并且is_work=1 工作日 =0 非工作日 
--详见：https://www.cnblogs.com/xiaobaidejiucuoben/p/14630923.html

create or replace function getworktime(begindate in date,enddate in date) return number as
  v_begindate date;
  v_enddate date;
  v_nextdate date;--下一天0时
  v_date date;--当天0时
  beforeworkhournum number(10,2);--开始时间育当天24点两时间相差几个小时
  endworkhournum number(10,2);--结束时间与当天0点两时间相差几个小时
  workhournum number(10,2);--两时间相差几个小时
  workdaynum number(10,2);--两时间相差几个整天工作日
  daynum number(10,2);--两时间相差几个整天
  is_work int;--是否为非工作日
begin

    v_begindate :=begindate;
    v_enddate :=enddate;
    v_nextdate :=To_date(To_char(Trunc(v_begindate+1), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 开始日期的下一天0时
    v_date :=To_date(To_char(Trunc(v_enddate), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 结束日期的当天0时
    beforeworkhournum :=0;
    endworkhournum :=0;
    workhournum :=0;
    workdaynum :=0;
    daynum :=0;

    if v_begindate is null or v_enddate is null or v_begindate>v_enddate --开始日期大于结束日期时
    then
        workhournum :=0;
    else
        if to_number(To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss')-To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss'))=0 --开始日期和结束日期时为同一天
        then
            select  count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --这天是否节假日
            if is_work=0 --or v_begindate is null or v_enddate is null 
            then
                workhournum :=0;
            else
                workhournum := round(to_number(v_enddate-v_begindate)*24,2);
            end if;  
        else 

            select  count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --开始日期是否节假日
            if is_work=0 --or v_begindate is null
            then
                beforeworkhournum :=0;
            else
                beforeworkhournum :=round(to_number(v_nextdate-v_begindate)*24,2)+beforeworkhournum;-- 开始时间到下一天0时的小时数
            end if; 

            select count(*) into workdaynum from DIM_oa_TIME where fdate>=v_begindate and fdate<=v_enddate and is_work=1; --工作日相差天数
            select  count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --结束日期是否节假日 
            if is_work=0 --or v_begindate is null
            then
                --如果v_enddate为节假日workdaynum不减1
                endworkhournum :=0;
            else
                endworkhournum :=round(to_number(v_enddate-v_date)*24,2)+endworkhournum;-- 下一天0时到结束时间的小时数
                workdaynum :=workdaynum-1;--如果v_enddate不为节假日workdaynum减1
            end if;  
            -- daynum :=to_date(To_char(v_enddate, 'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(To_char(v_begindate, 'yyyy/mm/dd'),'yyyy/mm/dd'); --工作日相差天数            
        end if;    
    end if;
    workhournum:=workdaynum*24+endworkhournum+beforeworkhournum+workhournum;
    return workhournum;
end;

---select getworktime(to_date('2021-04-01 11:12:38','yyyy-mm-dd hh24:mi:ss'),to_date('2021-04-03 11:12:38','yyyy-mm-dd hh24:mi:ss')) from  dual;

--select case when (count(*)-1)<0 then 0 else (count(*)-1) end from DIM_oa_TIME where fdate>=to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss') and fdate<=to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss') and is_work=1

--select (count(*)-2) from DIM_oa_TIME  where fdate>=to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss') and fdate<=to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss') and is_work=1

--select  count(*) into is_work from DIM_oa_TIME where fdate=to_date('2021-04-03 02:00:00','yyyy-mm-dd hh24:mi:ss') and is_work =1

-- select round(to_number(to_date('2020-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss'))*24,2) from dual

-- select round(to_number(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24,2) from dual
-- select to_number(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24 from dual

-- select round(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss'),2) from dual
--select  To_date(To_char(Trunc(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')  from dual