Oracle实现单次邮件发送多条DML变更记录的需求与问题

问题背景 #

原行级触发器会在dw.sap_exclude_sites表每执行一次INSERT/UPDATE/DELETE操作时发送单封邮件，现需改为在最后一次DML操作15分钟后发送汇总邮件，包含这段时间内的所有变更记录。

解决方案步骤 #

1. 改造日志表，增加发送标记字段

为避免重复发送邮件，给日志表添加IS_SENT字段标记记录是否已发送：

ALTER TABLE dw.EVALUATIONS_LOG 
ADD IS_SENT VARCHAR2(1) DEFAULT 'N' CHECK (IS_SENT IN ('Y','N'));

2. 简化触发器，仅记录变更日志

移除触发器内的邮件发送逻辑，只负责记录变更，同时修正DELETE操作时:NEW为NULL的错误：

CREATE OR REPLACE TRIGGER EVAL_CHANGE_TRIGGER
AFTER INSERT OR UPDATE OR DELETE
ON dw.sap_exclude_sites
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO dw.EVALUATIONS_LOG (log_date, action, old, new, is_sent)
    VALUES (SYSDATE, 'INSERT', NULL, :new.WHSCODE, 'N');
  ELSIF UPDATING THEN
    INSERT INTO dw.EVALUATIONS_LOG (log_date, action, old, new, is_sent)
    VALUES (SYSDATE, 'UPDATE', :old.WHSCODE, :new.WHSCODE, 'N');
  ELSIF DELETING THEN
    INSERT INTO dw.EVALUATIONS_LOG (log_date, action, old, new, is_sent)
    VALUES (SYSDATE, 'DELETE', :old.WHSCODE, NULL, 'N');
  END IF;
END;
/

3. 创建批量发送邮件的存储过程

该过程负责收集最近15分钟未发送的变更记录，格式化邮件内容，发送后标记记录为已发送：

CREATE OR REPLACE PROCEDURE SEND_BATCH_EVAL_LOG_MAIL
IS
  v_mail_subject VARCHAR2(4000);
  v_mail_body VARCHAR2(32767); -- 扩容支持批量内容
  v_email_to VARCHAR2(4000) := 'kkishore@****.com';
  v_email_from VARCHAR2(4000) := 'bidev-noreply@****.com';
  v_email_host VARCHAR2(4000) := '****.com';
BEGIN
  -- 初始化邮件主题与格式
  v_mail_subject := 'SAP_EXCLUDE_SITES 批量变更通知 (' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI') || ')';
  v_mail_body := '以下是最近的SAP_EXCLUDE_SITES变更记录:' || CHR(10) || CHR(10);
  v_mail_body := v_mail_body || '日期时间          | 操作类型 | 旧值       | 新值' || CHR(10);
  v_mail_body := v_mail_body || '-------------------|----------|------------|------------' || CHR(10);

  -- 拼接未发送的变更记录
  FOR log_rec IN (
    SELECT log_date, action, old, new
    FROM dw.EVALUATIONS_LOG
    WHERE is_sent = 'N' 
      AND log_date >= SYSDATE - INTERVAL '15' MINUTE
    ORDER BY log_date DESC
  ) LOOP
    v_mail_body := v_mail_body || 
      TO_CHAR(log_rec.log_date, 'DD-MON-YYYY HH24:MI:SS') || ' | ' ||
      RPAD(log_rec.action, 8) || ' | ' ||
      NVL(RPAD(log_rec.old, 10), 'NULL') || ' | ' ||
      NVL(RPAD(log_rec.new, 10), 'NULL') || CHR(10);
  END LOOP;

  -- 仅当有变更记录时发送邮件
  IF LENGTH(v_mail_body) > LENGTH('以下是最近的SAP_EXCLUDE_SITES变更记录:' || CHR(10) || CHR(10) || '日期时间          | 操作类型 | 旧值       | 新值' || CHR(10) || '-------------------|----------|------------|------------' || CHR(10)) THEN
    DLR_SEND_MAIL(v_email_to, v_mail_subject, v_mail_body, v_email_from, v_email_host);
    
    -- 标记记录为已发送
    UPDATE dw.EVALUATIONS_LOG
    SET is_sent = 'Y'
    WHERE is_sent = 'N' 
      AND log_date >= SYSDATE - INTERVAL '15' MINUTE;
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发送邮件失败: ' || SQLERRM);
    ROLLBACK;
END;
/

4. 创建定时调度任务

使用Oracle调度器每隔15分钟执行一次批量发送存储过程：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'BATCH_EVAL_LOG_MAIL_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'SEND_BATCH_EVAL_LOG_MAIL',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=15', -- 每15分钟执行一次
    enabled         => TRUE,
    comments        => '批量发送SAP_EXCLUDE_SITES变更通知邮件'
  );
END;
/

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