【IT168 服务器学院】 一个可以在Oracle发信的Procedure范例
CREATE OR REPLACE PROCEDURE "SYS"."SEND_MAIL"
(
pSender VARCHAR2,
pRecipient VARCHAR2,
pSubject VARCHAR2,
pMessage VARCHAR2)
IS
mailhost CONSTANT VARCHAR2(30) := ''YOUR MAIL SYSTEM IP OR FQDN'';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(1000);
mail_conn UTL_SMTP.CONNECTION;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := ''Date: '' || TO_CHAR( SYSDATE, ''dd Mon yy hh24:mi:ss'' ) || crlf || ''From: <''|| pSender ||''>'' || crlf || ''Subject: ''|| pSubject || crlf || ''To: ''||pRecipient || crlf || '''' || crlf || pMessage;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, pSender);
utl_smtp.rcpt(mail_conn, pRecipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
END send_mail;
使用范例
SQL> desc send_mail
PROCEDURE send_mail
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PSENDER VARCHAR2 IN
PRECIPIENT VARCHAR2 IN
PSUBJECT VARCHAR2 IN
PMESSAGE VARCHAR2 IN
SQL> exec send_mail(''apple'',''chu@odba.idv.tw'',''Test'',''hello'')
PL/SQL procedure successfully completed.
上面的范例是利用远端的mail service来寄信,简单又容易实做!!