如何通过Oracle实现自动发邮件功能

在内网项目、外网项目中多有 需要自动监控发邮件提醒的功能，因为邮件功能最便捷、便宜、不用开接口，不用接口费用。现 提供 我在一个内网项目中的使用案例：

案例背景：

在有限的资源下，能够自动给下级单位发布 数据考核评分（自动评分）和各 业务数据扣分 汇总统计后的数量（当然可进行拓展邮件信息）

案例工具：

oracle;一个邮箱的对外服务调用地址；账号；密码；

-----------------------------------------------------------------------------
 
 -----------------------------------------------------------------------------
 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;