在运维的过程中，有时候需要定时将SQL查询的数据结果集以html表格形式发送邮件，因此需要将SQL查询得到的结果集拼接成html代码。对于这种情形通常有二种方式来完成。一是直接使用cron job来定时轮询并借助os级别的邮件程序来完成。其查询结果集可以直接在SQL*Plus下通过设置html标签自动实现html表格形式。一种方式是在Oracle中使用scheduler job来定时轮询。这种方式需要我们手动拼接html代码。本文即是对第二种情形展开描述。
关于PL/SQL下如何发送邮件可参考: PL/SQL 下邮件发送程序
1、代码描述
?
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
--下面的代码段主要主要是用于发送数据库A部分数据同步到数据库B是出现的错误信息
--表syn_data_err_log_tbl主要是记录错误日志，也就是说只要表中出现了新的记录或者旧记录且mailed列标志为N，即表示需要发送邮件
--下面逐一描述代码段信息，该代码段可以封装到package.
PROCEDURE email_on_syn_data_err_log (err_numOUT NUMBER,
err_msgOUT VARCHAR2)
AS
v_msg_txtVARCHAR2 (32767);
v_subVARCHAR2 (100);
v_html_headerVARCHAR (4000);
v_html_contentVARCHAR (32767);
v_countNUMBER;
v_log_seqNUMBER (12);
v_loop_countNUMBER := 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>'
|| '<tdstyle="vertical-align:top;padding: 5px;"> '
|| sd.rec_id
|| '</td> '
|| '<td style="padding: 5px;"> '
|| REPLACE (REPLACE (sd.err_msg, '<', ';'), '>', ';')
|| '</td>'
|| '<tdstyle="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;">
<thstyle="color: white;width:100px;padding: 5px;">Log sequence</th>
<thstyle="color: white;width:100px;padding: 5px;">Process</th>
<thstyle="color: white;width:100px;padding: 5px;">Rec ID</th>
<thstyle="color: white;width:100px;padding: 5px;">Error message</th>
<thstyle="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、调用示例及邮件样式
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
gx_admin@SYBO2SZ> DECLARE
2ERR_NUM NUMBER;
3ERR_MSG VARCHAR2(32767);
4
5BEGIN
6ERR_NUM := NULL;
7ERR_MSG := NULL;
8
9GX_ADMIN.SENDMAIL_PKG.EMAIL_ON_SYN_DATA_ERR_LOG ( ERR_NUM, ERR_MSG );
10COMMIT;
11END;
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 联合数组与嵌套表
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
