oracle收發(fā)郵件存儲(chǔ)過程??

2012-04-13 13:33:32|??分類： .NET |??標(biāo)簽： |字號(hào)大中小?訂閱

CREATE OR REPLACE PROCEDURE PROC_SEND_EMAIL(P_TXT?????? VARCHAR2,
????????????????????????????????????????? P_SUB?????? VARCHAR2,
????????????????????????????????????????? P_SENDOR??? VARCHAR2,
????????????????????????????????????????? P_RECEIVER? VARCHAR2,
????????????????????????????????????????? P_SERVER??? VARCHAR2,
????????????????????????????????????????? P_PORT????? NUMBER DEFAULT 25,
????????????????????????????????????????? P_NEED_SMTP INT DEFAULT 0,
????????????????????????????????????????? P_USER????? VARCHAR2 DEFAULT NULL,
????????????????????????????????????????? P_PASS????? VARCHAR2 DEFAULT NULL,
????????????????????????????????????????? P_FILENAME? VARCHAR2 DEFAULT NULL,
????????????????????????????????????????? P_ENCODE??? VARCHAR2 DEFAULT 'bit 7')
? AUTHID CURRENT_USER IS
? /*
? 作用：用oracle發(fā)送郵件
? 主要功能：1、支持多收件人。
??????????? 2、支持中文
??????????? 3、支持抄送人
??????????? 4、支持大于32K的附件
??????????? 5、支持多行正文
??????????? 6、支持多附件
??????????? 7、支持文本附件和二進(jìn)制附件
??????????? 8、支持HTML格式
??????????? 8、支持
? 作者：suk
? 參數(shù)說明：
??????????? p_txt :郵件正文
??????????? p_sub: 郵件標(biāo)題
??????????? p_SendorAddress : 發(fā)送人郵件地址
??????????? p_ReceiverAddress : 接收地址，可以同時(shí)發(fā)送到多個(gè)地址上，地址之間用","或者";"隔開
??????????? p_EmailServer : 郵件服務(wù)器地址，可以是域名或者IP
??????????? p_Port ：郵件服務(wù)器端口
??????????? p_need_smtp：是否需要smtp認(rèn)證，0表示不需要，1表示需要
??????????? p_user：smtp驗(yàn)證需要的用戶名
??????????? p_pass：smtp驗(yàn)證需要的密碼
??????????? p_filename：附件名稱，必須包含完整的路徑，如"d:tempa.txt"。
??????????????????????? 可以有多個(gè)附件，附件名稱只見用逗號(hào)或者分號(hào)分隔
??????????? p_encode：附件編碼轉(zhuǎn)換格式，其中 p_encode='bit 7' 表示文本類型附件
???????????????????????????????????????????? p_encode='base64' 表示二進(jìn)制類型附件
? 注意：
??????? 1、對(duì)于文本類型的附件，不能用base64的方式發(fā)送，否則出錯(cuò)
??????? 2、對(duì)于多個(gè)附件只能用同一種格式發(fā)送
? */
? L_CRLF????????? VARCHAR2(2) := UTL_TCP.CRLF;
? L_SENDORADDRESS VARCHAR2(4000);
? L_SPLITE??????? VARCHAR2(10) := '++';
? BOUNDARY??????????? CONSTANT VARCHAR2(256) := '-----BYSUK';
? FIRST_BOUNDARY????? CONSTANT VARCHAR2(256) := '--' || BOUNDARY || L_CRLF;
? LAST_BOUNDARY?????? CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
??????????????????????????????????????????????? L_CRLF;
? MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' ||
??????????????????????????????????????????????? BOUNDARY || '"';
? /* 以下部分是發(fā)送大二進(jìn)制附件時(shí)用到的變量 */
? L_FIL???????????????? BFILE;
? L_FILE_LEN??????????? NUMBER;
? L_MODULO????????????? NUMBER;
? L_PIECES????????????? NUMBER;
? L_FILE_HANDLE???????? UTL_FILE.FILE_TYPE;
? L_AMT???????????????? BINARY_INTEGER := 672 * 3; /* ensures proper format;?? 2016 */
? L_FILEPOS???????????? PLS_INTEGER := 1; /* pointer for the file */
? L_CHUNKS????????????? NUMBER;
? L_BUF???????????????? RAW(2100);
? L_DATA??????????????? RAW(2100);
? L_MAX_LINE_WIDTH????? NUMBER := 54;
? L_DIRECTORY_BASE_NAME VARCHAR2(100) := 'DIR_FOR_SEND_MAIL';
? L_LINE??????????????? VARCHAR2(1000);
? L_MESG??????????????? VARCHAR2(32767);
? /* 以上部分是發(fā)送大二進(jìn)制附件時(shí)用到的變量 */
? TYPE ADDRESS_LIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
? MY_ADDRESS_LIST ADDRESS_LIST;
? TYPE ACCT_LIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
? MY_ACCT_LIST ACCT_LIST;
? -------------------------------------返回附件源文件所在目錄或者名稱--------------------------------------
? FUNCTION GET_FILE(P_FILE VARCHAR2, P_GET INT) RETURN VARCHAR2 IS
??? --p_get=1 表示返回目錄
??? --p_get=2 表示返回文件名
??? L_FILE VARCHAR2(1000);
? BEGIN
??? IF INSTR(P_FILE, '') > 0 THEN
????? --windows
????? IF P_GET = 1 THEN
??????? L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '', -1) - 1);
????? ELSIF P_GET = 2 THEN
??????? L_FILE := SUBSTR(P_FILE, - (LENGTH(P_FILE) - INSTR(P_FILE, '', -1)));
????? END IF;
??? ELSIF INSTR(P_FILE, '/') > 0 THEN
????? --linux/unix
????? IF P_GET = 1 THEN
??????? L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '/', -1) - 1);
????? ELSIF P_GET = 2 THEN
??????? L_FILE := SUBSTR(P_FILE, - (LENGTH(P_FILE) - INSTR(P_FILE, '/', -1)));
????? END IF;
??? END IF;
??? RETURN L_FILE;
? END;
? ---------------------------------------------刪除directory------------------------------------
? PROCEDURE DROP_DIRECTORY(P_DIRECTORY_NAME VARCHAR2) IS
? BEGIN
??? EXECUTE IMMEDIATE 'drop directory ' || P_DIRECTORY_NAME;
? EXCEPTION
??? WHEN OTHERS THEN
????? NULL;
? END;
? --------------------------------------------------創(chuàng)建directory-----------------------------------------
? PROCEDURE CREATE_DIRECTORY(P_DIRECTORY_NAME VARCHAR2, P_DIR VARCHAR2) IS
? BEGIN
??? EXECUTE IMMEDIATE 'create directory ' || P_DIRECTORY_NAME || ' as ''' ||
????????????????????? P_DIR || '''';
??? EXECUTE IMMEDIATE 'grant read,write on directory ' || P_DIRECTORY_NAME ||
????????????????????? ' to public';
? EXCEPTION
??? WHEN OTHERS THEN
????? RAISE;
? END;
? --------------------------------------------分割郵件地址或者附件地址-----------------------------------
? PROCEDURE P_SPLITE_STR(P_STR VARCHAR2, P_SPLITE_FLAG INT DEFAULT 1) IS
??? L_ADDR VARCHAR2(254) := '';
??? L_LEN? INT;
??? L_STR? VARCHAR2(4000);
??? J????? INT := 0; --表示郵件地址或者附件的個(gè)數(shù)
? BEGIN
??? /*處理接收郵件地址列表，包括去空格、將;轉(zhuǎn)換為,等*/
??? L_STR := TRIM(RTRIM(REPLACE(REPLACE(P_STR, ';', ','), ' ', ''), ','));
??? L_LEN := LENGTH(L_STR);
??? FOR I IN 1 .. L_LEN LOOP
????? IF SUBSTR(L_STR, I, 1) <> ',' THEN
??????? L_ADDR := L_ADDR || SUBSTR(L_STR, I, 1);
????? ELSE
??????? J := J + 1;
??????? IF P_SPLITE_FLAG = 1 THEN
????????? --表示處理郵件地址
????????? --前后需要加上'<>'，否則很多郵箱將不能發(fā)送郵件
????????? L_ADDR := '<' || L_ADDR || '>';
????????? --調(diào)用郵件發(fā)送過程
????????? MY_ADDRESS_LIST(J) := L_ADDR;
??????? ELSIF P_SPLITE_FLAG = 2 THEN
????????? --表示處理附件名稱
????????? MY_ACCT_LIST(J) := L_ADDR;
??????? END IF;
??????? L_ADDR := '';
????? END IF;
????? IF I = L_LEN THEN
??????? J := J + 1;
??????? IF P_SPLITE_FLAG = 1 THEN
????????? --調(diào)用郵件發(fā)送過程
????????? L_ADDR := '<' || L_ADDR || '>';
????????? MY_ADDRESS_LIST(J) := L_ADDR;
??????? ELSIF P_SPLITE_FLAG = 2 THEN
????????? MY_ACCT_LIST(J) := L_ADDR;
??????? END IF;
????? END IF;
??? END LOOP;
? END;
? ------------------------------------------------寫郵件頭和郵件內(nèi)容------------------------------------------
? PROCEDURE WRITE_DATA(P_CONN?? IN OUT NOCOPY UTL_SMTP.CONNECTION,
?????????????????????? P_NAME?? IN VARCHAR2,
?????????????????????? P_VALUE? IN VARCHAR2,
?????????????????????? P_SPLITE VARCHAR2 DEFAULT ':',
?????????????????????? P_CRLF?? VARCHAR2 DEFAULT L_CRLF) IS
? BEGIN
??? /* utl_raw.cast_to_raw 對(duì)解決中文亂碼問題很重要*/
??? UTL_SMTP.WRITE_RAW_DATA(P_CONN,
??????????????????????????? UTL_RAW.CAST_TO_RAW(CONVERT(P_NAME || P_SPLITE ||
???????????????????????????????????????????????????????? P_VALUE || P_CRLF,
???????????????????????????????????????????????????????? 'ZHS16GBK')));
? END;
? ----------------------------------------寫MIME郵件尾部-----------------------------------------------------
? PROCEDURE END_BOUNDARY(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
???????????????????????? LAST IN BOOLEAN DEFAULT FALSE) IS
? BEGIN
??? UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);
??? IF (LAST) THEN
????? UTL_SMTP.WRITE_DATA(CONN, LAST_BOUNDARY);
??? END IF;
? END;
? ----------------------------------------------發(fā)送附件----------------------------------------------------
? PROCEDURE ATTACHMENT(CONN???????? IN OUT NOCOPY UTL_SMTP.CONNECTION,
?????????????????????? MIME_TYPE??? IN VARCHAR2 DEFAULT 'text/plain',
?????????????????????? INLINE?????? IN BOOLEAN DEFAULT TRUE,
?????????????????????? FILENAME???? IN VARCHAR2 DEFAULT 't.txt',
?????????????????????? TRANSFER_ENC IN VARCHAR2 DEFAULT '7 bit',
?????????????????????? DT_NAME????? IN VARCHAR2 DEFAULT '0') IS

??? L_FILENAME VARCHAR2(1000);
? BEGIN
??? --寫附件頭
??? UTL_SMTP.WRITE_DATA(CONN, FIRST_BOUNDARY);
??? --設(shè)置附件格式
??? WRITE_DATA(CONN, 'Content-Type', MIME_TYPE);
??? --如果文件名稱非空，表示有附件
??? DROP_DIRECTORY(DT_NAME);
??? --創(chuàng)建directory
??? CREATE_DIRECTORY(DT_NAME, GET_FILE(FILENAME, 1));
??? --得到附件文件名稱
??? L_FILENAME := GET_FILE(FILENAME, 2);
??? IF (INLINE) THEN
????? WRITE_DATA(CONN, 'Content-Disposition',
???????????????? 'inline; filename="' || L_FILENAME || '"');
??? ELSE
????? WRITE_DATA(CONN, 'Content-Disposition',
???????????????? 'attachment; filename="' || L_FILENAME || '"');
??? END IF;
??? --設(shè)置附件的轉(zhuǎn)換格式
??? IF (TRANSFER_ENC IS NOT NULL) THEN
????? WRITE_DATA(CONN, 'Content-Transfer-Encoding', TRANSFER_ENC);
??? END IF;

??? UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);

??? --begin 貼附件內(nèi)容
??? IF TRANSFER_ENC = 'bit 7' THEN
????? --如果是文本類型的附件
????? BEGIN
??????? L_FILE_HANDLE := UTL_FILE.FOPEN(DT_NAME, L_FILENAME, 'r'); --打開文件
??????? --把附件分成多份，這樣可以發(fā)送超過32K的附件
??????? LOOP
????????? UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);
????????? L_MESG := L_LINE || L_CRLF;
????????? WRITE_DATA(CONN, '', L_MESG, '', '');
??????? END LOOP;
??????? UTL_FILE.FCLOSE(L_FILE_HANDLE);
??????? END_BOUNDARY(CONN);
????? EXCEPTION
??????? WHEN OTHERS THEN
????????? UTL_FILE.FCLOSE(L_FILE_HANDLE);
????????? END_BOUNDARY(CONN);
????????? NULL;
????? END; --結(jié)束文本類型附件的處理

??? ELSIF TRANSFER_ENC = 'base64' THEN
????? --如果是二進(jìn)制類型的附件
????? BEGIN
??????? --把附件分成多份，這樣可以發(fā)送超過32K的附件
??????? L_FILEPOS? := 1; --重置offset，在發(fā)送多個(gè)附件時(shí)，必須重置
??????? L_FIL????? := BFILENAME(DT_NAME, L_FILENAME);
??????? L_FILE_LEN := DBMS_LOB.GETLENGTH(L_FIL);
??????? L_MODULO?? := MOD(L_FILE_LEN, L_AMT);
??????? L_PIECES?? := TRUNC(L_FILE_LEN / L_AMT);
??????? IF (L_MODULO <> 0) THEN
????????? L_PIECES := L_PIECES + 1;
??????? END IF;
??????? DBMS_LOB.FILEOPEN(L_FIL, DBMS_LOB.FILE_READONLY);
??????? DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);
??????? L_DATA := NULL;
??????? FOR I IN 1 .. L_PIECES LOOP
????????? L_FILEPOS? := I * L_AMT + 1;
????????? L_FILE_LEN := L_FILE_LEN - L_AMT;
????????? L_DATA???? := UTL_RAW.CONCAT(L_DATA, L_BUF);
????????? L_CHUNKS?? := TRUNC(UTL_RAW.LENGTH(L_DATA) / L_MAX_LINE_WIDTH);
????????? IF (I <> L_PIECES) THEN
??????????? L_CHUNKS := L_CHUNKS - 1;
????????? END IF;
????????? UTL_SMTP.WRITE_RAW_DATA(CONN, UTL_ENCODE.BASE64_ENCODE(L_DATA));
????????? L_DATA := NULL;
????????? IF (L_FILE_LEN < L_AMT AND L_FILE_LEN > 0) THEN
??????????? L_AMT := L_FILE_LEN;
????????? END IF;
????????? DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);
??????? END LOOP;
??????? DBMS_LOB.FILECLOSE(L_FIL);
??????? END_BOUNDARY(CONN);
????? EXCEPTION
??????? WHEN OTHERS THEN
????????? DBMS_LOB.FILECLOSE(L_FIL);
????????? END_BOUNDARY(CONN);
????????? RAISE;
????? END; --結(jié)束處理二進(jìn)制附件

??? END IF; --結(jié)束處理附件內(nèi)容
??? DROP_DIRECTORY(DT_NAME);
? END; --結(jié)束過程ATTACHMENT
? ---------------------------------------------真正發(fā)送郵件的過程--------------------------------------------
? PROCEDURE P_EMAIL(P_SENDORADDRESS2?? VARCHAR2, --發(fā)送地址
??????????????????? P_RECEIVERADDRESS2 VARCHAR2) --接受地址
?? IS
??? L_CONN UTL_SMTP.CONNECTION; --定義連接
? BEGIN
??? /*初始化郵件服務(wù)器信息，連接郵件服務(wù)器*/
??? L_CONN := UTL_SMTP.OPEN_CONNECTION(P_SERVER, P_PORT);
??? UTL_SMTP.HELO(L_CONN, P_SERVER);
??? /* smtp服務(wù)器登錄校驗(yàn) */
??? IF P_NEED_SMTP = 1 THEN
????? UTL_SMTP.COMMAND(L_CONN, 'AUTH LOGIN', '');
????? UTL_SMTP.COMMAND(L_CONN,
?????????????????????? UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USER))));
????? UTL_SMTP.COMMAND(L_CONN,
?????????????????????? UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_PASS))));
??? END IF;

??? /*設(shè)置發(fā)送地址和接收地址*/
??? UTL_SMTP.MAIL(L_CONN, P_SENDORADDRESS2);
??? UTL_SMTP.RCPT(L_CONN, P_RECEIVERADDRESS2);

??? /*設(shè)置郵件頭*/
??? UTL_SMTP.OPEN_DATA(L_CONN);

??? WRITE_DATA(L_CONN, 'Date', TO_CHAR(SYSDATE, 'yyyymmdd hh24:mi:ss'));
??? /*設(shè)置發(fā)送人*/
??? WRITE_DATA(L_CONN, 'From', P_SENDOR);
??? /*設(shè)置接收人*/
??? WRITE_DATA(L_CONN, 'To', P_RECEIVER);
??? /*設(shè)置郵件主題*/
??? WRITE_DATA(L_CONN, 'Subject', P_SUB);

??? WRITE_DATA(L_CONN, 'Content-Type', MULTIPART_MIME_TYPE);
??? UTL_SMTP.WRITE_DATA(L_CONN, UTL_TCP.CRLF);
??? UTL_SMTP.WRITE_DATA(L_CONN, FIRST_BOUNDARY);
??? WRITE_DATA(L_CONN, 'Content-Type', 'text/plain;charset=gb2312');
??? --單獨(dú)空一行，否則，正文內(nèi)容不顯示
??? UTL_SMTP.WRITE_DATA(L_CONN, UTL_TCP.CRLF);
??? /* 設(shè)置郵件正文
????? 把分隔符還原成chr(10)。這主要是為了shell中調(diào)用該過程，如果有多行，則先把多行的內(nèi)容合并成一行，并用 l_splite分隔
????? 然后用 l_crlf替換chr(10)。這一步是必須的，否則將不能發(fā)送郵件正文有多行的郵件
??? */
??? WRITE_DATA(L_CONN, '',
?????????????? REPLACE(REPLACE(P_TXT, L_SPLITE, CHR(10)), CHR(10), L_CRLF),
?????????????? '', '');
??? END_BOUNDARY(L_CONN);

??? --如果文件名稱不為空，則發(fā)送附件
??? IF (P_FILENAME IS NOT NULL) THEN
????? --根據(jù)逗號(hào)或者分號(hào)拆分附件地址
????? P_SPLITE_STR(P_FILENAME, 2);
????? --循環(huán)發(fā)送附件(在同一個(gè)郵件中)
????? FOR K IN 1 .. MY_ACCT_LIST.COUNT LOOP
??????? ATTACHMENT(CONN => L_CONN, FILENAME => MY_ACCT_LIST(K),
?????????????????? TRANSFER_ENC => P_ENCODE,
?????????????????? DT_NAME => L_DIRECTORY_BASE_NAME || TO_CHAR(K));
????? END LOOP;
??? END IF;

??? /*關(guān)閉數(shù)據(jù)寫入*/
??? UTL_SMTP.CLOSE_DATA(L_CONN);
??? /*關(guān)閉連接*/
??? UTL_SMTP.QUIT(L_CONN);

??? /*異常處理*/
? EXCEPTION
??? WHEN OTHERS THEN
????? NULL;
????? RAISE;

? END;
? ---------------------------------------------------主過程-----------------------------------------------------
BEGIN
? L_SENDORADDRESS := '<' || P_SENDOR || '>';
? P_SPLITE_STR(P_RECEIVER); --處理郵件地址
? FOR K IN 1 .. MY_ADDRESS_LIST.COUNT LOOP
??? P_EMAIL(L_SENDORADDRESS, MY_ADDRESS_LIST(K));
? END LOOP;
? /*處理郵件地址，根據(jù)逗號(hào)分割郵件*/
EXCEPTION
? WHEN OTHERS THEN
??? RAISE;
END;
====================================================================

?

使用實(shí)例代碼:

?? SQL>set serverout on
?? SQL>exec PROCSENDEMAIL('中文測試郵件','中文主題','space6212@163.com','space6212@163.com,susk@souchang.com','202.108.5.85',25,1,'xxxx','xxxx','/tmp/a.jpg,/tmp/b.jpg','base64');
??? PL/SQL procedure successfully completed.?

轉(zhuǎn)載于:https://www.cnblogs.com/lteal/archive/2012/12/19/2824639.html

總結(jié)

以上是生活随笔為你收集整理的oracle收发邮件存储过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。