Oracle 11g 計算員工入職年限 SQL 寫法

Oracle 11g 中根據員工入職日期計算入職年限（工齡），需處理日期運算、年份差異與邊界條件。本文提供標準 SQL 寫法與進階考量。

基礎表結構假設

CREATE TABLE employees (
    emp_id     NUMBER PRIMARY KEY,
    emp_name   VARCHAR2(50),
    hire_date  DATE  -- 入職日期
);

核心 SQL 寫法

1. 簡易年份差 (僅年份相減)

SELECT emp_name,
       hire_date,
       EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hire_date) AS service_years_simple
FROM employees;

缺點：未考慮「月/日」是否已過，如 2023-12-31 入職，2024-01-01 查詢會算 1 年（實際不足 1 天）。

2. 精確年限 (月份差 / 12)

SELECT emp_name,
       hire_date,
       FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS service_years_exact
FROM employees;

MONTHS_BETWEEN 回傳月份差 (含小數)，除以 12 再 FLOOR 取整數年，最符合人力資源習慣算法。

3. 完整年月日顯示

SELECT emp_name,
       hire_date,
       FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS years,
       MOD(FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date)), 12) AS months,
       FLOOR(SYSDATE - ADD_MONTHS(hire_date, FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date)))) AS days
FROM employees;

進階場景處理

PL/SQL 函數封裝 (可重用)

CREATE OR REPLACE FUNCTION get_service_years(
    p_hire_date IN DATE,
    p_ref_date  IN DATE DEFAULT SYSDATE
) RETURN NUMBER IS
BEGIN
    RETURN FLOOR(MONTHS_BETWEEN(p_ref_date, p_hire_date) / 12);
EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
END;
/

使用：SELECT get_service_years(hire_date) FROM employees;

效能優化提示

• hire_date 建立索引若頻繁篩選 (如 WHERE service_years > 10)。
• 避免在 WHERE 條件對欄位做函數運算 (WHERE FLOOR(MONTHS_BETWEEN(...)) > 10 導致全表掃描)，改寫為範圍查詢：WHERE hire_date <= ADD_MONTHS(SYSDATE, -120)。

常見錯誤避坑

• ❌ SYSDATE - hire_date 回傳天數，除以 365 不精確 (閏年)。
• ❌ EXTRACT(YEAR FROM SYSDATE - hire_date) 語法錯誤 (INTERVAL 不支援 EXTRACT YEAR)。
• ❌ 忽略 NULL 入職日期，需 NVL(hire_date, SYSDATE) 或過濾。

掌握 MONTHS_BETWEEN + FLOOR 組合，即可精確計算 Oracle 中的入職年限，滿足 HR 各類報表需求。