oracle存储过程发邮件

CREATE OR REPLACE PROCEDURE PRO_SEND_MAIL(P_RECIPIENT  VARCHAR2, -- 邮件接收人
                                          P_SUBJECT    VARCHAR2, -- 邮件标题
                                          P_MESSAGE    VARCHAR2, -- 邮件正文
                                          P_MESSAGE_ID VARCHAR2 -- 关联邮件发送异常ID
                                          ) IS
  V_MAILHOST VARCHAR2(30) := 'smtp.163.com';
  V_USER     VARCHAR2(30) := 'username@163.com'; --用户名
  V_PASS     VARCHAR2(20) := 'password'; --密码
  V_SENDER   VARCHAR2(50) := 'username@163.com'; --发送者邮箱;

  V_CONN     UTL_SMTP.CONNECTION; --到邮件服务器的连接
  V_MSG      VARCHAR2(4000); --邮件内容
  V_TO_EMAIL VARCHAR2(200);
  M          INTEGER := 1;
  N          INTEGER := 1;
  LOG_ID     VARCHAR2(36);
  errorMsg varchar2(2000); --异常信息 
  
  
BEGIN
  LOOP
    V_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAILHOST, 25);
    UTL_SMTP.EHLO(V_CONN, V_MAILHOST); --服务器登录校验 ；
    UTL_SMTP.COMMAND(V_CONN, 'AUTH LOGIN'); --smtp服务器登录校验
    UTL_SMTP.COMMAND(V_CONN,
                     UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(V_USER))));
    UTL_SMTP.COMMAND(V_CONN,
                     UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(V_PASS))));
    UTL_SMTP.MAIL(V_CONN, '<' || V_SENDER || '>'); --设置发件人

    SELECT SUBSTR(P_RECIPIENT,
                  N,
                  LENGTH(SUBSTR(P_RECIPIENT,
                                N,
                                DECODE(INSTR(P_RECIPIENT, ';', 1, M),
                                       0,
                                       LENGTH(P_RECIPIENT),
                                       INSTR(P_RECIPIENT, ';', 1, M)) -
                                DECODE(INSTR(P_RECIPIENT, ';', 1, M),
                                       0,
                                       N - 1,
                                       N))))
      INTO V_TO_EMAIL
      FROM DUAL;

    UTL_SMTP.RCPT(V_CONN, V_TO_EMAIL); --设置收件人
    -- 创建要发送的邮件内容注意报头信息和邮件正文之间要空一行
    V_MSG := 'MIME-Version: 1.0' || UTL_TCP.CRLF || 'Date:' ||
             TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss') || UTL_TCP.CRLF ||
             'From: XXXXXXXXXXXXXXXXXXXX' || UTL_TCP.CRLF || 'To: <' ||
             P_RECIPIENT || '>' || UTL_TCP.CRLF || 'Subject: ' || P_SUBJECT ||
             UTL_TCP.CRLF || UTL_TCP.CRLF || UTL_TCP.CRLF || P_MESSAGE ||
             UTL_TCP.CRLF ||
             '------------------------------------------------------------------------------' 
             || UTL_TCP.CRLF || '注：这是系统发送的邮件，无需回复　　　　 发件日期:' ||
             TO_CHAR(SYSDATE, 'YYYY/MM/DD hh24:mi:ss') ;

    UTL_SMTP.OPEN_DATA(V_CONN); --打开流
    UTL_SMTP.WRITE_RAW_DATA(V_CONN, UTL_RAW.CAST_TO_RAW(V_MSG));
    UTL_SMTP.CLOSE_DATA(V_CONN); --关闭流
    UTL_SMTP.QUIT(V_CONN); --关闭连接
    N := NVL(INSTR(P_RECIPIENT, ';', N, 1), 0) + 1;
    M := M + 1;
    IF N = 1 THEN
      EXIT;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    IF P_MESSAGE_ID IS NULL THEN
      SELECT SYS_GUID() INTO LOG_ID FROM DUAL;
    ELSE
      LOG_ID := P_MESSAGE_ID;
    END IF;
    errorMsg := SUBSTR(SQLERRM, 1, 2000);
    INSERT INTO SEND_MAIL_DEVICE_AUDIT_LOG
      (ID, SUBJECT, MAIL_ADDRS, SEND_DATE, SQLERR, SUCCESS)
    VALUES
      (LOG_ID,
       SUBSTR(P_SUBJECT, 1, 500),
       SUBSTR(P_SUBJECT, 1, 2000),
       TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
       errorMsg,
       'F');
    COMMIT;
END PRO_SEND_MAIL;