利用oracle存储过程计算时长并剔除18:00-8:30之间的非工作时间

CREATE?OR?REPLACE

PROCEDURE?dgh

(

starttime?IN?DATE,

endtimeIN?DATE,

rst?OUT?NUMBER)

AS

wholeDays_TO_seconds?NUMBER;

firstDay_To_Seconds?NUMBER;

lastDay_To_SecondsNUMBER;

BEGIN

SELECT?CAST(endtime?-?starttime?AS?NUMBER(10,0))*60*60*(18-8.5)

INTO?wholeDays_TO_seconds

FROM?dual;

IF?starttime?>?TRUNC(starttime)+NUMTODSINTERVAL(18,'hour')?THEN

firstDay_To_Seconds:=0;

ELSE

SELECT?(TRUNC(starttime)+NUMTODSINTERVAL(18,'hour')-starttime)*60*60*24

INTO?firstDay_To_Seconds

FROM?dual;

END?IF;

IF?endtime<?TRUNC(endtime)+NUMTODSINTERVAL(8,'hour')+NUMTODSINTERVAL(30,'minute')?THEN

lastDay_To_Seconds:=0;

ELSE

SELECT?(endtime-(TRUNC(endtime)+NUMTODSINTERVAL(18,'hour')))*60*60*24

INTO?lastDay_To_Seconds

FROM?dual;

END?IF;

SELECT?wholeDays_TO_seconds+firstDay_To_Seconds+lastDay_To_Seconds

INTO?rst

FROM?dual;

END;set?serveroutput?on

declare

rst?number;

begin

rst:=0;

dgh(to_date('2014-06-18?12:43:09','yyyy-mm-dd?hh24:mi:ss'),to_date('2014-06-19?12:43:09','yyyy-mm-dd?hh24:mi:ss'),rst);

dbms_output.put_line('--------------'||rst);

end;