ORACLE函数返回时间段之间工作日天数（精确到小时）

公文需要进行超时统计，去除节假日精确到小时的用时。

网上没有找到合适的，就自写了一个。

用法：把特殊的节日和特殊的工作日放在表 t_work_holiday 中。

反正也上不了首页， 自己直接贴代码吧。

create table T_WORK_HOLIDAY
(
  DAY       DATE not null,
  ISHOLIDAY VARCHAR2(1) default 'H' not null
);


comment on column T_WORK_HOLIDAY.ISHOLIDAY
  is 'H:Holiday,  W:Workday';

alter table T_WORK_HOLIDAY
  add constraint PK primary key (DAY);
  
  
insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('13-06-2010', 'dd-mm-yyyy'), 'W');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('15-06-2010', 'dd-mm-yyyy'), 'H');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('16-06-2010', 'dd-mm-yyyy'), 'H');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('12-06-2010', 'dd-mm-yyyy'), 'W');

insert into t_work_holiday (DAY, ISHOLIDAY)
values (to_date('14-06-2010', 'dd-mm-yyyy'), 'H');


  

create or replace function f_getWorkdays(dayBegin in Date, dayEnd in Date)
return number
is
minWorkDay date := trunc(dayBegin);
maxWorkDay date := trunc(dayEnd);
isHoliday varchar2(1);
d date;
days number := 0;

begin
  -- t_work_holiday 表中存着特殊工作日和节假日
  --找到第一个工作日
  for i in 0..dayEnd-dayBegin loop
    d := minWorkDay + i;
    select nvl((select t.isholiday from t_work_holiday t where t.day = d), case to_char(d,'d') when '0' then 'H' when '6' then 'H' else 'W' end) into isHoliday from sys.dual;
    if(isHoliday = 'W')then
       minWorkday := d;
       exit;
    end if;
  end loop;
  dbms_output.put_line(minWorkday);

  --找到最后一个工作日
  for i in 0..dayEnd-dayBegin loop
    d := maxWorkDay - i;
    select nvl((select t.isholiday from t_work_holiday t where t.day = d), case to_char(d,'d') when '0' then 'H' when '6' then 'H' else 'W' end) into isHoliday from sys.dual;
    if(isHoliday = 'W') then
       maxWorkDay := d;
       exit;
    end if;
  end loop;
  dbms_output.put_line(maxWorkDay);
  
  --没有找到工作日
  if(maxWorkDay < minWorkDay) then 
      return(0);
  end if;
  
  --计算第一个工作日和最后一个工作日之间的工作日数量
  days := trunc((maxWorkDay - minWorkDay ) / 7) * 5 + nvl(length(replace(substr('01111100111110', to_char(minWorkDay, 'd'), mod(maxWorkDay - minWorkDay  , 7)), '0', '')),0);
  --修正公休及是工作日的周六日数量
  select  days + nvl((select sum(case t.isholiday when 'H' then -1 when 'W' then 1 else 0 end) from t_work_holiday t where t.day >= minWorkDay and t.day < maxWorkDay),0) into days from sys.dual;
  dbms_output.put_line('workdays:' || days);
  --修正前时差,如果 dayBegin 本身是工作日，需要修正小时差
  if(minWorkDay = trunc(dayBegin)) then
      days := days - (dayBegin - minWorkDay);
  end if;
  --修正后时差,如果 dayEnd 本身是工作日，需要修正小时差
  if(maxWorkDay = trunc(dayEnd)) then
      days := days + (dayEnd - maxWorkDay);
  else
      days := days + 1;
  end if;
  dbms_output.put_line(days);
  return(days);
end f_getWorkdays;

--进行测试
select t.*, oauser.f_getWorkdays(t.b, t.e) as days from oauser.tmp_d t

       B    E    DAYS
1    2010-6-29 8:00:00    2010-6-30 12:00:00    1.16666666666667
2    2010-6-29 12:00:00    2010-6-30 12:00:00    1
3    2010-6-27 8:00:00    2010-6-30 12:00:00    2.16666666666667
4    2010-6-28 12:00:00    2010-7-1 12:00:00    3
5    2010-6-10 12:00:00    2010-6-14 12:00:00    3.5
6    2010-6-10 12:00:00    2010-6-30 12:00:00    13
7    2010-6-30 12:00:00    2010-6-30 13:00:00    0.0416666666666667
8    2010-6-10 12:00:00    2010-6-15 12:00:00    3.5