Oracle发送邮件

Tzq@2018

已于 2023-08-12 05:36:11 修改

阅读量1k

点赞数

分类专栏： Oracle 文章标签： oracle

于 2023-08-12 05:19:04 首次发布

本文链接：https://blog.csdn.net/tttzzzqqq2018/article/details/132242517

版权

Oracle 专栏收录该内容

120 篇文章 13 订阅

订阅专栏

Oracle发送邮件

一、创建发送邮件存过
二、创建自动发送文件的存过。创建Oracle job，每天11点55发送执行该存过发送邮件。
三、设置Windows计划任务，每天11点50执行备份数据库的脚本

一、创建发送邮件存过

创建发送邮件存过p_send_mail()，代码如下：

CREATE OR REPLACE PROCEDURE p_send_mail(p_txt       VARCHAR2,
                                        p_sub       VARCHAR2,
                                        p_sendor    VARCHAR2,
                                        p_receiver  VARCHAR2,
                                        p_server    VARCHAR2,
                                        p_port      NUMBER DEFAULT 25,
                                        p_need_smtp INT DEFAULT 0,
                                        p_user      VARCHAR2 DEFAULT NULL,
                                        p_pass      VARCHAR2 DEFAULT NULL,
                                        p_filename  VARCHAR2 DEFAULT NULL,
                                        p_encode    VARCHAR2 DEFAULT 'bit 7')
  AUTHID CURRENT_USER IS
  /*
  参数说明：
            p_txt :邮件正文
            p_sub: 邮件标题
            p_SendorAddress : 发送人邮件地址
            p_ReceiverAddress : 接收地址，可以同时发送到多个地址上，地址之间用","或者";"隔开
            p_EmailServer : 邮件服务器地址，可以是域名或者IP
            p_Port ：邮件服务器端口
            p_need_smtp：是否需要smtp认证，0表示不需要，1表示需要
            p_user：smtp验证需要的用户名
            p_pass：smtp验证需要的密码
            p_filename：附件名称，必须包含完整的路径，如"d:tempa.txt"。
                        可以有多个附件，附件名称只见用逗号或者分号分隔
            p_encode：附件编码转换格式，其中 p_encode='bit 7' 表示文本类型附件
                                             p_encode='base64' 表示二进制类型附件
  注意：
        1、对于文本类型的附件，不能用base64的方式发送，否则出错
        2、对于多个附件只能用同一种格式发送
  */
  l_crlf          VARCHAR2(2) := utl_tcp.crlf;
  l_sendoraddress VARCHAR2(4000);
  l_splite        VARCHAR2(10) := '++';
  boundary            CONSTANT VARCHAR2(256) := '-----BYSUK';
  first_boundary      CONSTANT VARCHAR2(256) := '--' || boundary || l_crlf;
  last_boundary       CONSTANT VARCHAR2(256) := '--' || boundary || '--' ||
                                                l_crlf;
  multipart_mime_type CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' ||
                                                boundary || '"';
  /* 以下部分是发送大二进制附件时用到的变量 */
  l_fil                 BFILE;
  l_file_len            NUMBER;
  l_modulo              NUMBER;
  l_pieces              NUMBER;
  l_file_handle         utl_file.file_type;
  l_amt                 BINARY_INTEGER := 672 * 3; /* ensures proper format;   2016 */
  l_filepos             PLS_INTEGER := 1; /* pointer for the file */
  l_chunks              NUMBER;
  l_buf                 RAW(2100);
  l_data                RAW(2100);
  l_max_line_width      NUMBER := 54;
  l_directory_base_name VARCHAR2(100) := 'DIR_FOR_SEND_MAIL';
  l_line                VARCHAR2(1000);
  l_mesg                VARCHAR2(32767);
  /* 以上部分是发送大二进制附件时用到的变量 */
  TYPE address_list IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  my_address_list address_list;
  TYPE acct_list IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  my_acct_list acct_list;
  -------------------------------------返回附件源文件所在目录或者名称--------------------------------------
  FUNCTION get_file(p_file VARCHAR2, p_get INT) RETURN VARCHAR2 IS
    --p_get=1 表示返回目录
    --p_get=2 表示返回文件名
    l_file VARCHAR2(1000);
  BEGIN
    IF instr(p_file
            ,'\') > 0 THEN
      --windows
      IF p_get = 1 THEN
        l_file := substr(p_file
                        ,1
                        ,instr(p_file
                              ,'\'
                              ,-1) - 1);
      ELSIF p_get = 2 THEN
        l_file := substr(p_file
                        ,- (length(p_file) - instr(p_file
                                                 ,'\'
                                                 ,-1)));
      END IF;
    ELSIF instr(p_file
               ,'/') > 0 THEN
      --linux/unix
      IF p_get = 1 THEN
        l_file := substr(p_file
                        ,1
                        ,instr(p_file
                              ,'/'
                              ,-1) - 1);
      ELSIF p_get = 2 THEN
        l_file := substr(p_file
                        ,- (length(p_file) - instr(p_file
                                                 ,'/'
                                                 ,-1)));
      END IF;
    END IF;
    RETURN l_file;
  END;
  ---------------删除directory---------------
  PROCEDURE drop_directory(p_directory_name VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'drop directory ' || p_directory_name;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  ---------------创建directory---------------
  PROCEDURE create_directory(p_directory_name VARCHAR2, p_dir VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'create directory ' || p_directory_name || ' as ''' ||
                      p_dir || '''';
    EXECUTE IMMEDIATE 'grant read,write on directory ' || p_directory_name ||
                      ' to public';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END;
  ---------------分割邮件地址或者附件地址---------------
  PROCEDURE p_splite_str(p_str VARCHAR2, p_splite_flag INT DEFAULT 1) IS
    l_addr VARCHAR2(254) := '';
    l_len  INT;
    l_str  VARCHAR2(4000);
    j      INT := 0; --表示邮件地址或者附件的个数
  BEGIN
    /*处理接收邮件地址列表，包括去空格、将;转换为,等*/
    l_str := TRIM(rtrim(REPLACE(REPLACE(p_str
                                       ,';'
                                       ,',')
                               ,' '
                               ,'')
                       ,','));
    l_len := length(l_str);
    FOR i IN 1 .. l_len LOOP
      IF substr(l_str
               ,i
               ,1) <> ',' THEN
        l_addr := l_addr || substr(l_str
                                  ,i
                                  ,1);
      ELSE
        j := j + 1;
        IF p_splite_flag = 1 THEN
          --表示处理邮件地址
          --前后需要加上'<>'，否则很多邮箱将不能发送邮件
          l_addr := '<' || l_addr || '>';
          --调用邮件发送过程
          my_address_list(j) := l_addr;
        ELSIF p_splite_flag = 2 THEN
          --表示处理附件名称
          my_acct_list(j) := l_addr;
        END IF;
        l_addr := '';
      END IF;
      IF i = l_len THEN
        j := j + 1;
        IF p_splite_flag = 1 THEN
          --调用邮件发送过程
          l_addr := '<' || l_addr || '>';
          my_address_list(j) := l_addr;
        ELSIF p_splite_flag = 2 THEN
          my_acct_list(j) := l_addr;
        END IF;
      END IF;
    END LOOP;
  END;
  ---------------写邮件头和邮件内容---------------
  PROCEDURE write_data(p_conn   IN OUT NOCOPY utl_smtp.connection,
                       p_name   IN VARCHAR2,
                       p_value  IN VARCHAR2,
                       p_splite VARCHAR2 DEFAULT ':',
                       p_crlf   VARCHAR2 DEFAULT l_crlf) IS
  BEGIN
    /* utl_raw.cast_to_raw 对解决中文乱码问题很重要*/
    utl_smtp.write_raw_data(p_conn
                           ,utl_raw.cast_to_raw(convert(p_name || p_splite ||
                                                        p_value || p_crlf
                                                       ,'ZHS16GBK')));
  END;
  ---------------写MIME邮件尾部---------------
  PROCEDURE end_boundary(conn IN OUT NOCOPY utl_smtp.connection,
                         LAST IN BOOLEAN DEFAULT FALSE) IS
  BEGIN
    utl_smtp.write_data(conn
                       ,utl_tcp.crlf);
    IF (LAST) THEN
      utl_smtp.write_data(conn
                         ,last_boundary);
    END IF;
  END;
  ---------------发送附件---------------
  PROCEDURE attachment(conn         IN OUT NOCOPY utl_smtp.connection,
                       mime_type    IN VARCHAR2 DEFAULT 'text/plain',
                       inline       IN BOOLEAN DEFAULT TRUE,
                       filename     IN VARCHAR2 DEFAULT 't.txt',
                       transfer_enc IN VARCHAR2 DEFAULT '7 bit',
                       dt_name      IN VARCHAR2 DEFAULT '0') IS
  
    l_filename VARCHAR2(1000);
  BEGIN
    --写附件头
    utl_smtp.write_data(conn
                       ,first_boundary);
    --设置附件格式
    write_data(conn
              ,'Content-Type'
              ,mime_type);
    --如果文件名称非空，表示有附件
    drop_directory(dt_name);
    --创建directory
    create_directory(dt_name
                    ,get_file(filename
                             ,1));
    --得到附件文件名称
    l_filename := get_file(filename
                          ,2);
    IF (inline) THEN
      write_data(conn
                ,'Content-Disposition'
                ,'inline; filename="' || l_filename || '"');
    ELSE
      write_data(conn
                ,'Content-Disposition'
                ,'attachment; filename="' || l_filename || '"');
    END IF;
    --设置附件的转换格式
    IF (transfer_enc IS NOT NULL) THEN
      write_data(conn
                ,'Content-Transfer-Encoding'
                ,transfer_enc);
    END IF;
  
    utl_smtp.write_data(conn
                       ,utl_tcp.crlf);
  
    --begin 贴附件内容
    IF transfer_enc = 'bit 7' THEN
      --如果是文本类型的附件
      BEGIN
        l_file_handle := utl_file.fopen(dt_name
                                       ,l_filename
                                       ,'r'); --打开文件
        --把附件分成多份，这样可以发送超过32K的附件
        LOOP
          utl_file.get_line(l_file_handle
                           ,l_line);
          l_mesg := l_line || l_crlf;
          write_data(conn
                    ,''
                    ,l_mesg
                    ,''
                    ,'');
        END LOOP;
        utl_file.fclose(l_file_handle);
        end_boundary(conn);
      EXCEPTION
        WHEN OTHERS THEN
          utl_file.fclose(l_file_handle);
          end_boundary(conn);
          NULL;
      END; --结束文本类型附件的处理
    
    ELSIF transfer_enc = 'base64' THEN
      --如果是二进制类型的附件
      BEGIN
        --把附件分成多份，这样可以发送超过32K的附件
        l_filepos  := 1; --重置offset，在发送多个附件时，必须重置
        l_fil      := bfilename(dt_name
                               ,l_filename);
        l_file_len := dbms_lob.getlength(l_fil);
        l_modulo   := MOD(l_file_len
                         ,l_amt);
        l_pieces   := trunc(l_file_len / l_amt);
        IF (l_modulo <> 0) THEN
          l_pieces := l_pieces + 1;
        END IF;
        dbms_lob.fileopen(l_fil
                         ,dbms_lob.file_readonly);
        dbms_lob.read(l_fil
                     ,l_amt
                     ,l_filepos
                     ,l_buf);
        l_data := NULL;
        FOR i IN 1 .. l_pieces LOOP
          l_filepos  := i * l_amt + 1;
          l_file_len := l_file_len - l_amt;
          l_data     := utl_raw.concat(l_data
                                      ,l_buf);
          l_chunks   := trunc(utl_raw.length(l_data) / l_max_line_width);
          IF (i <> l_pieces) THEN
            l_chunks := l_chunks - 1;
          END IF;
          utl_smtp.write_raw_data(conn
                                 ,utl_encode.base64_encode(l_data));
          l_data := NULL;
          IF (l_file_len < l_amt AND l_file_len > 0) THEN
            l_amt := l_file_len;
          END IF;
          dbms_lob.read(l_fil
                       ,l_amt
                       ,l_filepos
                       ,l_buf);
        END LOOP;
        dbms_lob.fileclose(l_fil);
        end_boundary(conn);
      EXCEPTION
        WHEN OTHERS THEN
          dbms_lob.fileclose(l_fil);
          end_boundary(conn);
          RAISE;
      END; --结束处理二进制附件
    
    END IF; --结束处理附件内容
    drop_directory(dt_name);
  END; --结束过程ATTACHMENT
  ---------------真正发送邮件的过程---------------
  PROCEDURE p_email(p_sendoraddress2   VARCHAR2, --发送地址
                    p_receiveraddress2 VARCHAR2) --接受地址
   IS
    l_conn utl_smtp.connection; --定义连接
  BEGIN
    /*初始化邮件服务器信息，连接邮件服务器*/
    l_conn := utl_smtp.open_connection(p_server
                                      ,p_port);
    utl_smtp.helo(l_conn
                 ,p_server);
    /* smtp服务器登录校验 */
    IF p_need_smtp = 1 THEN
      utl_smtp.command(l_conn
                      ,'AUTH LOGIN'
                      ,'');
      utl_smtp.command(l_conn
                      ,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user))));
      utl_smtp.command(l_conn
                      ,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_pass))));
    END IF;
  
    /*设置发送地址和接收地址*/
    utl_smtp.mail(l_conn
                 ,p_sendoraddress2);
    utl_smtp.rcpt(l_conn
                 ,p_receiveraddress2);
  
    /*设置邮件头*/
    utl_smtp.open_data(l_conn);
  
    write_data(l_conn
              ,'Date'
              ,to_char(SYSDATE
                      ,'yyyy-mm-dd hh24:mi:ss'));
    /*设置发送人*/
    write_data(l_conn
              ,'From'
              ,p_sendor);
    /*设置接收人*/
    write_data(l_conn
              ,'To'
              ,p_receiver);
    /*设置邮件主题*/
    write_data(l_conn
              ,'Subject'
              ,p_sub);
  
    write_data(l_conn
              ,'Content-Type'
              ,multipart_mime_type);
    utl_smtp.write_data(l_conn
                       ,utl_tcp.crlf);
    utl_smtp.write_data(l_conn
                       ,first_boundary);
    write_data(l_conn
              ,'Content-Type'
              ,'text/plain;charset=gb2312');
    --单独空一行，否则，正文内容不显示
    utl_smtp.write_data(l_conn
                       ,utl_tcp.crlf);
    /* 设置邮件正文
           把分隔符还原成chr(10)。这主要是为了shell中调用该过程，如果有多行，则先把多行的内容合并成一行，
    并用 l_splite分隔  然后用 l_crlf替换chr(10)。这一步是必须的，否则将不能发送邮件正文有多行的邮件
    
         */
    write_data(l_conn
              ,''
              ,REPLACE(REPLACE(p_txt
                              ,l_splite
                              ,chr(10))
                      ,chr(10)
                      ,l_crlf)
              ,''
              ,'');
    end_boundary(l_conn);
  
    --如果文件名称不为空，则发送附件
    IF (p_filename IS NOT NULL) THEN
      --根据逗号或者分号拆分附件地址
      p_splite_str(p_filename
                  ,2);
      --循环发送附件(在同一个邮件中)
      FOR k IN 1 .. my_acct_list.count LOOP
        attachment(conn => l_conn
                  ,filename => my_acct_list(k)
                  ,transfer_enc => p_encode
                  ,dt_name => l_directory_base_name || to_char(k));
      END LOOP;
    END IF;
  
    /*关闭数据写入*/
    utl_smtp.close_data(l_conn);
    /*关闭连接*/
    utl_smtp.quit(l_conn);
  
    /*异常处理*/
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
      RAISE;
    
  END;
BEGIN
  l_sendoraddress := '<' || p_sendor || '>';
  p_splite_str(p_receiver); --处理邮件地址
  FOR k IN 1 .. my_address_list.count LOOP
    p_email(l_sendoraddress
           ,my_address_list(k));
  END LOOP;
  /*处理邮件地址，根据逗号分割邮件*/
EXCEPTION
  WHEN OTHERS THEN
    --RAISE;
    dbms_output.put_line('邮件发送失败!');
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_call_stack);
END;

二、创建自动发送文件的存过。创建Oracle job，每天11点55发送执行该存过发送邮件。

创建自动发送文件的存过yw_dmp_bak_1150()，创建Oracle job，每天11点55发送执行该存过发送邮件。
在此之前，需要先自动备份好文件，否则发送失败。
存过存过yw_dmp_bak_1150()，代码如下：

CREATE OR REPLACE PROCEDURE yw_dmp_bak_1150
AUTHID CURRENT_USER is
 v_date VARCHAR2(20);
begin
  select to_char(sysdate,'yyyymmdd') into v_date from dual;
    --改用qq邮箱
  /*P_SEND_MAIL('【个人博客系统】-数据库备份-'||v_date||'-1150',--邮件正文
              '【个人博客系统】-数据库备份-'||v_date||'-1150',--邮件标题
              'tttzzzqqq2019@163.com',--发送邮箱
              'tttzzzqqq@qq.com',--接收邮箱
              'smtp.163.com',
              25,
              1,
              'tttzzzqqq2019@163.com',--发送邮箱的账号
              'dttzq84',--发送邮箱的密码
              'c:\dmp\'||v_date||'-1150-log.zip',
              'base64');*/
    P_SEND_MAIL('【个人博客系统】-数据库备份-'||v_date||'-1150',--邮件正文
              '【个人博客系统】-数据库备份-'||v_date||'-1150',--邮件标题
              'tzq949819874@qq.com',--发送邮箱
              'tttzzzqqq@qq.com',--接收邮箱
              'smtp.qq.com',
              25,
              1,
              'tzq949819874@qq.com',--发送邮箱的账号
              'pvypilackxqxbeai',--发送邮箱的密码
              'c:\dmp\'||v_date||'-1150-log.zip',
              'base64');
end yw_dmp_bak_1150;

三、设置Windows计划任务，每天11点50执行备份数据库的脚本

log_bak_1150.bat 脚本如下：

@echo off
set hour=%time:~,2%
if "%time:~,1%"==" " set hour=0%time:~1,1%
set d=%date:~0,4%%date:~5,2%%date:~8,2%-
::set t=%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%
::set t=%hour%%time:~3,2%%time:~6,2%%time:~9,2%

::set t=%hour%%time:~3,2%
set BACKUPDATE=%d%1150

::exp '%USER%/%PASSWORD%@%DATABASE% as sysdba' file='%dump_dir%%USER%_%BACKUPDATE%.dmp' log='%dump_dir%%USER%_%BACKUPDATE%.log' owner=%USER%

::set projectname=个人博客系统数据库备份
set projectname=log
set USER=log
set PASSWORD=1
set IP=127.0.0.1
set DATABASE=tzq

set dump_dir=%~dp0
set file_DATABASE=%IP%%DATABASE%
::set file_name=%BACKUPDATE%_%file_DATABASE%.%USER%
set file_name=%BACKUPDATE%-%USER%
::set file_name2=%BACKUPDATE%_%file_DATABASE%.%USER2%
::exp '%USER%/%PASSWORD%@%DATABASE%' file='%dump_dir%%file_name%.dmp' log='%dump_dir%%file_name%.log' owner=%USER% INDEXES=y grants=y constraints=y compress=y
::exp '%USER2%/%PASSWORD%@%DATABASE%' file='%dump_dir%%file_name2%.dmp' log='%dump_dir%%file_name2%.log' owner=%USER2% INDEXES=y grants=y constraints=y compress=y
::expdp tzq/1@tzq network_link=tzq_to_fs3i directory=tzqDump dumpfile=FS3I20180712_1518.DMP logfile=FS3I20180712_1518.LOG schemas=fs3i
::expdp tzq/1@tzq network_link=tzq_to_fs3i directory=tzqDump dumpfile='%file_name%.dmp' logfile='%file_name%.log' schemas=%USER%

::E:\app\Administrator\product\11.2.0\dbhome_1\BIN\exp.exe '%USER%/%PASSWORD%@%IP%/%DATABASE%' file='%dump_dir%%file_name%.dmp' log='%dump_dir%%file_name%.log' owner=%USER% INDEXES=y grants=y constraints=y compress=y
::exp '%USER%/%PASSWORD%@%IP%/%DATABASE%' file='%dump_dir%%file_name%.dmp' log='%dump_dir%%file_name%.log' tables=(IU_%%,YW_%%,TEST%%) INDEXES=y grants=y constraints=y compress=y
exp '%USER%/%PASSWORD%@%IP%/%DATABASE%' file='%dump_dir%%file_name%.dmp' log='%dump_dir%%file_name%.log' owner=%USER% INDEXES=y grants=y constraints=y compress=y
::exp '%USER%/%PASSWORD%@%IP%/%DATABASE%' file='%dump_dir%%file_name%.dmp' log='%dump_dir%%file_name%.log' tables=(ARTICLE_VIEW_COUNT_T,INFO,INFO_TYPE,KEYINFOMTBL,LOG_ARTICLE_COMMENT_T,LOG_VEHICLE_SEARCH_RECORDS_T,SYS_USER,TESTPW) INDEXES=y grants=y constraints=y compress=y
::start winrar a -ep1 -df "%dump_dir%%BACKUPDATE%_%projectname%.zip" "%dump_dir%%BACKUPDATE%_%file_DATABASE%.*"
start winrar a -ep1 -df "%dump_dir%%BACKUPDATE%-%projectname%.zip" "%dump_dir%%file_name%.*"
::del %dump_dir%%file_name%.dmp
::del %dump_dir%%file_name%.log