- --对单个人发邮件
- 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;
- --该片段来自于http://www.codesnippet.cn/detail/270520133594.html
来源: http://www.codesnippet.cn/detail/270520133594.html