ORACLE 發送郵件代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 ORACLE 發送郵件
作者:Dezai.CN / 发布于2011/7/7/ 959
<div>
CREATE OR REPLACE PROCEDURE send_mail (p_From IN VARCHAR2
,p_To IN VARCHAR2
,p_Subject IN VARCHAR2
,p_Body IN VARCHAR2
,p_Cc IN VARCHAR2 := NULL
,p_Bcc IN VARCHAR2 := NULL
,p_ContentType IN VARCHAR2 := 'text/plain;charset=big5'
,p_MailIp IN VARCHAR2 := '10.98.0.211' --'10.98.0.28'
,p_Port IN NUMBER := 25) IS
v_Connection utl_smtp.connection;
v_Data RAW(32767);
v_email varchar2(100);
v_count number;
v_start number;
v_end number;
v_date varchar2(50);
BEGIN
v_date:=to_char(sysdate-1/3,'YYYY-mm-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN');
v_Connection := UTL_smtp.open_connection(p_MailIp,p_Port);
UTL_smtp.ehlo(v_Connection, p_MailIp);
UTL_smtp.mail(v_Connection, p_From);
IF (p_to IS NOT NULL) THEN
v_count := 1;
v_start := 0;
v_end :=1;
for c in 1..length(p_to) loop
if c>v_start then
v_end := instr(p_to,';',1,v_count);
if v_end>0 then
UTL_smtp.rcpt(v_Connection,substr(p_to,v_start+1,v_end-v_start-1));
else
UTL_smtp.rcpt(v_Connection,substr(p_to,v_start+1,length(p_to)-v_start));
exit;
end if;
v_start := v_end;
v_count := v_count +1;
end if;
end loop;
END IF;
IF (p_Cc IS NOT NULL) THEN
v_count := 1;
v_start := 0;
v_end :=1;
for c in 1..length(p_Cc) loop
if c>v_start then
v_end := instr(p_Cc,';',1,v_count);
if v_end>0 then
UTL_smtp.rcpt(v_Connection,substr(p_Cc,v_start+1,v_end-v_start-1));
else
UTL_smtp.rcpt(v_Connection,substr(p_Cc,v_start+1,length(p_Cc)-v_start));
exit;
end if;
v_start := v_end;
v_count := v_count +1;
end if;
end loop;
END IF;
IF (p_Bcc IS NOT NULL) THEN
v_count := 1;
v_start := 0;
v_end :=1;
for c in 1..length(p_Bcc) loop
if c>v_start then
v_end := instr(p_Bcc,';',1,v_count);
if v_end>0 then
UTL_smtp.rcpt(v_Connection,substr(p_Bcc,v_start+1,v_end-v_start-1));
else
UTL_smtp.rcpt(v_Connection,substr(p_Bcc,v_start+1,length(p_Bcc)-v_start));
exit;
end if;
v_start := v_end;
v_count := v_count +1;
end if;
end loop;
END IF;
UTL_smtp.open_data(v_Connection);
--select to_char(sysdate,'YYYY-MON-DD','NLS_DATE_LANGUAGE=AMERICAN') from dual
/* ** Sending the header information */
UTL_smtp.write_data(v_Connection,'Date: ' || to_char(sysdate-1/3,'YYYY-MON-DD hh24:mi:ss','NLS_DATE_LANGUAGE=AMERICAN') ||UTL_tcp.CRLF);
--UTL_smtp.write_data(v_Connection,'Date: ' || v_date ||UTL_tcp.CRLF);
--UTL_smtp.write_data(v_Connection,'Date: ' || 'NOW' || UTL_tcp.CRLF);
IF (p_From IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'From: ' ||p_From|| UTL_tcp.CRLF);
END IF;
IF (p_to IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'To: ' || p_to|| UTL_tcp.CRLF);
END IF;
IF (p_Cc IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'Cc: ' || p_cc || UTL_tcp.CRLF);
END IF;
IF (p_Bcc IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'Bcc: ' || p_bcc || UTL_tcp.CRLF);
END IF;
--UTL_smtp.write_data(v_Connection, 'Subject: ' || p_Subject || UTL_tcp.CRLF); //以下可以顯示中文subject
UTL_smtp.write_raw_data(v_Connection,utl_raw.cast_to_raw(convert('Subject: ' ||p_Subject||utl_tcp.crlf,'zht16big5'))); 
UTL_smtp.write_data(v_Connection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);
UTL_smtp.write_data(v_Connection, 'Content-Type: ' ||p_ContentType|| UTL_tcp.CRLF);
UTL_smtp.write_data(v_Connection, 'Content-Transfer-Encoding: ' || '8bit' || UTL_tcp.CRLF);
/* ** End of header information */
UTL_smtp.write_data(v_Connection, UTL_tcp.CRLF);
/* ** Actual body is sent here */
v_Data := utl_raw.cast_to_raw(p_body);
UTL_smtp.write_raw_data(v_Connection, v_Data);
/* ** Connection is closed here */
UTL_smtp.close_data(v_Connection);
UTL_smtp.quit(v_Connection);
EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(v_Connection);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(v_Connection);
dbms_output.put_line(sqlerrm);
END send_mail;

-- End of DDL script for SEND_EMAIL
--執行存儲過程
begin
send_mail(<a href="mailto:'cg.du@mic.com.tw','duchenggang.accp@163.com','ceshi','"><font color="#0066cc">'cg.du@mic.com.tw','duchenggang.accp@163.com','ceshi','</font></a>這是我用oracle寫的發送郵件'); <a href="mailto:--cg.du@mic.com.tw"><font color="#0066cc">--cg.du@mic.com.tw</font></a> 代表來自誰 <a href="mailto:duchenggang.accp@163.com"><font color="#0066cc">duchenggang.accp@163.com</font></a> 代表發送給誰 ，test代表標題 ，這是我用oracle寫的發送郵件 代表郵件內容
end;

</div>
试试其它关键字
　發送郵件　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)