时间:2021-07-01 10:21:17 帮助过:12人阅读
哎,每次都介绍,有啥好介绍的呢;好吧, 版权完全是我的,转载要说明出处哦。 无 CREATE OR REPLACE PROCEDURE SEND_MAIL_one(as_recp in varchar2, --邮件接收者 as_subject in varchar2, --邮件标题 as_msg_body in varchar2) --邮件内容 IS ls_mailhost v
哎,每次都介绍,有啥好介绍的呢;好吧, 版权完全是我的,转载要说明出处哦。 <无>CREATE OR REPLACE PROCEDURE SEND_MAIL_one(as_recp in varchar2, --邮件接收者 as_subject in varchar2, --邮件标题 as_msg_body in varchar2) --邮件内容 IS ls_mailhost varchar2(30) := '192.168.0.1'; -- 邮件服务地址 lc_mail_conn utl_smtp.connection; ls_subject varchar2(100); ls_msg_body varchar2(20000); --邮件服务器上发件人的帐号和密码 ls_username varchar2(256) := 'fajian@xxx.com'; ls_password varchar2(256) := '123456'; BEGIN lc_mail_conn := utl_smtp.open_connection(ls_mailhost, 25);--在特定地址特定端口获得链接 utl_smtp.helo(lc_mail_conn, ls_mailhost); utl_smtp.command(lc_mail_conn, 'AUTH LOGIN'); utl_smtp.command(lc_mail_conn, demo_base64.encode(utl_raw.cast_to_raw(ls_username))); utl_smtp.command(lc_mail_conn, demo_base64.encode(utl_raw.cast_to_raw(ls_password))); ls_subject := 'Subject: [' || upper(sys_context('userenv', 'db_name')) || '] - ' || convert(as_subject, 'ZHS16GBK', 'AL32UTF8'); ls_msg_body := convert(as_msg_body, 'ZHS16GBK', 'AL32UTF8'); utl_smtp.mail(lc_mail_conn, '<' || '发件人sxgkwei' || '>'); utl_smtp.rcpt(lc_mail_conn, '<' || as_recp || '>'); utl_smtp.open_data(lc_mail_conn); ls_msg_body := 'From: ' || '发件人sxgkwei' || chr(13) || chr(10) || 'To: ' || as_recp || chr(13) || chr(10) || ls_subject || chr(13) || chr(10) || chr(13) || chr(10) || ls_msg_body; utl_smtp.write_raw_data(lc_mail_conn, utl_raw.cast_to_raw(ls_msg_body)); utl_smtp.close_data(lc_mail_conn); utl_smtp.quit(lc_mail_conn); EXCEPTION WHEN UTL_SMTP.INVALID_OPERATION THEN dbms_output.put_line('invalid operation'); WHEN UTL_SMTP.TRANSIENT_ERROR THEN dbms_output.put_line('transient error'); WHEN UTL_SMTP.PERMANENT_ERROR THEN dbms_output.put_line('permanent error'); WHEN OTHERS THEN dbms_output.put_line('others'); end SEND_MAIL_one;
CREATE OR REPLACE PROCEDURE SEND_MAIL(as_recp in varchar2, --邮件接收者,多接收者可用';'号分隔 as_subject in varchar2, --邮件标题 as_msg_body in varchar2) --邮件内容 IS type myArr is table of varchar2(500) index by binary_integer; i number; l_idx number; recp varchar2(500); as_recp_arr myArr; BEGIN recp := as_recp; i := 1; l_idx := instr(recp, ';'); while l_idx > 0 loop as_recp_arr(i) := substr(recp, 1, l_idx - 1); recp := substr(recp, l_idx + 1); i := i + 1; l_idx := instr(recp, ';'); end loop; if length(recp) > 1 then as_recp_arr(i) := recp; end if; for i in 1 .. as_recp_arr.count loop SEND_MAIL_one(as_recp_arr(i), as_subject, as_msg_body); end loop; end send_mail;