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

      在运维的过程中,有时候需要定时将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.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

android之数据存储之SQLite

          SQLite开源轻量级数据库,支持92-SQL标准,主要用于嵌入式系统,只占几百K系统资源此外,SQLite 不支持一些标准的 SQL 功能...

21590
来自专栏杨建荣的学习笔记

MySQL在RR隔离级别下的unique失效和死锁模拟

今天在测试MySQL事务隔离级别的时候,发现了一个有趣的问题,也参考了杨一之前总结的一篇。http://blog.itpub.net/22664653/view...

38460
来自专栏深度学习之tensorflow实战篇

数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。

SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。 1. 数据查询语言DQL 数据查询语言DQL基本结构...

39090
来自专栏乐沙弥的世界

SQL*PLus 帮助手册(SP2-0171)

    对于经常在SQL*Plus 下工作的大师们而言,总是时不时查询SQL*Plus的帮助命令。着实太多了,记不住。SQL*Plus下直接提供了help命令来...

37930
来自专栏乐沙弥的世界

启用 Oracle 10046 调试事件

    Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_t...

7320
来自专栏乐沙弥的世界

批量迁移Oracle数据文件,日志文件及控制文件

   有些时候需要将Oracle的多个数据文件以及日志文件重定位或者迁移到新的分区或新的位置,比如磁盘空间不足,或因为特殊需求。对于这种情形可以采取批量迁移的方...

16420
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(一)(r11笔记第33天)

今天同事问我一个问题,看起来比较常规,但是仔细分析了一圈,发现实在是有些晕,我隐隐感觉这是一个bug,但是有感觉问题还有很多需要确认和理解的细节。 同事...

36290
来自专栏杨建荣的学习笔记

数据紧急修复之启用错误日志 (r2第12天)

昨晚对测试环境进行了升级,同步了部分生产的数据。整个过程比较顺利,但是在最后一步启用foreign key constraint的时候报了错误。 ora-022...

31490
来自专栏xingoo, 一个梦想做发明家的程序员

Log4j官方文档翻译(九、输出到数据库)

log4j提供了org.apache.log4j.JDBCAppender对象,可以把日志输出到特定的数据库。 常用的属性: bufferSize 设置buff...

22270
来自专栏杨建荣的学习笔记

基于时间点的不完全恢复的例子(r6笔记第9天)

说到不完全恢复,一般有三种场景,基于时间点的不完全恢复,基于scn的不完全恢复,基于cancel的不完全恢复。 三种情况都是不完全恢复采用的方式,而不完全恢复都...

27750

扫码关注云+社区

领取腾讯云代金券