Oracle的邮件发送

最新推荐文章于 2024-09-01 22:02:32 发布

1910260505

最新推荐文章于 2024-09-01 22:02:32 发布

阅读量95

点赞数 1

分类专栏： Oracle快速开发文章标签： oracle

本文链接：https://blog.csdn.net/m0_72327175/article/details/140607727

版权

Oracle快速开发专栏收录该内容

8 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE PROCEDURE SEND_MAIL(SENDER    IN VARCHAR2, --发送人
                                      RECIPIENT IN VARCHAR2, --接收人,多个接收人以逗号分隔
                                      SUBJECT   IN VARCHAR2, --邮件主题
                                      MESSAGE   IN VARCHAR2) IS
  --邮件内容
  MAILHOST VARCHAR2(30) := 'mail.qq.com'; --邮箱服务器
  C        UTL_SMTP.CONNECTION;
  MSG      VARCHAR2(30000);
  TEMP_RECIPIENT VARCHAR2(30000);
BEGIN
  /*MSG := 'Date: ' || TO_CHAR(SYSDATE - 1, 'dd mon yy hh24:mi:ss') ||
  UTL_TCP.CRLF || 'From: <' || SENDER || '>' || UTL_TCP.CRLF ||
  'Subject: ' || SUBJECT || UTL_TCP.CRLF || 'To: <' || RECIPIENT || '>;<' || '191@qq.com' ||'>' ||
  UTL_TCP.CRLF || '' || UTL_TCP.CRLF || MESSAGE;*/
  --将多个收件人重新组装
  TEMP_RECIPIENT := NULL;
  FOR cur IN (SELECT * FROM table(perhost.skg.fn_split(p_str => RECIPIENT, p_delimiter => ','))) LOOP
     IF TEMP_RECIPIENT IS NULL THEN
       TEMP_RECIPIENT := '<' || cur.column_value || '>';
     ELSE
       TEMP_RECIPIENT := TEMP_RECIPIENT || ';' || '<' || cur.column_value || '>';
     END IF;
  END LOOP;
  MSG := 'Date: ' || TO_CHAR(SYSDATE - 1, 'dd mon yy hh24:mi:ss') ||  UTL_TCP.CRLF ||
         'From: <' || SENDER || '>' || UTL_TCP.CRLF ||
         'Subject: ' || SUBJECT || UTL_TCP.CRLF ||
         'TO: ' || TEMP_RECIPIENT || UTL_TCP.CRLF ||
         --'To: <' || RECIPIENT || '>' || UTL_TCP.CRLF
         --'Cc: <' || '1910@qq.com' || '>' || UTL_TCP.CRLF || --抄送人
         '' || UTL_TCP.CRLF || --消息内容必须多一个空行
         MESSAGE;
  --dbms_output.put_line(MSG);
  C := UTL_SMTP.OPEN_CONNECTION(MAILHOST, 25);
  UTL_SMTP.EHLO(C, MAILHOST); --是用 ehlo() 而不是 helo() 函数 ，否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.
  UTL_SMTP.COMMAND(C, 'auth login'); --1
  --UTL_SMTP.COMMAND(C, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('191@qq.com')))); --3 --发送邮箱用户名
  UTL_SMTP.COMMAND(C,
                   UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('191')))); --3 --发送邮箱用户名【不加@后面的】
  UTL_SMTP.COMMAND(C,
                   UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('密码')))); --5 --发送邮箱密码
  UTL_SMTP.MAIL(C, SENDER); --发送者，要加@，例如191@qq.com
  --UTL_SMTP.RCPT(C, RECIPIENT);--接收者，要加@，例如191@qq.com
  --UTL_SMTP.RCPT(C, '191@qq.com');
  --UTL_SMTP.RCPT(C, '192@qq.com');
  FOR cur IN (SELECT * FROM table(perhost.skg.fn_split(p_str => RECIPIENT, p_delimiter => ','))) LOOP
     UTL_SMTP.RCPT(C, cur.column_value);
  END LOOP;
  UTL_SMTP.OPEN_DATA(C);
  UTL_SMTP.WRITE_RAW_DATA(C, UTL_RAW.CAST_TO_RAW(MSG));
  UTL_SMTP.CLOSE_DATA(C);
  UTL_SMTP.QUIT(C);
EXCEPTION
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error1: ' || SQLCODE || ', ' || SQLERRM);
    BEGIN
      UTL_SMTP.QUIT(C);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error2: ' || SQLCODE || ', ' || SQLERRM);
    BEGIN
      UTL_SMTP.QUIT(C);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error3: ' || SQLCODE || ', ' || SQLERRM);
    BEGIN
      UTL_SMTP.QUIT(C);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
END SEND_MAIL;