Oracle發送郵件存儲過程

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;