Oracle实现两个日期之间工单处理时长，剔除非工作时间，周末和法定节假日

–实现处理时间统计函数，剔除非工作时间，周末和法定节假日
由于法定节假日没有固定的表，需要引入节假日表：t_work_holiday

create table T_WORK_HOLIDAY
(
  day       DATE not null,
  isholiday VARCHAR2(1) default 'H' not null
);
-- Add comments to the columns 
comment on column T_WORK_HOLIDAY.isholiday
  is 'H:Holiday,  W:Workday';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_WORK_HOLIDAY
  add constraint PK primary key (DAY)；

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('26-04-2020', 'dd-mm-yyyy'), 'W');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('09-05-2020', 'dd-mm-yyyy'), 'W');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('01-05-2020', 'dd-mm-yyyy'), 'H');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('02-05-2020', 'dd-mm-yyyy'), 'H');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('03-05-2020', 'dd-mm-yyyy'), 'H');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('04-05-2020', 'dd-mm-yyyy'), 'H');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('05-05-2020', 'dd-mm-yyyy'), 'H');

CREATE OR REPLACE FUNCTION get_finish_time(p_dayBegin in date,
                                      p_dayEnd   in date,
                                      p_num      in number)
/************************************************************/
  /*  funcate     : get_finish_time
  /*  Description : 计算工作小时数
                  : 工作日：早9:00~晚18:00 共计9小时
                  : 推算公式: 间隔天数超过1天：当前的时间+结束那天的时间+9*工作日间隔天数
                              当天处理完：结束时间-开始时间
  /*  Parameters  : p_dayBegin  开始时间
                    p_dayEnd    结束时间
                    p_num  保留位数
  */
 return number is
  minWorkday  date := p_dayBegin; --派单时间
  maxWorkDay  date := p_dayEnd; --当前时间
  v_dayBegin  date; --处理后的派单时间
  v_dayEnd    date; --处理后的当前时间
  v_num       number := p_num; --小数保留位数
  v_workBegin number := 1; --开始工作时间
  v_workEnd   number := 1; --结束工作时间
  v_calcHour  number; --总相差小时数
  days        number := 0; --间隔天数
  v_hour      number; --实际相差小时数

  isHoliday varchar2(1); --是否为节假日标签
  d         date; --临时变量

begin
  --计算工作时间
  v_workBegin := 9 / 24;
  v_workEnd   := 18 / 24;
  --找到第一个工作日
  for i in 0 .. TRUNC(p_dayEnd) - TRUNC(p_dayBegin) loop
    d := minWorkDay + i;
    select nvl((select t.isholiday
                 from t_work_holiday t
                where t.day = trunc(d)),
               case to_char(d, 'd')
                 when '1' then
                  'H'
                 when '7' then
                  'H'
                 else
                  'W'
               end)
      into isHoliday
      from sys.dual;
    if (isHoliday = 'W') then
      minWorkday := d;
      exit;
    else
      minWorkday := TRUNC(minWorkday) + v_workBegin;
    end if;
  end loop;

  --找到最后一个工作日
  for i in 0 .. TRUNC(p_dayEnd) - TRUNC(p_dayBegin) loop
    d := maxWorkDay - i;
    select nvl((select t.isholiday
                 from t_work_holiday t
                where t.day = trunc(d)),
               case to_char(d, 'd')
                 when '1' then
                  'H'
                 when '7' then
                  'H'
                 else
                  'W'
               end)
      into isHoliday
      from sys.dual;
    if (isHoliday = 'W') then
      maxWorkDay := d;
      exit;
    else
      maxWorkDay := TRUNC(maxWorkDay) + v_workEnd;
    end if;
  end loop;
  dbms_output.put_line(to_char(minWorkDay, 'yyyymmdd hh24:mi:ss'));
  dbms_output.put_line(to_char(maxWorkDay, 'yyyymmdd hh24:mi:ss'));

  --没有找到工作日
  if (maxWorkDay < minWorkDay) or isHoliday = 'H' then
    return(0);
  end if;

  --进行判断处理派单时间
  if minWorkday > (TRUNC(minWorkday) + v_workEnd) THEN
    --晚上工作时间以后
    v_dayBegin := (TRUNC(minWorkday) + v_workEnd); --变成当天晚上工作时间
  elsif minWorkday < (TRUNC(minWorkday) + v_workBegin) THEN
    --早上工作时间之前
    v_dayBegin := (TRUNC(minWorkday) + v_workBegin); --变成当天早上工作时间
  else
    v_dayBegin := minWorkday; --工作时间
  end if;
  --进行判断处理当前时间
  if maxWorkDay > (TRUNC(maxWorkDay) + v_workEnd) THEN
    --晚上工作时间之后
    v_dayEnd := (TRUNC(maxWorkDay) + v_workEnd); --变成当天晚上工作时间
  elsif maxWorkDay < (TRUNC(maxWorkDay) + v_workBegin) THEN
    --早上工作时间之前
    v_dayEnd := (TRUNC(maxWorkDay) + v_workBegin); --变成当天早上工作时间
  else
    v_dayEnd := maxWorkDay; --工作时间
  end if;
  dbms_output.put_line('v_dayBegin:' ||
                       to_char(v_dayBegin, 'yyyymmdd hh24:mi:ss'));
  dbms_output.put_line('v_dayEnd:' ||
                       to_char(v_dayEnd, 'yyyymmdd hh24:mi:ss'));
  --计算第一个工作日和最后一个工作日之间的工作日数量
  select sum(nvl(case b.isholiday
                   when 'H' then
                    0
                   when 'W' then
                    1
                   else
                    null
                 end,
                 case to_char(a.DAY_ID, 'd')
                   when '1' then
                    0
                   when '7' then
                    0
                   else
                    1
                 end))
    into days
    from (SELECT trunc(v_dayBegin) + ROWNUM - 1 DAY_ID
            FROM DUAL
          CONNECT BY ROWNUM <= trunc(v_dayEnd) - trunc(v_dayBegin) + 1) a
    left join t_work_holiday b
      on a.DAY_ID = b.day;

  dbms_output.put_line('workdays1:' || days);

  --计算时间差
  if (trunc(v_dayEnd) = trunc(v_dayBegin)) then
    --当天的情况
    v_calcHour := to_number(v_dayEnd - v_daybegin) * 24;
  else
    --隔天的情况
    v_calcHour := to_number((trunc(v_daybegin) + v_workEnd) - v_daybegin) * 24 +
                  to_number(v_dayEnd - (trunc(v_dayEnd) + v_workBegin)) * 24;
  end if;
  dbms_output.put_line('v_calcHour:' || v_calcHour);
  --计算工作小时数
  if days = 1 then
    v_hour := round(v_calcHour, v_num);
  else
    v_hour := round(v_calcHour + 9 * (days - 2), v_num);
  end if;
  return v_hour;
end get_finish_time;