实现在oracle中发邮件功能

首先将如下代码在相关oracle数据库内编译创建，然后直接传入相应的参数即可调用！

 

代码如下：

------------------------------------------------------------------------------------------------------------------------------

create or replace procedure oms_sendmail_prc(p_sendor    varchar2 ----发件人
                                            ,p_receiver  varchar2 ----接受人
                                            ,p_sub       varchar2 ----主题
                                            ,p_txt       varchar2 ----内容
                                            ,p_server    varchar2 ----邮件服务器地址，可以是域名或者IP，推荐域名，IP可能变
                                            ,p_port      number default 25 ----邮件服务器端口
                                            ,p_need_smtp int default 0 ----是否需要smtp认证，0表示不需要，1表示需要
                                            ,p_user      varchar2 default null ----邮箱账户
                                            ,p_pass      varchar2 default null ----账户密码
                                            ,p_filename  varchar2 default null
                                            ,p_encode    varchar2 default 'bit 7'
                                            ,retcode     out number
                                            ,errbuf      out varchar2)
/*
   ===============================================================
   *    Program Name:   oms_sendmail_prc
   *    Author      :  jackychen
   *    Date        :   2009-7-30 下午 13:30:00
   *    Purpose     :   公用sql发邮件功能
   *
   *    Update History
   *    Version     Date         Name            Description
   *    --------  ----------  ---------------  --------------------
   *     V1.0      2009-7-30    jackychen        Creation
   --------------------------------------------------------------
   作用：用oracle发送邮件
   主要功能：1、支持多收件人。
             2、支持中文
             3、支持抄送人
             4、支持大于32K的附件
             5、支持多行正文
             6、支持多附件
             7、支持文本附件和二进制附件
             8、支持HTML格式
             8、支持。。。。待完善！
   --------------------------------------------------------------
   参数说明：
             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、对于多个附件只能用同一种格式发送
   ----------------------------------------------------------------
  调用示例：
         declare
             v_temp  number;
             v_temp2 varchar2(1000);
         begin
           oms_sendmail_prc('test@***.com.cn'
                           ,'test@***.com.cn'
                           ,'邮件主题'
                           ,'邮件内容'
                           ,'192.168.199.168' --代理
                           ,25 --端口
                           ,1
                           ,'****'  --邮箱账户
                           ,'****'  --邮箱密码
                           ,''
                           ,'base64' --这个可以固定
                           ,v_num     --返回值
                           ,v_temp); --返回内容
           dbms_output.put_line(v_num || '
                               ,' || v_temp);
         end;
   ===============================================================
   */
authid current_user is
  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
  -------------------------------------------------------------

  -------------------------------------------------------------
  -----创建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
      retcode := 1;
      errbuf  := 'create directory error!';
      --raise;
  end;
  -----创建directory
  -------------------------------------------------------------

  -------------------------------------------------------------
  -----分割邮件地址或者附件地址
  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);
          retcode := 2;
          errbuf  := 'process attachments error!';
      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
      retcode := 3;
      errbuf  := 'prcess mailsevers information error!';
   
  end;
  -----发送邮件的过程
  -------------------------------------------------------------

  -------------------------------------------------------------
  -------------------------------------------------------------
begin
  -------------------------------------------------------------
  ----主过程
  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
      retcode := 4;
      errbuf  := 'send mail error!';
  end;
  ----主过程
  -------------------------------------------------------------
  -------------------------------------------------------------

end oms_sendmail_prc;