写函数计算 任意两个时间 之间的工作小时
每天工作时间(8:00 - 20:00 共12小时),没有休息日
CREATE OR REPLACE FUNCTION CALCULATE_WORK_HOURS_FUNC (
p_current_time IN DATE, -- 当前时间
p_over_time IN DATE, --竣工时间
p_order_time IN DATE -- 工单到达时间
) RETURN NUMBER
AS
p_work_hours NUMBER := 0;
v_hours NUMBER := 0;
p_work_time DATE := p_order_time;
p_big_time DATE := p_current_time;
BEGIN
-- 如果竣工时间不为null, 则 用竣工时间-工单到达时间, 否则用 当前时间-工单到达时间
IF p_over_time IS NOT NULL THEN
p_big_time := p_over_time;
END IF;
-- 计算第一天的工作时间
IF TRUNC(p_big_time) = TRUNC(p_work_time) THEN
v_hours := LEAST(TO_NUMBER(TO_CHAR(p_big_time, 'HH24')), 20) - GREATEST(TO_NUMBER(TO_CHAR(p_work_time, 'HH24')), 8);
ELSIF TRUNC(p_big_time) > TRUNC(p_work_time) THEN
IF GREATEST(TO_NUMBER(TO_CHAR(p_work_time, 'HH24')), 8) <= 20 THEN
v_hours := 20 - GREATEST(TO_NUMBER(TO_CHAR(p_work_time, 'HH24')), 8);
ELSE
v_hours := 0;
END IF;
END IF;
p_work_hours := p_work_hours + v_hours ;
p_work_time := p_work_time + 1;
-- 计算中间整天的工作时间
WHILE TRUNC(p_work_time) < TRUNC(p_big_time) LOOP
p_work_hours := p_work_hours + 12;
p_work_time := p_work_time + 1;
END LOOP;
-- 计算最后一天的工作时间
IF TRUNC(p_work_time) = TRUNC(p_big_time) THEN
IF LEAST(TO_NUMBER(TO_CHAR(p_big_time, 'HH24')), 20) >= 8 THEN
v_hours := LEAST(TO_NUMBER(TO_CHAR(p_big_time, 'HH24')), 20) - 8;
ELSE
v_hours := 0;
END IF;
p_work_hours := p_work_hours + v_hours ;
p_work_time := p_work_time + 1;
END IF;
-- 对不满一小时的时间处理
IF TO_NUMBER(TO_CHAR(p_big_time, 'HH24')) >= 8 AND TO_NUMBER(TO_CHAR(p_big_time, 'HH24')) < 20 THEN
p_work_hours := p_work_hours + TO_NUMBER(TO_CHAR(p_big_time, 'MI')/60);
END IF;
IF TO_NUMBER(TO_CHAR(p_work_time, 'HH24')) >= 8 AND TO_NUMBER(TO_CHAR(p_work_time, 'HH24')) < 20 THEN
p_work_hours := p_work_hours - TO_NUMBER(TO_CHAR(p_work_time, 'MI')/60);
END IF;
RETURN p_work_hours;
END CALCULATE_WORK_HOURS_FUNC;
调用函数结果:
SELECT SC_YD_DESI.CALCULATE_WORK_HOURS_FUNC(
TO_DATE('2022-11-10 23:00:00', 'YYYY-MM-DD HH24:MI:SS'),NULL ,
TO_DATE('2022-11-07 10:50:00', 'YYYY-MM-DD HH24:MI:SS')
)
FROM DUAL;