Oracle中用于发送邮件的存储过程
1 create or replace procedure sendmail( 2 p_To in varchar2,--目标 3 p_Subject in varchar2,--主题 4 p_content in varchar2--内容，可包含Html 5 ) is 6 7 v_From varchar2(100); 8 v_Smtp_Host varchar2(100); 9 v_Smtp_Port varchar2(100); 10 11 v_Account varchar2(100); 12 v_Account_exchange varchar2(100); 13 v_PassWord varchar2(100); 14 v_PassWord_exchange varchar2(100); 15 16 v_Connection utl_smtp.connection; 17 18 v_Temp varchar2(32767) default null; 19 v_Boundary varchar2(256) default 'a1b2c3d4e3f2g1'; 20 21 v_Body clob :=empty_clob();--邮件信息 22 23 v_Offset number;--正文偏移量 24 v_Amount number;--字符长度 25 26 begin 27 v_From:='test@163.com';--发件人地址 28 v_Smtp_Host:='smtp.163.com';--163邮箱 29 v_Smtp_Port:='25';--端口号，一般都是25 30 v_Account:='test@163.com';--登录账号 31 v_Account_exchange:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_Account)));--要转换 32 v_PassWord:='123456';--密码 33 v_PassWord_exchange:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_PassWord)));--要转换 34 35 --连接服务器 36 v_Connection:=utl_smtp.open_connection(host => v_Smtp_Host,port => v_Smtp_Port); 37 --utl_smtp.helo(v_Connection,v_Smtp_Host);--问候服务器 38 utl_smtp.ehlo(v_Connection,v_Smtp_Host);--问候服务器 39 40 -- 登录 41 utl_smtp.command(v_Connection,'Auth Login');--用AUTH LOGIN选项使用安全登录功能 42 --utl_smtp.command(v_Connection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_Account)))); 43 --utl_smtp.command(v_Connection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_PassWord)))); 44 utl_smtp.command(v_Connection,v_Account_exchange); 45 utl_smtp.command(v_Connection,v_PassWord_exchange); 46 47 --设置发件人和收件人 48 utl_smtp.mail(v_Connection,'<'||v_From||'>');--发件人信息 49 utl_smtp.rcpt(v_Connection,'<'||p_To||'>');--收件人信息 50 --记得在两边加上<>，否则出错 51 52 --初始化Clob 53 dbms_lob.createtemporary(v_Body,true);--初始化Clob 54 55 --头信息 56 v_Temp:=v_Temp||'MIME-Version:1.0'||chr(13)||chr(10); 57 v_Temp:=v_Temp||'To: '||p_To||chr(13)||chr(10); 58 v_Temp:=v_Temp||'From: '||v_From||chr(13)||chr(10); 59 v_Temp:=v_Temp||'Subject: '||p_Subject||chr(13)||chr(10); 60 v_Temp:=v_Temp||'Reply-To: '||v_From||chr(13)||chr(10); 61 v_Temp:=v_Temp||'Content-Type: multipart/alternative; boundary= '||chr(34)||v_Boundary||chr(34)||chr(13)||chr(10); 62 dbms_lob.write(v_Body,length(v_Temp),1,v_Temp);--将v_Temp中信息写入v_body 63 64 --正文 65 v_Temp:=chr(13)||chr(10)||'--'||v_Boundary||'--'||chr(13)||chr(10); 66 v_Temp:=v_Temp||'Content-Type:text/plain;charset=us-ascii'||chr(13)||chr(10)||chr(13)||chr(10); 67 --v_Offset:=dbms_lob.getlength(v_Body)+1; 68 --dbms_lob.write(v_Body,length(v_Temp),v_Offset,v_Temp);或者 69 dbms_lob.writeappend(v_Body,length(v_Temp),v_Temp); 70 71 dbms_lob.writeappend(v_Body,length(p_content),p_content); 72 73 --末尾分界线 74 v_Temp:=chr(13)||chr(10)||chr(13)||chr(10)||'--'||v_Boundary||'--'||chr(13)||chr(10)||chr(13)||chr(10); 75 dbms_lob.writeappend(v_Body,length(v_Temp),v_Temp); 76 77 --发送邮件 78 v_Offset:=1; 79 v_Amount:=1900; 80 utl_smtp.open_data(v_Connection); 81 82 while v_Offset<dbms_lob.getlength(v_Body)+1 83 loop 84 utl_smtp.write_raw_data(v_Connection,utl_raw.cast_to_raw(dbms_lob.substr(v_Body,dbms_lob.getlength(v_Body),1))); 85 v_Offset:=v_Offset+v_Amount; 86 v_Amount:=least(1900,dbms_lob.getlength(v_Body)-v_Amount); 87 end loop; 88 89 --关闭连接 90 utl_smtp.close_data(v_Connection); 91 utl_smtp.quit(v_Connection); 92 --释放CLOB 93 dbms_lob.freetemporary(v_Body); 94 95 --成功写信息 96 dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh-mi-ss')||'发送成功！'); 97 98 --捕获异常 99 Exception 100 when others then 101 dbms_output.put_line(dbms_utility.format_error_stack); 102 dbms_output.put_line(dbms_utility.format_call_stack); 103 dbms_output.put_line(sqlerrm); 104 105 end sendmail;
该存储过程在oracle 10g下编写，已测试通过。
