oracle 计算非工作时间(去除节假日和非工作时间)

本文链接：https://blog.csdn.net/wangxiaoquan115522/article/details/100533646

CREATE OR REPLACE function moniter_work_time(p_bgdatetime1 in date, --开始时间
p_eddatetime1 in date, --结束时间
workorderid in number
) return number is
min_time number(9);
   v_holiday_count varchar2(400);
v_yongshi number := 0; ----用时,单位小时
v_bgtime char(8);
v_edtime char(8);
v_bgdate char(10);
v_eddate char(10);
v_tmp_bgdatetime varchar2(50);
v_tmp_eddatetime varchar2(50);
v_midtime number := 0; -----中午的1.5小时
v_dtime number;
   v_fishDate date;
   p_bgdatetime DATE;
   p_eddatetime       DATE;
begin
   p_bgdatetime := p_bgdatetime1;
   p_eddatetime := p_eddatetime1;
v_bgtime := to_char(p_bgdatetime, 'hh24:mi:ss');
v_edtime := to_char(p_eddatetime, 'hh24:mi:ss');
   v_bgdate := to_char(p_bgdatetime, 'yyyy-mm-dd');
v_eddate := to_char(p_eddatetime, 'yyyy-mm-dd');
   for r1 in (SELECT DEFINE_WEEK FROM JK_OFFICIAL_HOLIDAY WHERE IS_STATUS = 1) loop
       if v_bgdate = r1.DEFINE_WEEK then
       p_bgdatetime:= p_bgdatetime - 1;
       v_bgtime := '18:00:00';
       end if;
       if v_eddate = r1.DEFINE_WEEK then
       p_eddatetime:= p_eddatetime + 1;
       v_edtime := '08:30:00';
       end if;
   end loop;
   v_bgdate := to_char(p_bgdatetime, 'yyyy-mm-dd');
v_eddate := to_char(p_eddatetime, 'yyyy-mm-dd');
   --挂起时间
begin
select nvl(max(sum(round(moniter_work_time_tmp(wp.pause_begin_date,nvl(wp.pause_end_date, v_fishDate),workorderid),2))),0)
INTO v_dtime
from work_order_pause wp
where wp.work_order_id = workorderid
group by wp.work_order_id;
EXCEPTION
WHEN OTHERS THEN
v_dtime := 0;
end;
   begin
--去除节假日
       SELECT
           COUNT( 1 ) into v_holiday_count
       FROM
           JK_OFFICIAL_HOLIDAY jjk
       WHERE
           jjk.IS_STATUS = 1
       AND to_date( jjk.DEFINE_WEEK, 'yyyy-mm-dd' ) BETWEEN p_bgdatetime AND p_eddatetime;
       EXCEPTION
WHEN OTHERS THEN
v_holiday_count := 0;
end;

   if v_bgtime <= '08:30:00' then v_bgtime := '08:30:00';
   elsif v_bgtime >= '18:00:00' then   v_bgtime := '18:00:00';
   end if;
   if v_edtime <= '08:30:00' then v_edtime := '08:30:00';
elsif v_edtime >= '18:00:00' then v_edtime := '18:00:00';
   end if;
   if trunc(p_bgdatetime,'dd')=trunc(p_eddatetime,'dd')then
   --同一天
       v_tmp_bgdatetime := '2016-11-22 ' || v_bgtime;
       v_tmp_eddatetime := '2016-11-22 ' || v_edtime;
       v_yongshi := (to_date(v_tmp_eddatetime, 'yyyy-mm-dd hh24:mi:ss') - to_date(v_tmp_bgdatetime, 'yyyy-mm-dd hh24:mi:ss')) * 24;
   ELSIF (trunc(p_bgdatetime,'dd') <> trunc(p_eddatetime,'dd')) THEN
       --规范化时间
       v_tmp_bgdatetime := v_bgdate || v_bgtime;
       v_tmp_eddatetime := v_eddate || v_edtime;
       v_yongshi := (to_date(v_tmp_eddatetime, 'yyyy-mm-dd hh24:mi:ss') - to_date(v_tmp_bgdatetime, 'yyyy-mm-dd hh24:mi:ss')) * 24 - (trunc(to_date(v_eddate,'yyyy-mm-dd'),'dd')- trunc(to_date(v_bgdate,'yyyy-mm-dd'),'dd'))*14.5;
end if;
   min_time := round(v_yongshi * 60, 2) - v_holiday_count*9.5*60 - v_dtime;
   return min_time;
EXCEPTION
WHEN OTHERS THEN
min_time := (p_eddatetime1 - p_bgdatetime1) * 60 * 24;
return min_time;
end moniter_work_time;