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

时间:2021-09-10 04:24:52

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

案例背景:

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

 

案例工具:

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;