你的位置:首页 > 数据库

[数据库]oracle发送邮件


 1 CREATE OR REPLACE PROCEDURE PROCSENDEMAIL(P_TXT    VARCHAR2, 2                      P_SUB    VARCHAR2, 3                      P_SENDOR  VARCHAR2, 4                      P_RECEIVER VARCHAR2, 5                      P_SERVER  VARCHAR2, 6                      P_PORT   NUMBER DEFAULT 25, 7                      P_NEED_SMTP INT DEFAULT 0, 8                      P_USER   VARCHAR2 DEFAULT NULL, 9                      P_PASS   VARCHAR2 DEFAULT NULL, 10                      P_FILENAME VARCHAR2 DEFAULT NULL, 11                      P_ENCODE  VARCHAR2 DEFAULT 'bit 7') 12  AUTHID CURRENT_USER IS 13  /* 14  作用:用oracle发送邮件 15  主要功能:1、支持多收件人。 16       2、支持中文 17       3、支持抄送人 18       4、支持大于32K的附件 19       5、支持多行正文 20       6、支持多附件 21       7、支持文本附件和二进制附件 22       8、支持HTML格式 23       8、支持 24  作者:suk 25  参数说明: 26       p_txt :邮件正文 27       p_sub: 邮件标题 28       p_SendorAddress : 发送人邮件地址 29       p_ReceiverAddress : 接收地址,可以同时发送到多个地址上,地址之间用","或者";"隔开 30       p_EmailServer : 邮件服务器地址,可以是域名或者IP 31       p_Port :邮件服务器端口 32       p_need_smtp:是否需要smtp认证,0表示不需要,1表示需要 33       p_user:smtp验证需要的用户名 34       p_pass:smtp验证需要的密码 35       p_filename:附件名称,必须包含完整的路径,如"d:\temp\a.txt"。 36             可以有多个附件,附件名称只见用逗号或者分号分隔 37       p_encode:附件编码转换格式,其中 p_encode='bit 7' 表示文本类型附件 38                        p_encode='base64' 表示二进制类型附件 39  注意: 40     1、对于文本类型的附件,不能用base64的方式发送,否则出错 41     2、对于多个附件只能用同一种格式发送 42  */ 43  44  L_CRLF VARCHAR2(2) := UTL_TCP.CRLF; 45  L_SENDORADDRESS VARCHAR2(4000); 46  L_SPLITE    VARCHAR2(10) := '++'; 47  BOUNDARY      CONSTANT VARCHAR2(256) := '-----BYSUK'; 48  FIRST_BOUNDARY   CONSTANT VARCHAR2(256) := '--' || BOUNDARY || L_CRLF; 49  LAST_BOUNDARY    CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' || 50                         L_CRLF; 51  MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' || 52                         BOUNDARY || '"'; 53  /* 以下部分是发送大二进制附件时用到的变量 */ 54  L_FIL         BFILE; 55  L_FILE_LEN      NUMBER; 56  L_MODULO       NUMBER; 57  L_PIECES       NUMBER; 58  L_FILE_HANDLE     UTL_FILE.FILE_TYPE; 59  L_AMT         BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */ 60  L_FILEPOS       PLS_INTEGER := 1; /* pointer for the file */ 61  L_CHUNKS       NUMBER; 62  L_BUF         RAW(2100); 63  L_DATA        RAW(2100); 64  L_MAX_LINE_WIDTH   NUMBER := 54; 65  L_DIRECTORY_BASE_NAME VARCHAR2(100) := 'DIR_FOR_SEND_MAIL'; 66  L_LINE        VARCHAR2(1000); 67  L_MESG        VARCHAR2(32767); 68  /* 以上部分是发送大二进制附件时用到的变量 */ 69  70  TYPE ADDRESS_LIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; 71  MY_ADDRESS_LIST ADDRESS_LIST; 72  TYPE ACCT_LIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; 73  MY_ACCT_LIST ACCT_LIST; 74  -------------------------------------返回附件源文件所在目录或者名称-------------------------------------- 75  FUNCTION GET_FILE(P_FILE VARCHAR2, 76           P_GET INT) RETURN VARCHAR2 IS 77   --p_get=1 表示返回目录 78   --p_get=2 表示返回文件名 79   L_FILE VARCHAR2(1000); 80  BEGIN 81    IF P_GET = 1 THEN 82     L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '\', -1) - 1); 83    ELSIF P_GET = 2 THEN 84     L_FILE := SUBSTR(P_FILE, - (LENGTH(P_FILE) - INSTR(P_FILE, '\', -1))); 85    END IF; 86   /* 87    IF INSTR(P_FILE, '\') > 0 THEN 88    --windows 89    IF P_GET = 1 THEN 90     L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '\', -1) - 1); 91    ELSIF P_GET = 2 THEN 92     L_FILE := SUBSTR(P_FILE, - (LENGTH(P_FILE) - INSTR(P_FILE, '\', -1))); 93    END IF; 94   ELSIF INSTR(P_FILE, '/') > 0 THEN 95    --linux/unix 96    IF P_GET = 1 THEN 97     L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, '/', -1) - 1); 98    ELSIF P_GET = 2 THEN 99     L_FILE := SUBSTR(P_FILE, - (LENGTH(P_FILE) - INSTR(P_FILE, '/', -1)));100    END IF;101   END IF;102   */103 104   RETURN L_FILE;105  END;106  ---------------------------------------------删除directory------------------------------------107  PROCEDURE DROP_DIRECTORY(P_DIRECTORY_NAME VARCHAR2) IS108  BEGIN109   EXECUTE IMMEDIATE 'drop directory ' || P_DIRECTORY_NAME;110  EXCEPTION111   WHEN OTHERS THEN112    NULL;113  END;114  --------------------------------------------------创建directory-----------------------------------------115  PROCEDURE CREATE_DIRECTORY(P_DIRECTORY_NAME VARCHAR2,116               P_DIR      VARCHAR2) IS117  BEGIN118   EXECUTE IMMEDIATE 'create directory ' || P_DIRECTORY_NAME || ' as ''' ||119            P_DIR || '''';120   EXECUTE IMMEDIATE 'grant read,write on directory ' || P_DIRECTORY_NAME ||121            ' to public';122   EXCEPTION123   WHEN OTHERS THEN124    RAISE;125  END;126  --------------------------------------------分割邮件地址或者附件地址-----------------------------------127  PROCEDURE P_SPLITE_STR(P_STR     VARCHAR2,128             P_SPLITE_FLAG INT DEFAULT 1) IS129   L_ADDR VARCHAR2(254) := '';130   L_LEN INT;131   L_STR VARCHAR2(4000);132   J   INT := 0; --表示邮件地址或者附件的个数133  BEGIN134   /*处理接收邮件地址列表,包括去空格、将;转换为,等*/135   L_STR := TRIM(RTRIM(REPLACE(REPLACE(P_STR, ';', ','), ' ', ''), ','));136   L_LEN := LENGTH(L_STR);137   FOR I IN 1 .. L_LEN LOOP138    IF SUBSTR(L_STR, I, 1) <> ',' THEN139     L_ADDR := L_ADDR || SUBSTR(L_STR, I, 1);140    ELSE141     J := J + 1;142     IF P_SPLITE_FLAG = 1 THEN --表示处理邮件地址143      --前后需要加上'<>',否则很多邮箱将不能发送邮件144      L_ADDR := '<' || L_ADDR || '>';145      --调用邮件发送过程146      MY_ADDRESS_LIST(J) := L_ADDR;147     ELSIF P_SPLITE_FLAG = 2 THEN --表示处理附件名称148      MY_ACCT_LIST(J) := L_ADDR;149     END IF;150     L_ADDR := '';151    END IF;152    IF I = L_LEN THEN153     J := J + 1;154     IF P_SPLITE_FLAG = 1 THEN155      --调用邮件发送过程156      L_ADDR := '<' || L_ADDR || '>';157      MY_ADDRESS_LIST(J) := L_ADDR;158     ELSIF P_SPLITE_FLAG = 2 THEN159      MY_ACCT_LIST(J) := L_ADDR;160     END IF;161    END IF;162   END LOOP;163  END;164  ------------------------------------------------写邮件头和邮件内容------------------------------------------165  PROCEDURE WRITE_DATA(P_CONN  IN OUT NOCOPY UTL_SMTP.CONNECTION,166            P_NAME  IN VARCHAR2,167            P_VALUE IN VARCHAR2,168            P_SPLITE VARCHAR2 DEFAULT ':',169            P_CRLF  VARCHAR2 DEFAULT L_CRLF) IS170  BEGIN171   /* utl_raw.cast_to_raw 对解决中文乱码问题很重要*/172   UTL_SMTP.WRITE_RAW_DATA(P_CONN, UTL_RAW.CAST_TO_RAW(CONVERT(P_NAME ||173                             P_SPLITE ||174                             P_VALUE ||175                             P_CRLF, 'ZHS16GBK')));176  END;177  ----------------------------------------写MIME邮件尾部-----------------------------------------------------178 179  PROCEDURE END_BOUNDARY(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,180             LAST IN BOOLEAN DEFAULT FALSE) IS181  BEGIN182   UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);183   IF (LAST) THEN184    UTL_SMTP.WRITE_DATA(CONN, LAST_BOUNDARY);185   END IF;186  END;187 188  ----------------------------------------------发送附件----------------------------------------------------189 190  PROCEDURE ATTACHMENT(CONN     IN OUT NOCOPY UTL_SMTP.CONNECTION,191            MIME_TYPE  IN VARCHAR2 DEFAULT 'text/plain',192            INLINE    IN BOOLEAN DEFAULT TRUE,193            FILENAME   IN VARCHAR2 DEFAULT 't.txt',194            TRANSFER_ENC IN VARCHAR2 DEFAULT '7 bit',195            DT_NAME   IN VARCHAR2 DEFAULT '0') IS196 197   L_FILENAME VARCHAR2(1000);198  BEGIN199   --写附件头200   UTL_SMTP.WRITE_DATA(CONN, FIRST_BOUNDARY);201   --设置附件格式202   WRITE_DATA(CONN, 'Content-Type', MIME_TYPE);203   --如果文件名称非空,表示有附件204   DROP_DIRECTORY(DT_NAME);205   --创建directory206   CREATE_DIRECTORY(DT_NAME, GET_FILE(FILENAME, 1));207   --得到附件文件名称208   L_FILENAME := GET_FILE(FILENAME, 2);209   IF (INLINE) THEN210    --WRITE_DATA(CONN, 'Content-Disposition', 'inline; filename="' ||211    WRITE_DATA(CONN, 'Content-Disposition', 'inline; filename="' ||212          L_FILENAME || '"');213   ELSE214    WRITE_DATA(CONN, 'Content-Disposition', 'attachment; filename="' ||215          L_FILENAME || '"');216   END IF;217 218   --设置附件的转换格式219   IF (TRANSFER_ENC IS NOT NULL) THEN220    WRITE_DATA(CONN, 'Content-Transfer-Encoding', TRANSFER_ENC);221   END IF;222 223   UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);224 225   --begin 贴附件内容226   IF TRANSFER_ENC = 'bit 7' THEN227    --如果是文本类型的附件228    BEGIN229     L_FILE_HANDLE := UTL_FILE.FOPEN(DT_NAME, L_FILENAME, 'r'); --打开文件230     --把附件分成多份,这样可以发送超过32K的附件231     LOOP232      UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);233      L_MESG := L_LINE || L_CRLF;234      WRITE_DATA(CONN, '', L_MESG, '', '');235     END LOOP;236     UTL_FILE.FCLOSE(L_FILE_HANDLE);237     END_BOUNDARY(CONN);238    EXCEPTION239     WHEN OTHERS THEN240      UTL_FILE.FCLOSE(L_FILE_HANDLE);241      END_BOUNDARY(CONN);242      NULL;243    END; --结束文本类型附件的处理244 245   ELSIF TRANSFER_ENC = 'base64' THEN246    --如果是二进制类型的附件247    BEGIN248     --把附件分成多份,这样可以发送超过32K的附件249     L_FILEPOS := 1;--重置offset,在发送多个附件时,必须重置250     L_FIL   := BFILENAME(DT_NAME, L_FILENAME);251     L_FILE_LEN := DBMS_LOB.GETLENGTH(L_FIL);252     L_MODULO  := MOD(L_FILE_LEN, L_AMT);253     L_PIECES  := TRUNC(L_FILE_LEN / L_AMT);254     IF (L_MODULO <> 0) THEN255      L_PIECES := L_PIECES + 1;256     END IF;257     DBMS_LOB.FILEOPEN(L_FIL, DBMS_LOB.FILE_READONLY);258     DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);259     L_DATA := NULL;260     FOR I IN 1 .. L_PIECES LOOP261      L_FILEPOS := I * L_AMT + 1;262      L_FILE_LEN := L_FILE_LEN - L_AMT;263      L_DATA   := UTL_RAW.CONCAT(L_DATA, L_BUF);264      L_CHUNKS  := TRUNC(UTL_RAW.LENGTH(L_DATA) / L_MAX_LINE_WIDTH);265      IF (I <> L_PIECES) THEN266       L_CHUNKS := L_CHUNKS - 1;267      END IF;268      UTL_SMTP.WRITE_RAW_DATA(CONN, UTL_ENCODE.BASE64_ENCODE(L_DATA));269      L_DATA := NULL;270      IF (L_FILE_LEN < L_AMT AND L_FILE_LEN > 0) THEN271       L_AMT := L_FILE_LEN;272      END IF;273      DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);274     END LOOP;275     DBMS_LOB.FILECLOSE(L_FIL);276     END_BOUNDARY(CONN);277    EXCEPTION278     WHEN OTHERS THEN279      DBMS_LOB.FILECLOSE(L_FIL);280      END_BOUNDARY(CONN);281      RAISE;282    END; --结束处理二进制附件283 284   END IF; --结束处理附件内容285   DROP_DIRECTORY(DT_NAME);286  END; --结束过程ATTACHMENT287 288  ---------------------------------------------真正发送邮件的过程--------------------------------------------289  PROCEDURE P_EMAIL(P_SENDORADDRESS2  VARCHAR2, --发送地址290           P_RECEIVERADDRESS2 VARCHAR2) --接受地址291  IS292   L_CONN UTL_SMTP.CONNECTION; --定义连接293  BEGIN294   /*初始化邮件服务器信息,连接邮件服务器*/295   L_CONN := UTL_SMTP.OPEN_CONNECTION(P_SERVER, P_PORT);296   UTL_SMTP.HELO(L_CONN, P_SERVER);297   /* smtp服务器登录校验 */298   IF P_NEED_SMTP = 1 THEN299    UTL_SMTP.COMMAND(L_CONN, 'AUTH LOGIN', '');300    UTL_SMTP.COMMAND(L_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USER))));301    UTL_SMTP.COMMAND(L_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_PASS))));302   END IF;303 304   /*设置发送地址和接收地址*/305   UTL_SMTP.MAIL(L_CONN, P_SENDORADDRESS2);306   UTL_SMTP.RCPT(L_CONN, P_RECEIVERADDRESS2);307 308   /*设置邮件头*/309   UTL_SMTP.OPEN_DATA(L_CONN);310 311   WRITE_DATA(L_CONN, 'Date', TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));312   /*设置发送人*/313   WRITE_DATA(L_CONN, 'From', P_SENDOR);314   /*设置接收人*/315   WRITE_DATA(L_CONN, 'To', P_RECEIVER);316   /*设置邮件主题*/317   WRITE_DATA(L_CONN, 'Subject', P_SUB);318 319   WRITE_DATA(L_CONN, 'Content-Type', MULTIPART_MIME_TYPE);320   UTL_SMTP.WRITE_DATA(L_CONN, UTL_TCP.CRLF);321   UTL_SMTP.WRITE_DATA(L_CONN, FIRST_BOUNDARY);322   WRITE_DATA(L_CONN, 'Content-Type', 'text/plain;charset=gb2312');323   --单独空一行,否则,正文内容不显示324   UTL_SMTP.WRITE_DATA(L_CONN, UTL_TCP.CRLF);325   /* 设置邮件正文326    把分隔符还原成chr(10)。这主要是为了shell中调用该过程,如果有多行,则先把多行的内容合并成一行,并用 l_splite分隔327    然后用 l_crlf替换chr(10)。这一步是必须的,否则将不能发送邮件正文有多行的邮件328   */329   WRITE_DATA(L_CONN, '', REPLACE(REPLACE(P_TXT, L_SPLITE, CHR(10)), CHR(10), L_CRLF), '', '');330   END_BOUNDARY(L_CONN);331 332  --如果文件名称不为空,则发送附件333   IF (P_FILENAME IS NOT NULL) THEN334    --根据逗号或者分号拆分附件地址335    P_SPLITE_STR(P_FILENAME, 2);336    --循环发送附件(在同一个邮件中)337    FOR K IN 1 .. MY_ACCT_LIST.COUNT LOOP338     ATTACHMENT(CONN => L_CONN, FILENAME => MY_ACCT_LIST(K), TRANSFER_ENC => P_ENCODE, DT_NAME => L_DIRECTORY_BASE_NAME ||339                 TO_CHAR(K));340    END LOOP;341   END IF;342 343   /*关闭数据写入*/344   UTL_SMTP.CLOSE_DATA(L_CONN);345   /*关闭连接*/346   UTL_SMTP.QUIT(L_CONN);347 348   /*异常处理*/349  EXCEPTION350   WHEN OTHERS THEN351    NULL;352    RAISE;353 354  END;355 356  ---------------------------------------------------主过程-----------------------------------------------------357 358 BEGIN359  L_SENDORADDRESS := '<' || P_SENDOR || '>';360  P_SPLITE_STR(P_RECEIVER);--处理邮件地址361  FOR K IN 1 .. MY_ADDRESS_LIST.COUNT LOOP362   P_EMAIL(L_SENDORADDRESS, MY_ADDRESS_LIST(K));363  END LOOP;364  /*处理邮件地址,根据逗号分割邮件*/365 366 EXCEPTION367  WHEN OTHERS THEN368   RAISE;369 END;

注:需要相应的用户有相应的ACL权限,否则会报错