计算服务期间内的分钟数

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;