在运维的过程中，有时候需要定时将SQL查询的数据结果集以html表格形式发送邮件，因此需要将SQL查询得到的结果集拼接成html代码。对于这种情形通常有二种方式来完成。一是直接使用cron job来定时轮询并借助os级别的邮件程序来完成。其查询结果集可以直接在SQL*Plus下通过设置html标签自动实现html表格形式。一种方式是在Oracle中使用scheduler job来定时轮询。这种方式需要我们手动拼接html代码。本文即是对第二种情形展开描述。
关于PL/SQL下如何发送邮件可参考: PL/SQL 下邮件发送程序
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 '
| '
|| 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;
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部分信息
'
';
v_html_header := -->下面是拼接每一个字段的信息
v_html_header
|| '
| Log sequence |
Process |
Rec ID |
Error message |
Log time |
';
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 || '
'; --->这里添加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 || '