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.

 

   

更多参考

使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码

使用PL/SQL Developer剖析PL/SQL代码

对比 PL/SQL profiler 剖析结果

PL/SQL Profiler 剖析报告生成html

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

时间: 2014-01-24

PL/SQL 下SQL结果集以html形式发送邮件的相关文章

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

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

PL/SQL 下邮件发送程序

      对DBA而言,尽管在os级别下发送邮件是轻而易举的事情,然而很多时候我们也需要在PL/SQL中来发送邮件,比如监控job的执行状况等.本文根据网友(源作者未考证)的代码将其改装并封装到了package,感谢这位网友的无私奉献.文章首先给出演示调用该包发送邮件的情形后面给出了完整的代码.经测试Oracle 10g,Oracle 11g下均可用.关于os下发送邮件可参考:不可或缺的 sendEmail   1.调用SENDMAIL_PKG来发送邮件 gx_admin@SYBO2SZ> s

PL/SQL动态SQL(原创)

概述 使用动态SQL是在编写PL/SQL过程时经常使用的方法之一.很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成.再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只能针对某几个特定的表来形成分页.而使用动态的SQL,则可以对不同的表,不同的字段进行不同的分页.这些情况的处理通常都是用动态SQL来完成. 动态SQL和静态SQL静态SQL静态SQL通常用于完成可以确定的任务.比如传递部门号调用存

sql语句-mysql定时任务 写下SQL语句

问题描述 mysql定时任务 写下SQL语句 想让MYSQL里面的jpzh表里面的isstaus在晚上00定时更新 那个大侠 写下SQL语句 解决方案 CREATE EVENT e_testON SCHEDULE EVERY 1 DAYSTARTS '2014-12-04 00:00:00'DO UPDATE jpzh SET isstaus=.....; 从 2014-12-04 00:00:00 开始,每天做一次 解决方案二: 你的逻辑好像不太对,按常理开发模式,一般是从应用端控制时间,然后

在连接到 SQL Server 2005 时,在默认的设置下 SQL Server 不允许进行远程连接可能会导致此失败。 (provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接)

error|server|程序 错误:"在连接到 SQL Server 2005 时,在默认的设置下 SQL Server 不允许进行远程连接可能会导致此失败. (provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接) ",       上述错误我遇到两种情况,一种是在打开打开SQL Server 2005时弹出的,另一种是在应用程序连接SQL Server 2005时出现的.归纳了一下,由以下几个原因: 1.数据库引擎没有启动.  

.NET Compact Framework下SQL CE的使用

在Wince和Windows Mobile下最常用的数据库为SQL CE,SQL CE也曾经叫做SQL Server for Windows CE和SQL Server Mobile Edition,最新版本命名为SQL Server Compact 3.5 SP1. SQL Server Compact不仅仅能运 行于Wince和Windows Mobile,而且能运行于Windows的PC上,是Access的有效替代品,如果不使用存储 过程,在SQL Server Compact下开发的程序

Ruby连接使用windows下sql server数据库代码实例

  这篇文章主要介绍了Ruby连接使用windows下sql server数据库代码实例,本文直接给出实现代码,而且给出了两种实现和access数据库的实现代码,需要的朋友可以参考下 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 req

C#环境下sql数据库连接

问题描述 C#环境下sql数据库连接 怎么确定自己的C#环境下,sql sever数据库的连接状态是断开还是可以访问(本地数据库) 解决方案 在用C#操作数据库的时候有个state可以判断数据库的状态的: 解决方案二: 使用named pipe访问,你需要先在服务管理器中启用pipe协议. 解决方案三: 其实你这个问题的不太清楚. 你想问的是:A: 如何判断C#的数据库连接串是否是正确的:B: 如何判断C#的运行某个代码片断中, sqlserver连接状态是否是断开的. 你想问哪一种? 解决方案

请问下sql怎么按分钟去重复呢,注意是sqlserver

问题描述 请问下sql怎么按分钟去重复呢,注意是sqlserver 一张表有 recordid name createtime 三个字段 现在有很多数据,比如一个人在一分钟内记录了好多次数据 比如 1 "Simba1" 2016-01-01 08:09:11.000 2 "Simba1" 2016-01-01 08:09:21.000 3 "Simba1" 2016-01-01 08:09:31.000 4 "Simba2"