求一个oracle函数：返回两个时间点之间的有效工作时间,除去正常的下班时间

Question

返回两个时间点之间的有效工作时间。函数有2个参数，分别是起始，截至时间点，除星期六，星期日和下班时间，得到有效的工作时间。
或着除下班时间，得到有效的工作时间。也可   希望能得到高手指点

Answer 1

比如输入2009-08-28 08：00 2009-08-31 08：00 
  上午8点上班
   中午12点下班 下午2点上班18点下班
把下班的时间去掉中午12点到下午2点时2个小时  下午18点到早上8点是14个小时  一共是16个小时非工作时间可以跟据你输入的时间段算出你所有效的工作时间是多少
用Oracle函数写  谢谢啦  返回单位是小时。分

Answer 2

create or replace function f_test
(
    p_start_time in varchar2, -- 格式如：200909010800
    p_end_time   in varchar2  -- 格式如：200909011800
)
return number
is
    v_work_time_start_1 date;
    v_work_time_end_1   date;
    v_work_time_start_2 date;
    v_work_time_end_2   date;  
    v_start_time        date;
    v_end_time          date;
    v_start_day         date;
    v_end_day           date; 
    v_work_time         number;
    v_day               number;
begin
    v_start_time := to_date(p_start_time, 'yyyymmddhh24mi');
    v_end_time   := to_date(p_end_time, 'yyyymmddhh24mi');
    v_start_day  := trunc(v_start_time);
    v_end_day    := trunc(v_end_time);
    
    while v_start_day <= v_end_day loop
        v_work_time_start_1 := to_date(to_char(v_start_day, 'yyyymmdd') || '0800', 'yyyymmddhh24mi');
        v_work_time_end_1   := to_date(to_char(v_start_day, 'yyyymmdd') || '1200', 'yyyymmddhh24mi');
        v_work_time_start_2 := to_date(to_char(v_start_day, 'yyyymmdd') || '1400', 'yyyymmddhh24mi');
        v_work_time_end_2   := to_date(to_char(v_start_day, 'yyyymmdd') || '1800', 'yyyymmddhh24mi');
        
        v_day := least(v_work_time_end_1, v_end_time) - greatest(v_work_time_start_1, v_start_time);
        select decode(sign(v_day), 1, v_day, 0) into v_work_time from dual;        
        v_day := least(v_work_time_end_2, v_end_time) - greatest(v_work_time_start_2, v_start_time);
        select decode(sign(v_day), 1, v_day, 0) + v_work_time  into v_work_time from dual;
        
        v_start_day := v_start_day + 1;        
    end loop;    return v_work_time*24*60; -- 返回有效工作时间（以分钟为单位）
end f_test;

Answer 3

刚才发的有问题，重新发一次：create or replace function f_test
(
    p_start_time in varchar2, -- 统计开始时间，格式如：200909010800
    p_end_time   in varchar2  -- 统计结束时间，格式如：200909011800
)
return number
is
    v_work_time_start_1 date;   -- 有效工作时间段1的起始时间，如：2009-09-01 08：00
    v_work_time_end_1   date;   -- 有效工作时间段1的结束时间，如：2009-09-01 12：00
    v_work_time_start_2 date;   -- 有效工作时间段2的起始时间，如：2009-09-01 14：00
    v_work_time_end_2   date;   -- 有效工作时间段2的结束时间，如：2009-09-01 18：00 
    v_start_time        date;   -- 统计开始时间（精确到分钟）
    v_end_time          date;   -- 统计结束时间（精确到分钟）
    v_start_day         date;   -- 统计开始时间（精确到天）
    v_end_day           date;   -- 统计结束时间（精确到天）
    v_work_time         number; --有效工作时间（以分钟为单位）
    v_day               number;
begin
    v_start_time := to_date(p_start_time, 'yyyymmddhh24mi');
    v_end_time   := to_date(p_end_time, 'yyyymmddhh24mi');
    v_start_day  := trunc(v_start_time);
    v_end_day    := trunc(v_end_time);
    v_work_time  := 0;
    
    -- 循环统计每天的有效工作时间
    while v_start_day <= v_end_day loop
        v_work_time_start_1 := to_date(to_char(v_start_day, 'yyyymmdd') || '0800', 'yyyymmddhh24mi');
        v_work_time_end_1   := to_date(to_char(v_start_day, 'yyyymmdd') || '1200', 'yyyymmddhh24mi');
        v_work_time_start_2 := to_date(to_char(v_start_day, 'yyyymmdd') || '1400', 'yyyymmddhh24mi');
        v_work_time_end_2   := to_date(to_char(v_start_day, 'yyyymmdd') || '1800', 'yyyymmddhh24mi');
        
        -- 有效工作时间段1内的工作时间
        v_day := least(v_work_time_end_1, v_end_time) - greatest(v_work_time_start_1, v_start_time);
        select decode(sign(v_day), 1, v_day, 0) + v_work_time into v_work_time from dual;        
        -- 有效工作时间段2内的工作时间
        v_day := least(v_work_time_end_2, v_end_time) - greatest(v_work_time_start_2, v_start_time);
        select decode(sign(v_day), 1, v_day, 0) + v_work_time into v_work_time from dual;
        
        v_start_day := v_start_day + 1;        
    end loop;    -- 返回有效工作时间（以分钟为单位）
    return v_work_time*24*60; 
end f_test;