如何实现合同到期前3个月向员工自动发送通知？附SQL尝试

一、完善SQL查询逻辑 #

核心是精准筛选距离合同到期日还有0-3个月的员工，需根据数据库类型和表结构调整SQL：

1. 表中已有contract_end_date（明确合同到期日） #

用月份函数计算更准确（避免按天计算的跨月误差）：

-- Oracle 写法
SELECT emp_id, emp_name, contract_end_date
FROM emp
WHERE contract_end_date > SYSDATE  -- 排除已到期员工
  AND contract_end_date <= ADD_MONTHS(SYSDATE, 3); -- 到期日在未来3个月内

-- MySQL 写法
SELECT emp_id, emp_name, contract_end_date
FROM emp
WHERE contract_end_date > CURRENT_DATE
  AND contract_end_date <= DATE_ADD(CURRENT_DATE, INTERVAL 3 MONTH);

-- PostgreSQL 写法
SELECT emp_id, emp_name, contract_end_date
FROM emp
WHERE contract_end_date > CURRENT_DATE
  AND contract_end_date <= CURRENT_DATE + INTERVAL '3 months';

2. 仅存hire_date，合同期限固定（如3年=36个月） #

先计算到期日，再筛选：

-- Oracle 示例（合同3年）
SELECT emp_id, emp_name, ADD_MONTHS(hire_date, 36) AS contract_end_date
FROM emp
WHERE ADD_MONTHS(hire_date, 36) > SYSDATE
  AND ADD_MONTHS(hire_date, 36) <= ADD_MONTHS(SYSDATE, 3);

-- MySQL 示例
SELECT emp_id, emp_name, DATE_ADD(hire_date, INTERVAL 36 MONTH) AS contract_end_date
FROM emp
WHERE DATE_ADD(hire_date, INTERVAL 36 MONTH) > CURRENT_DATE
  AND DATE_ADD(hire_date, INTERVAL 36 MONTH) <= DATE_ADD(CURRENT_DATE, INTERVAL 3 MONTH);

3. 合同期限不固定（需新增字段） #

若员工合同时长不同，建议在emp表新增contract_term_months字段（存储该员工合同总月数），再计算筛选：

-- Oracle 写法
SELECT emp_id, emp_name, ADD_MONTHS(hire_date, contract_term_months) AS contract_end_date
FROM emp
WHERE ADD_MONTHS(hire_date, contract_term_months) > SYSDATE
  AND ADD_MONTHS(hire_date, contract_term_months) <= ADD_MONTHS(SYSDATE, 3);

二、通知执行与去重机制 #

1. 定时触发 #

用数据库自带的定时任务每天执行一次查询：

• Oracle：使用DBMS_SCHEDULER创建凌晨执行的任务
• MySQL：启用event_scheduler后创建定时事件
• PostgreSQL：安装pg_cron扩展实现调度

2. 通知发送 #

• 数据库层：通过存储过程调用邮件服务（如Oracle的UTL_MAIL、MySQL的SEND_MAIL）直接发送
• 应用层：将查询结果导出到后端服务，由服务调用邮件/企业微信/短信API发送，灵活性更高

3. 避免重复通知 #

• 方案1：在emp表新增last_contract_notify_date字段，发送后更新该字段，查询时排除已发送的员工
• 方案2：新建contract_notify_log表，记录emp_id、notify_date、status，查询时关联过滤已发送记录

三、异常处理与日志 #

• 过滤NULL值：查询时加上WHERE hire_date IS NOT NULL（或contract_end_date IS NOT NULL）避免计算错误
• 日志记录：每次执行任务时，将查询结果、发送状态写入日志表，方便排查漏发或失败问题

内容的提问来源于stack exchange，提问作者Retal Omar