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

 

转自：刘岛的blog 

转载于:https://www.cnblogs.com/51aspx/archive/2009/09/22/1571926.html