CREATE OR REPLACE FUNCTION Cal_ServiceTime(pstartDate DATE,
pLastDate DATE,
pServiceBegin varchar2,
pserviceEnd varchar2) RETURN NUMBER AS
vTotalMinutes NUMBER;
TimeFrom varchar2(8);
TimeTo varchar2(8);
TimeStyle Varchar(10);
BEGIN
--Created by 三界.
--计算服务期间(pServiceBegin-pserviceEnd)内的分钟数.
if Regexp_Like(pServiceBegin, '\d\d\:\d\d\:\d\d') then
TimeStyle := 'HH24:MI:SS';
elsif Regexp_Like(pServiceBegin, '\d{1,2}\:\d{1,2}\:\d{1,2}') then
TimeStyle := 'HH:MI:SS';
elsif Regexp_Like(pServiceBegin, '^\d\d$') then
TimeStyle := 'HH24';
else
TimeStyle := 'HH24:MI';
end if;
TimeFrom := TO_CHAR(pstartDate, TimeStyle);
TimeTo := TO_CHAR(pLastDate, TimeStyle);
if pstartDate >= pLastDate or pstartDate is null or pLastDate is null then
RETURN 0;
elsif pServiceBegin = pserviceEnd then
--全时段
RETURN(pLastDate - pstartDate) * 1440;
elsif TimeFrom < TimeTo then
--t+0
if pServiceBegin < pserviceEnd then
--服务时段不跨0点
TimeFrom := Greatest(TimeFrom, pServiceBegin);
TimeTo := Least(TimeTo, pserviceEnd);
if TimeTo > TimeFrom then
vTotalMinutes := (TO_DATE(TimeTo, TimeStyle) - TO_DATE(TimeFrom, TimeStyle)) * 1440;
else
vTotalMinutes := 0;
end if;
else
if TimeFrom >= pServiceBegin or TimeTo <= pserviceEnd then
--午夜前或午夜后时段
vTotalMinutes := (TO_DATE(TimeTo, TimeStyle) - TO_DATE(TimeFrom, TimeStyle)) * 1440;
elsif TimeFrom < pserviceEnd and TimeTo > pServiceBegin then
vTotalMinutes := (TO_DATE(TimeTo, TimeStyle) - TO_DATE(pServiceBegin, TimeStyle) +
TO_DATE(pserviceEnd, TimeStyle) - TO_DATE(TimeFrom, TimeStyle)) * 1440;
elsif TimeFrom < pserviceEnd then
vTotalMinutes := (TO_DATE(pserviceEnd, TimeStyle) - TO_DATE(TimeFrom, TimeStyle)) * 1440;
elsif TimeTo > pServiceBegin then
vTotalMinutes := (TO_DATE(TimeTo, TimeStyle) - TO_DATE(pServiceBegin, TimeStyle)) * 1440;
else
vTotalMinutes := 0;
end if;
end if;
elsif TimeFrom > TimeTo then
--t+1
if pServiceBegin < pserviceEnd then
--服务时段不跨0点
if TimeFrom between pServiceBegin and pserviceEnd then
vTotalMinutes := (TO_DATE(pserviceEnd, TimeStyle) - TO_DATE(TimeFrom, TimeStyle)) * 1440;
elsif TimeFrom < pServiceBegin then
vTotalMinutes := (TO_DATE(pserviceEnd, TimeStyle) - TO_DATE(pServiceBegin, TimeStyle)) * 1440;
else
vTotalMinutes := 0;
end if;
if TimeTo between pServiceBegin and pserviceEnd then
vTotalMinutes := (TO_DATE(TimeTo, TimeStyle) - TO_DATE(pServiceBegin, TimeStyle)) * 1440 +
vTotalMinutes;
elsif TimeTo > pserviceEnd then
vTotalMinutes := (TO_DATE(pserviceEnd, TimeStyle) - TO_DATE(pServiceBegin, TimeStyle)) * 1440 +
vTotalMinutes;
end if;
else
if TimeFrom between pserviceEnd and pServiceBegin then
TimeFrom := pServiceBegin;
end if;
if TimeTo between pserviceEnd and pServiceBegin then
TimeTo := pserviceEnd;
end if;
vTotalMinutes := (TO_DATE(TimeTo, TimeStyle) + 1 - TO_DATE(TimeFrom, TimeStyle)) * 1440;
if TimeFrom < pserviceEnd and TimeTo < pserviceEnd then
vTotalMinutes := vTotalMinutes -
(TO_DATE(pServiceBegin, TimeStyle) - TO_DATE(pserviceEnd, TimeStyle)) * 1440;
elsif TimeFrom > pServiceBegin and TimeTo > pServiceBegin then
vTotalMinutes := vTotalMinutes -
(TO_DATE(pServiceBegin, TimeStyle) - TO_DATE(pserviceEnd, TimeStyle)) * 1440;
end if;
end if;
else
vTotalMinutes := 0;
end if;
--加上间隔天数的每天服务分钟数.
if pLastDate - pstartDate >= 1 then
vTotalMinutes := Floor(pLastDate - pstartDate) * 1440 + vTotalMinutes;
end if;
RETURN vTotalMinutes;
END;