萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

PL/SQL 將 SQL 結果集轉 HTML 表格郵件發送實作

業務場景

營運維護需定時將同步錯誤日誌 (syn_data_err_log_tbl) 以 HTML 表格郵件發送給相關人員。使用 Oracle Scheduler Job 排程,需手動拼接 HTML 代碼。

核心邏輯流程

  1. 游標查詢待發送錯誤記錄 (mailed = 'N'),直接在 SQL 中拼接 <tr><td>... 字串。
  2. 組裝 HTML 標頭 (<table>、樣式、表頭)。
  3. 迴圈疊加每列 HTML 行。
  4. 補上 HTML 尾部 (</table>)。
  5. 呼叫郵件發送套件 (參考 PL/SQL 下郵件發送程序)。
  6. 更新 mailed = 'Y'、mail_time = SYSDATE 標記已發送。

完整程式碼架構


PROCEDURE email_on_syn_data_err_log (
    err_num OUT NUMBER,
    err_msg OUT VARCHAR2
) AS
    v_msg_txt      VARCHAR2(32767);
    v_sub          VARCHAR2(100);
    v_html_header  VARCHAR2(4000);
    v_html_content VARCHAR2(32767);
    v_count        NUMBER;
    v_log_seq      NUMBER(12);
    v_loop_count   NUMBER := 0;

    -- 游標:直接產生帶樣式的 ... 字串
    CURSOR cur_errlog IS
    SELECT 
        '' || TO_CHAR(sd.log_seq) || '' ||
        '' || sd.process || '' ||
        '' || sd.rec_id || '' ||
        '' || REPLACE(REPLACE(sd.err_msg, '<', '<'), '>', '>') || '' ||
        '' || TO_CHAR(sd.log_time, 'yyyy-mm-dd hh24:mi:ss') || '',
        sd.log_seq
    FROM syn_data_err_log_tbl sd
    WHERE sd.mailed = 'N'
    ORDER BY sd.log_seq;
BEGIN
    err_num := common_pkg.c_suc_general;  -- 假設常數包定義成功碼

    -- 1. 組裝 HTML 標頭
    v_html_header := '

資料同步錯誤報表

' || '' || '' || ''; -- 2. 迴圈疊加內容 FOR rec IN cur_errlog LOOP v_html_content := v_html_content || rec.html_row; -- 假設游標回傳 html_row v_loop_count := v_loop_count + 1; v_log_seq := rec.log_seq; -- 記錄最大 log_seq 供後續更新 END LOOP; -- 3. 無資料則不發送 IF v_loop_count = 0 THEN RETURN; END IF; -- 4. 完成 HTML v_msg_txt := v_html_header || v_html_content || '
序號程序記錄ID錯誤訊息記錄時間
'; v_sub := '【自動通知】資料同步錯誤報表 - ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI'); -- 5. 發送郵件 (呼叫通用郵件包) mail_pkg.send_mail( p_to => 'dba_team@company.com', p_subject => v_sub, p_body => v_msg_txt, p_body_type => 'text/html' ); -- 6. 更新發送標記 UPDATE syn_data_err_log_tbl SET mailed = 'Y', mail_time = SYSDATE WHERE log_seq <= v_log_seq AND mailed = 'N'; COMMIT; EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 2000); ROLLBACK; -- 記錄錯誤至日誌表 common_pkg.log_error('email_on_syn_data_err_log', err_num, err_msg); END;

關鍵技術點

  • HTML 跳脫:REPLACE(REPLACE(err_msg, '<', '&lt;'), '>', '&gt;') 防止錯誤訊息含標籤破壞 HTML 結構。
  • 游標直接產出 HTML 片段:減少應用層拼接,效能佳。
  • 批次更新標記:以 log_seq <= max_seq 一次更新,避免逐列 UPDATE。
  • 例外處理:捕捉所有錯誤,回滾、記錄、回傳錯誤碼,確保 Job 不會靜默失敗。
  • 大物件 (LOB) 注意:VARCHAR2(32767) 為 PL/SQL 最大變數長度,超大報表需改用 CLOB。

SQL*Plus 替代方案 (無需手動拼接)


SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SPOOL report.html
SELECT * FROM syn_data_err_log_tbl WHERE mailed = 'N';
SPOOL OFF
-- 再用 OS 指令 (mailx, sendmail) 發送 report.html

適用於簡單排程、無需複雜格式控制的場景。

總結

在 PL/SQL 中發送 HTML 郵件,核心在於 游標層面直接產生 HTML 片段、妥善跳脫特殊字元、批次更新狀態 與 完整例外處理。此模式可泛化為任何「查詢 → HTML 表格 → 郵件/報表」的自動化需求。

copyright © 萬盛學電腦網 all rights reserved