Oracle 計算兩日期間工作日天數：SQL 純函數法與 PL/SQL 迴圈法完整對比

Oracle 計算兩日期間工作日天數：SQL 純函數法與 PL/SQL 迴圈法完整對比

需求：計算起始日期與結束日期之間的**工作日天數** (不含雙方日期、不含週末)。

方法一：純 SQL 數學公式法 (高效能、單語句)

SELECT 
  ((TO_NUMBER(TRUNC(TO_DATE('2015-04-22','YYYY-MM-DD'), 'D') - 
                TRUNC(TO_DATE('2015-04-21','YYYY-MM-DD') + 6, 'D'))) / 7 * 5)
  + MOD(7 - TO_NUMBER(TO_CHAR(TO_DATE('2015-04-21','YYYY-MM-DD'), 'D')), 6)
  + LEAST(TO_NUMBER(TO_CHAR(TO_DATE('2015-04-22','YYYY-MM-DD'), 'D')) - 2, 5)
  AS work_days
FROM dual;

公式邏輯拆解

1. 完整週數 × 5：(TRUNC(end,'D') - TRUNC(start+6,'D')) / 7 * 5
   將日期對齊到週邊界 (週日/週一)，計算中間完整週數
2. 起始部分週工作日：MOD(7 - TO_NUMBER(TO_CHAR(start,'D')), 6)
   計算起始日期所在週剩餘工作日 (週一=2...週日=1，Oracle 'D' 格式)
3. 結束部分週工作日：LEAST(TO_NUMBER(TO_CHAR(end,'D')) - 2, 5)
   計算結束日期所在週已過工作日，上限 5 天

測試案例

注意： 此公式**將起始/結束日期若為工作日則計入**。若需「不含雙方日期」，需調整邊界條件。

方法二：PL/SQL 迴圈函數法 (直觀、易維護、可精確控制邊界)

CREATE OR REPLACE FUNCTION num_business_days (
  p_start_date IN DATE,
  p_end_date   IN DATE
) RETURN NUMBER
IS
  v_curr_date DATE := p_start_date;
  v_day_name  VARCHAR2(10);
  v_count     NUMBER := 0;
BEGIN
  -- 邊界檢查
  IF p_end_date - p_start_date <= 0 THEN
    RETURN 0;
  END IF;

  LOOP
    v_curr_date := v_curr_date + 1;  -- 移至下一天
    EXIT WHEN v_curr_date = p_end_date;  -- 到達結束日期停止 (不含結束日)
    
    -- 取得星期名稱 (需注意 NLS_TERRITORY 影響)
    SELECT TO_CHAR(v_curr_date, 'fmDay') INTO v_day_name FROM dual;
    
    -- 非週末則計數
    IF v_day_name NOT IN ('Saturday', 'Sunday') THEN
      v_count := v_count + 1;
    END IF;
  END LOOP;

  RETURN v_count;
END num_business_days;
/

函數測試

兩方法關鍵差異對比

維度
SQL 公式法
PL/SQL 迴圈法
效能
極高 (單語句、無上下文切換)
較低 (逐日迴圈、Context Switch)
邊界語義
含起始/結束工作日
**不含**起始/結束日期 (可精確控制)
NLS 敏感度
低 (使用數字格式 'D')
高 (依賴 'fmDay' 文字，受 NLS_TERRITORY 影響)
可維護性
低 (公式晦澀難改)
高 (邏輯直觀易修改)
適用場景
大量資料集運算、報表查詢
業務邏輯封裝、精確邊界控制


進階優化：SQL 版不含邊界日期
-- 使用數字格式 'D' (1=週日, 7=週六，美式標準)
v_day_num := TO_NUMBER(TO_CHAR(v_curr_date, 'D'));
IF v_day_num NOT IN (1, 7) THEN  -- 排除週日(1)與週六(7)
  v_count := v_count + 1;
END IF;

選型建議

報表/批次處理大量日期區間：使用優化版 SQL 公式
業務規則封裝、需精確控制包含/不含邊界：使用 PL/SQL 函數 (改用數字判斷規避 NLS)
12c+：可建立為 DETERMINISTIC 函數並建立函數索引加速查詢