如何通过Oracle实现自动发邮件功能
在内网项目、外网项目中多有 需要自动监控发邮件提醒的功能，因为邮件功能最便捷、便宜、不用开接口，不用接口费用。现 提供 我在一个内网项目中的使用案例：
案例背景：
在有限的资源下，能够自动给下级单位发布 数据考核评分（自动评分）和各 业务数据扣分 汇总统计后的数量（当然可进行拓展邮件信息）
案例工具：
oracle;一个邮箱的对外服务调用地址；账号；密码；
-----------------------------------------------------------------------------
/*
过程名称: 定时发送邮件存储，主要用于对当日考核情况向配置好的邮件群组进行推送；
程序版本: V1.0.0
输入参数： 无
输出参数：
创建人: SongZhenHua 创建日期 2016-09-14
修改人：
修改说明:
*/
-----------------------------------------------------------------------------
PROCEDURE p_bm_b_email_record(out_code OUT VARCHAR2,
out_msg OUT VARCHAR2) IS
v_addressee VARCHAR2(32767); ----收件人email地址
v_text_content VARCHAR2(32767); ----邮件内容
v_text_topic VARCHAR2(2000) := "计量业务运营监督系统考核结果"; ----邮件主题
v_date VARCHAR2(40); ----考核时间
v_out_code VARCHAR2(2000);
v_out_msg VARCHAR2(2000);
v_sql_no VARCHAR2(20);
v_send_time DATE;
v_create_time DATE;
BEGIN
v_create_time := SYSDATE;
SELECT to_char(SYSDATE, "yyyy/mm/dd")
INTO v_date
FROM dual;
FOR c IN (SELECT *
FROM (SELECT t.org_name,
t.org_no,
t.score,
t.score_chg,
t.rank,
t.rank_chg
FROM (SELECT br.rslt_id,
blc.short_name AS org_name,
blc.org_no AS org_no,
decode(nvl(br.score, 99999), 99999, "--",
TRIM(to_char(br.score, "9990.00"))) AS score,
CASE blc.short_name
WHEN "计量中心" THEN
""
ELSE
to_char(br.rank - 1)
END ranks,
br.rank rank,
decode(nvl(br.score_chg, 99999), 99999, "--",
TRIM(to_char(br.score_chg, "9990.00"))) AS score_chg,
CASE blc.short_name
WHEN "计量中心" THEN
""
ELSE
decode(nvl(br.rank_chg, 99999), 99999, "--",
br.rank_chg)
END rank_chgs,
decode(nvl(br.rank_chg, 99999), 99999, "--", br.rank_chg) AS rank_chg,
br.calc_time,
row_number() over(PARTITION BY br.eval_date, br.org_no, br.eval_cycle, br.eval_date ORDER BY br.calc_time DESC) AS rw
FROM bm_b_eval_rslt br,
bm_s_org_level_cfg blc
WHERE br.org_no(+) = blc.org_no
AND br.sum_type = "01"
AND br.sum_flag = "01"
AND br.sum_value(+) = "8000000000000002"
AND br.eval_cycle(+) = "02"
AND br.eval_date(+) = to_char(SYSDATE, "yyyymm")
AND br.calc_time < trunc(SYSDATE) + 1
ORDER BY br.rank) t
WHERE t.rw = "1") t1
LEFT JOIN (SELECT t.res_org_no,
nvl(SUM(total_act_inx_num), 0) AS total_act_inx_num,
nvl(SUM(total_idx_num), 0) AS total_idx_num,
nvl(SUM(warning_idx_num), 0) AS warning_idx_num,
nvl(SUM(alarm_idx_num), 0) AS alarm_idx_num
FROM (SELECT ms.total_act_inx_num,
ms.total_idx_num,
ms.res_org_no,
ms.warning_idx_num,
ms.alarm_idx_num,
row_number() over(PARTITION BY ms.busi_prop, ms.data_src, ms.res_org_no, ms.eval_item_id, ms.alarm_grade, ms.busi_influence_degree, ms.check_influence_degree, ms.stat_time, trunc(ms.calc_time) ORDER BY ms.calc_time DESC) AS rw
FROM bm_b_idx_monitor_stat ms
WHERE ms.sum_flag = "01"
AND ms.stat_cycle = "01"
AND trunc(ms.calc_time) = trunc(SYSDATE)
AND ms.eval_item_id = "8000000000000002"
AND ms.eval_item_flag = "01") t
WHERE t.rw = "1"
GROUP BY t.res_org_no) t2
ON t1.org_no = t2.res_org_no
LEFT JOIN
(SELECT t.res_org_no,
nvl(SUM(total_act_points), 0) AS total_act_points,
nvl(SUM(total_points), 0) AS total_points,
nvl(SUM(alarm_check_points), 0) AS alarm_check_points
FROM (SELECT cs.total_act_points,
cs.total_points,
cs.res_org_no,
cs.alarm_check_points,
row_number() over(PARTITION BY cs.busi_prop, cs.res_org_no, cs.data_src, cs.eval_item_id, cs.check_categ, cs.busi_influence_degree, cs.check_influence_degree, cs.stat_time, trunc(cs.calc_time) ORDER BY cs.calc_time DESC) AS rw
FROM bm_b_data_check_stat cs
WHERE cs.sum_flag = "01"
AND cs.calc_frqcy = "01"
AND trunc(cs.calc_time) = trunc(SYSDATE)
AND cs.eval_item_id = "8000000000000002"
AND cs.eval_item_flag = "01") t
WHERE t.rw = "1"
GROUP BY t.res_org_no) t3
ON t1.org_no = t3.res_org_no
LEFT JOIN (SELECT yy.org_no res_org_no,
SUM(yy.comp_tab_num) AS comp_tab_num,
SUM(yy.comp_tab_num_p) AS comp_tab_num_p,
SUM(yy.comp_col_num) AS comp_col_num,
SUM(yy.comp_col_num_p) AS comp_col_num_p,
SUM(yy.excp_tab_num) AS excp_tab_num,
SUM(yy.excp_col_num) AS excp_col_num
FROM (SELECT t.comp_tab_num,
t.comp_tab_num_p,
t.comp_col_num,
t.comp_col_num_p,
t.excp_tab_num,
t.org_no,
t.excp_col_num,
row_number() over(PARTITION BY t.base_sys_no, t.comp_sys_no, t.org_no, trunc(t.comp_date) ORDER BY t.comp_date DESC) AS rw
FROM bm_b_data_compare_global t
WHERE trunc(t.comp_date) = trunc(SYSDATE)) yy
WHERE yy.rw = 1
GROUP BY yy.org_no) t4
ON t1.org_no = t4.res_org_no
ORDER BY to_number(t1.org_no))
LOOP
v_addressee := NULL;
FOR d IN (SELECT t.email,
t.name
FROM (SELECT c.belong_org_no,
c.email,
c.name,
row_number() over(PARTITION BY c.belong_org_no, c.email ORDER BY to_number(c.belong_org_no)) rw
FROM bm_s_sms_tel_group a,
bm_s_sms_tel_group_det b,
bm_s_sms_tel_no c
WHERE a.group_type = "02"
AND c.belong_org_no = c.org_no
AND a.group_id = b.group_id
AND b.tel_id = c.tel_id) t
WHERE t.rw = 1)
LOOP
IF d.email IS NOT NULL
THEN
v_addressee := d.email;
IF v_addressee IS NOT NULL
THEN
v_send_time := SYSDATE;
v_text_content := c.org_name || ": " || chr(10) || " 您好！" || chr(10) ||
" 贵单位在" || v_date || "考核中得分为： " || c.score ||
",得分变化为：" || c.score_chg || ",同级排名为
：" || c.rank || ",排名变化为：" || c.rank_chg || chr(10) ||
" 在本次考核中，贵单位数据情况具体如下：" || chr(10) || " 1.指标监控类考核情况为：" ||
"应统计指标数：" || c.total_act_inx_num || "，实统计指标数：" ||
c.total_idx_num || "，预警指标数：" || c.warning_idx_num ||
"，告警指标数：" || c.alarm_idx_num || "。" || chr(10) ||
" 2.数据核查类考核情况为：" || "应统计数为：" || c.total_act_points ||
"，实统计数为：" || c.total_points || "，有异常数为：" ||
c.alarm_check_points || "。" || chr(10) || " 3.一致性比对情况为：" ||
"应比对表：" || c.comp_tab_num || "，实比对表数：" || c.comp_tab_num_p ||
"，应比对字段数：" || c.comp_col_num || "，实比对字段数：" ||
c.comp_col_num_p || "，异常表数：" || c.excp_tab_num ||
"，异常字段数位：" || c.excp_col_num || "。" || chr(10) ||
" 请及时登录系统查看异常明细并解决。" || chr(10) || " 谢谢！" ||
chr(10) ||
" 计量业务运营监督系统 " ||
chr(10) ||
" " ||
to_char(v_send_time, "yyyy/mm/dd hh24:mi:ss");
p_bm_b_send_mail(v_addressee, v_text_topic, v_text_content, v_send_time,
v_out_code, v_out_msg);
--失败
IF v_out_code <> 0
THEN
INSERT INTO bm_b_email_record
(email_id,
email_topic,
email_content,
attach_name,
attach_path,
org_no,
addressee,
send_time,
create_time,
is_successed)
SELECT seq_bm_b_email_record.nextval,
v_text_topic,
v_text_content,
"",
"",
c.org_no,
d.name,
v_send_time,
v_create_time,
"0"
FROM dual;
COMMIT;
ELSE
--成功
INSERT INTO bm_b_email_record
(email_id,
email_topic,
email_content,
attach_name,
attach_path,
org_no,
addressee,
send_time,
create_time,
is_successed)
SELECT seq_bm_b_email_record.nextval,
v_text_topic,
v_text_content,
"",
"",
c.org_no,
d.name,
v_send_time,
v_create_time,
"1"
FROM dual;
COMMIT;
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
out_code := SQLCODE;
out_msg := SQLERRM;
pkg_bm_util.p_log(c_module_name, c_package_desc, pkg_bm_util.c_sum_data_calc,
c_package_name, c_package_version, "p_bm_b_calc_excep_det_delete",
v_sql_no, "", "", "", "", "", v_create_time, v_send_time, "0",
out_code, out_msg, "发送邮件存储过程失败！");
END p_bm_b_email_record;
PROCEDURE p_bm_b_send_mail(p_recipient VARCHAR2, -- 邮件接收人
p_subject VARCHAR2, -- 邮件标题
p_message VARCHAR2, -- 邮件正文
p_send_time DATE,
out_code OUT VARCHAR2,
out_msg OUT VARCHAR2) IS
--下面四个变量请根据实际邮件服务器进行赋值
v_mailhost VARCHAR2(30); --SMTP服务器地址
v_user VARCHAR2(30); --登录SMTP服务器的用户名
v_pass VARCHAR2(20); --登录SMTP服务器的密码
v_sender VARCHAR2(50); --发送者邮箱，一般与 ps_user 对应
v_conn utl_smtp.connection; --到邮件服务器的连接
v_msg VARCHAR2(4000); --邮件内容
v_source_nls_characterset VARCHAR2(4000); -- 避免乱码
v_sql_no VARCHAR2(40); --用于记录SQL执行过程
BEGIN
SELECT a.param_item_val
INTO v_mailhost
FROM bm_s_sys_parameter a
WHERE a.param_no = "BM_EMAIL_SMTP";
SELECT a.param_item_val
INTO v_user
FROM bm_s_sys_parameter a
WHERE a.param_no = "BM_EMAIL_USER";
SELECT a.param_item_val
INTO v_pass
FROM bm_s_sys_parameter a
WHERE a.param_no = "BM_EMAIL_PW";
SELECT a.param_item_val
INTO v_sender
FROM bm_s_sys_parameter a
WHERE a.param_no = "BM_EMAIL_SENDER";
v_sql_no := 0;
v_conn := utl_smtp.open_connection(v_mailhost, 25);
v_sql_no := 1;
utl_smtp.ehlo(v_conn, v_mailhost); --是用 ehlo() 而不是 helo() 函数
--否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.
v_sql_no := 2;
utl_smtp.command(v_conn, "AUTH LOGIN"); -- smtp服务器登录校验
v_sql_no := 3;
utl_smtp.command(v_conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_user))));
v_sql_no := 4;
utl_smtp.command(v_conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_pass))));
v_sql_no := 5;
utl_smtp.mail(v_conn, "<" || v_sender || ">"); --设置发件人
v_sql_no := 6;
utl_smtp.rcpt(v_conn, "<" || p_recipient || ">"); --设置收件人
v_sql_no := 7;
-- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行
v_msg := "Date:" || to_char(SYSDATE, "yyyy mm dd hh24:mi:ss") || utl_tcp.crlf ||
"From: " || v_sender || "" || utl_tcp.crlf || "To: " || p_recipient || "" ||
utl_tcp.crlf || "Subject: " || p_subject || utl_tcp.crlf || utl_tcp.crlf -- 这前面是报头信息
|| p_message; -- 这个是邮件正文
v_sql_no := 8;
utl_smtp.open_data(v_conn); --打开流
v_sql_no := 9;
----wyl 解决邮件客户端收邮件邮件乱码 增加convert 转码 20161214
SELECT VALUE
INTO v_source_nls_characterset
FROM nls_database_parameters
WHERE parameter = "NLS_CHARACTERSET";
v_sql_no := 10;
utl_smtp.write_raw_data(v_conn,
utl_raw.cast_to_raw(convert(v_msg, "ZHS16GBK",
v_source_nls_characterset))); --先转码
--这样写标题和内容都能用中文
v_sql_no := 11;
utl_smtp.close_data(v_conn); --关闭流
v_sql_no := 12;
utl_smtp.quit(v_conn); --关闭连接
v_sql_no := 13;
EXCEPTION
WHEN OTHERS THEN
out_code := SQLCODE;
out_msg := SQLERRM;
pkg_bm_util.p_log(c_module_name, c_package_desc, pkg_bm_util.c_sum_data_calc,
c_package_name, c_package_version, "send_mail", v_sql_no,
p_message, "", "", "", "", "", p_send_time, "0", out_code, out_msg,
"发送邮件失败！");
END p_bm_b_send_mail;
以上是 如何通过Oracle实现自动发邮件功能 的全部内容， 来源链接： utcz.com/z/531898.html
