PL/SQL 下SQL结果集以html形式发送邮件

来源：互联网发布：python的安装步骤编辑：程序博客网时间：2024/05/13 10:51

在运维的过程中，有时候需要定时将SQL查询的数据结果集以html表格形式发送邮件，因此需要将SQL查询得到的结果集拼接成html代码。对于这种情形通常有二种方式来完成。一是直接使用cron job来定时轮询并借助os级别的邮件程序来完成。其查询结果集可以直接在SQL*Plus下通过设置html标签自动实现html表格形式。一种方式是在Oracle中使用scheduler job来定时轮询。这种方式需要我们手动拼接html代码。本文即是对第二种情形展开描述。

关于PL/SQL下如何发送邮件可参考: PL/SQL 下邮件发送程序
OS 下发送邮件可参考：不可或缺的 sendEmail

1、代码描述

--下面的代码段主要主要是用于发送数据库A部分数据同步到数据库B是出现的错误信息--表syn_data_err_log_tbl主要是记录错误日志，也就是说只要表中出现了新的记录或者旧记录且mailed列标志为N，即表示需要发送邮件--下面逐一描述代码段信息，该代码段可以封装到package. 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    VARCHAR (4000);      v_html_content   VARCHAR (32767);      v_count          NUMBER;      v_log_seq        NUMBER (12);      v_loop_count     NUMBER := 0;      CURSOR cur_errlog    --使用cursor来生成表格标题部分      IS           SELECT '<tr >                            <td style="vertical-align:top;padding: 5px;"> '                  || TO_CHAR (sd.log_seq)                  || '</td>                            <td style="vertical-align:top;padding: 5px;"> '                  || sd.process                  || '</td>'                  || '<td  style="vertical-align:top;padding: 5px;"> '                  || sd.rec_id                  || '</td> '                  || '<td style="padding: 5px;"> '                  || REPLACE (REPLACE (sd.err_msg, '<', ';'), '>', ';')                  || '</td>'                  || '<td  style="vertical-align:top;padding: 5px;">'                  || TO_CHAR (sd.log_time, 'yyyy-mm-dd hh24:mi:ss')                  || '</td>                            </tr>',                  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;      SELECT COUNT (*)             INTO v_count        -->统计当次需要发送的总记录数        FROM syn_data_err_log_tbl sd       WHERE sd.mailed = 'N';      IF v_count > 0        --> 表示有记录需要发送邮件      THEN         SELECT 'Job process failed on ' || instance_name || '/' || host_name           INTO v_sub       -->生成邮件的subject             FROM v$instance;         v_html_header :=             -->定义表格的header部分信息            '<html><header><style>                    #log-table {                    margin: 0;                    padding: 0;                    width: 90%;                    border-collapse: collapse;                    font: 12px "Lucida Grande", Helvetica, Sans-Serif;                    border:1px solid #CCC;                    }                    #log-table td {                    padding: 5px;                    border:1px solid #CCC;                    }                    #log-table th {                    padding: 5px;                    background: black;                    color: white;                    text-align: left;                    }                    #log-table tr:nth-child(even) td {                    background: #eee;                    }                    </style></header><body>                             <table id="log-table"  style="width: 100%;border-collapse: collapse;font-size:12px;">';         v_html_header :=              -->下面是拼接每一个字段的信息            v_html_header            || '<tr style="background: black;">                     <th  style="color: white;width:100px;padding: 5px;">Log sequence</th>                     <th  style="color: white;width:100px;padding: 5px;">Process</th>                     <th  style="color: white;width:100px;padding: 5px;">Rec ID</th>                     <th  style="color: white;width:100px;padding: 5px;">Error message</th>                     <th  style="color: white;padding: 5px;">Log time</th></tr>';         OPEN cur_errlog;     -->打开游标         LOOP            FETCH cur_errlog               INTO v_msg_txt, v_log_seq;            EXIT WHEN cur_errlog%NOTFOUND;            v_loop_count := v_loop_count + 1;            v_html_content := v_html_content || v_msg_txt;   --->注意这里，不断地把从原表中的err_msg拿出来进行拼接通过v_msg_txt            --Maximun record = 50 --            IF v_loop_count > 50              --->这里的判断就是用于控制表格总共显示多少行            THEN                              --->主要是用于如果由于需要拼接的行太多导致超过字符长度32767，因此从50行处截断               v_html_content :=                  v_html_header || v_html_content || '</table></body></html>';  --->这里添加html尾部               SENDMAIL_PKG.sendmail (                  bo_system_pkg.get_sys_para_value ('EMAIL_SENDER_HC_EMAIL'),   --->调用函数获得邮件的接收者，此处可以直接写接收者                  v_sub,                  v_html_content,                  err_num,                  err_msg);               v_msg_txt := '';             --->注，此处对三个本地变量置空               v_html_content := '';               v_loop_count := 0;                              UPDATE syn_data_err_log_tbl sd     --->根据log_seq字段对已经发送过的记录标记为Y                  SET mailed = 'Y'                WHERE sd.mailed = 'N' AND log_seq <= v_log_seq;            -- COMMIT;            ELSIF v_count = cur_errlog%ROWCOUNT   --->当v_count与游标取得记录数相等时，拼接表格尾部html代码，发送邮件以及更新mailed列            THEN               v_html_content :=                  v_html_header || v_html_content || '</table></body></html>';               SENDMAIL_PKG.sendmail (                  bo_system_pkg.get_sys_para_value ('EMAIL_SENDER_HC_EMAIL'),                  v_sub,                  v_html_content,                  err_num,                  err_msg);               v_msg_txt := '';               v_html_content := '';               UPDATE syn_data_err_log_tbl sd                  SET mailed = 'Y'                WHERE sd.mailed = 'N' AND log_seq <= v_log_seq;            END IF;         END LOOP;         COMMIT;         CLOSE cur_errlog;      END IF;   EXCEPTION      WHEN NO_DATA_FOUND      THEN         err_num := common_pkg.c_fail_data_not_found;      WHEN OTHERS      THEN         err_num := common_pkg.c_fail_user_define;         err_msg := 'Fail in process SENDMAIL_PKG.email_on_syn_data_err_log. ';   END;

2、调用示例及邮件样式

gx_admin@SYBO2SZ> DECLARE   2    ERR_NUM NUMBER;  3    ERR_MSG VARCHAR2(32767);  4    5  BEGIN   6    ERR_NUM := NULL;  7    ERR_MSG := NULL;  8    9    GX_ADMIN.SENDMAIL_PKG.EMAIL_ON_SYN_DATA_ERR_LOG ( ERR_NUM, ERR_MSG ); 10    COMMIT;  11  END; 12  /PL/SQL procedure successfully completed.