golang.png
go+sqlite3，目前有缺陷，以后找时间重写。
/*
* @Author: lzx
* @Description: send mail
* @File: main.go
* @Version: 1.0.0
* @Date: 2020/3/24 21:44
*/
package main
import (
"strings"
"database/sql"
"fmt"
"log"
"mime"
"path/filepath"
"gopkg.in/gomail.v2"
_ "github.com/mattn/go-sqlite3"
)
var flag string
func main() {
var rowid int
var recipient string
var cc string
var subject string
var message string
var html string
var attchment_path string
fmt.Println("打开数据")
db, err := sql.Open("sqlite3", "./RCS.db")
checkErr(err)
fmt.Println("生成数据表")
sql := `select rowid,recipient,ifnull(cc,''),subject,ifnull(message,''),ifnull(html,''),
ifnull(attachment_path,'') from send_mail
WHERE statusflag='N'`
fmt.Println("准备执行SQL")
rows, err := db.Query(sql)
checkErr(err)
fmt.Println("执行完毕")
defer rows.Close()
for rows.Next() {
err = rows.Scan(&rowid,&recipient, &cc, &subject, &message, &html, &attchment_path)
checkErr(err)
fmt.Println(rowid,recipient, cc, subject, message, html, attchment_path)
recipient_arr:=strings.Split(recipient,`;`)
cc_arr:=strings.Split(cc,`;`)
flag, err := SendMail(recipient_arr, cc_arr, subject, message, html, attchment_path)
fmt.Println(flag,err,rowid)
if err != nil {
log.Print(err)
fmt.Printf("Send fail!")
return
}
fmt.Printf("send successfully!")
}
vsql :=`delete from send_mail where rowid <11`
result,err:=db.Exec(vsql)
fmt.Println(vsql)
if err !=nil{
log.Println("exec failed:", err, ", sql:", vsql)
}
idAff, err := result.RowsAffected()
if err != nil {
log.Println("RowsAffected failed:", err)
return
}
log.Println("id:", idAff)
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
func SendMail(recipient []string, cc []string, subject string, message string, html string, attchment_path string) (string, error) {
m := gomail.NewMessage()
m.SetHeader("From", "AsiaRCS@cyberway.net.cn")
m.SetHeader("To", recipient...)
if cc[0] !=""{
m.SetHeader("Cc", cc...)
}
m.SetHeader("Subject", subject)
if message != "" {
m.SetBody("text/html", message)
}
if html != "" {
m.SetBody("text/html", html)
}
if attchment_path != "" {
filename := filepath.Base(attchment_path)
m.Attach(attchment_path,
gomail.SetHeader(map[string][]string{
"Content-Disposition": []string{
fmt.Sprintf(`attachment;filename="%s"`, mime.BEncoding.Encode("UTF-8", filename)),
},
}))
}
d := gomail.NewDialer("smtp.exmail.qq.com", 25, "AsiaRCS@cyberway.net.cn", "RCS1qaz*2wsx")
err := d.DialAndSend(m)
flag := "Y"
if err != nil {
panic(err)
flag := "F"
return flag, err
}
return flag, err
}
PLSQL.png
/*=========================================
procedure name:rcs_send_email
Author:lzx
Create date:2019/10/7 17:46:23
Modify record:
-------------------------
2019/10/13 增加带附件发送功能
2019/11/19 存储过程sub_splite_str增加初始化my_address_list数组，避免产生邮件接收人重复增加的问题
2019/12/07 邮件标题和MIME内容增加Base64的转码
-------------------------
作用：用oracle发送邮件
主要功能：1、支持多收件人。
2、支持中文
3、支持抄送人
4、支持大于32K的附件
5、支持多行正文
6、支持多附件
7、支持HTML邮件
===========================================*/
-------------------包头---------------------------------
procedure rcs_send_email(p_recipient VARCHAR2, -- 邮件接收人，多个接收人用;号分隔
p_CC VARCHAR2, --抄送人，多个抄送人用;号分隔
p_subject VARCHAR2, -- 邮件标题
p_message clob default null, -- 邮件正文
p_html clob default null, --html邮件
p_attachment_path varchar2, --附件地址（绝对路径）,多附件用逗号或分号分隔
o_statusflag out char, --发送结果 Y-成功,F-失败
o_errtext out varchar2); --发送失败原因
---------------------------------------包体---------------------------------
PROCEDURE rcs_send_email(p_recipient VARCHAR2, -- 邮件接收人
p_CC VARCHAR2, --抄送人
p_subject VARCHAR2, -- 邮件标题
p_message clob default null, -- 纯文本邮件正文
p_html clob default null, --html邮件
p_attachment_path varchar2, --附件地址（绝对路径）,多附件用逗号或分号分隔
o_statusflag out char, --发送结果 Y-成功,F-失败
o_errtext out varchar2) IS
-- 下面四个变量请根据实际邮件服务器进行赋值
v_mailhost VARCHAR2(30) := 'smtp.exmail.qq.com'; -- SMTP服务器地址
v_user VARCHAR2(30) := 'user'; -- 登录SMTP服务器的用户名
v_pass VARCHAR2(50) := 'password'; -- 登录SMTP服务器的密码
v_sender VARCHAR2(50) := 'user'; -- 发送者邮箱，一般与 v_user 一样
---------------- 配置完成 -----------------
v_conn UTL_SMTP.connection; -- 到邮件服务器的连接
v_msg VARCHAR2(4000); -- 邮件内容
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;
v_receivers varchar2(32767);
v_CC varchar2(32767);
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1'; --邮件段体内的每个子段以此串定界
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_filepos pls_integer := 1;
l_fil bfile;
l_file_len number;
l_modulo number;
l_pieces number;
l_data raw(32767);
l_amt number := 8580;
l_buf raw(32767);
l_amount number;
--分割邮件地址或者附件地址
procedure sub_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; --表示邮件地址或者附件的个数
begin
my_address_list.delete; --初始化数组
/*处理接收邮件地址列表，将;转换为,等*/
l_str := trim(rtrim(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
--表示处理邮件地址
--前后需要加上'<>'，否则很多邮箱将不能发送邮件
l_addr := '<' || l_addr || '>';
--调用邮件发送过程
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
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;
--返回附件源文件所在目录或者名称
function sub_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
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
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;
procedure sub_attachment(filename in varchar2, dt_name in varchar2) is
--dt_name目录名称
l_filename varchar2(1000);
-- l_amount number;
begin
--得到附件文件名称
l_filename := sub_get_file(filename, 2);
v_msg := v_msg || UTL_TCP.CRLF || '--' || l_boundary ||
UTL_TCP.CRLF;
v_msg := v_msg || 'Content-Type: application/octet-stream;name="' ||
l_filename || '"' || UTL_TCP.CRLF ||
'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF ||
'Content-Disposition: attachment;filename="' ||
l_filename || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(v_msg), l_offset, v_msg);
--dbms_output.put_line(l_body_html);
--把附件分成多份，这样可以发送超过32k的附件
l_filepos := 1; --重置offset，在发送多个附件时，必须重置
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);
l_data := null;
l_amount := l_amt;
for i in 1 .. l_pieces loop
dbms_lob.read(l_fil, l_amount, l_filepos, l_buf);
l_filepos := i * l_amount + 1;
l_file_len := l_file_len - l_amount;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,
length(utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_buf))),
l_offset,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_buf)));
if i = l_pieces then
l_amount := l_file_len;
end if;
end loop;
dbms_lob.fileclose(l_fil);
/*exception
when others then
dbms_lob.fileclose(l_fil);
sub_end_boundary(conn);
raise;
end; --结束处理二进制附件*/
end; --结束过程attachment
BEGIN
--连接邮件服务器
v_conn := UTL_SMTP.open_connection(host => v_mailhost,
port => '25',
secure_connection_before_smtp => FALSE);
--向邮件服务器打招呼
UTL_SMTP.ehlo(v_conn, v_mailhost);
UTL_SMTP.command(v_conn, 'AUTH LOGIN'); -- smtp服务器登录校验
UTL_SMTP.command(v_conn,
UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
UTL_SMTP.command(v_conn,
UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));
UTL_SMTP.mail(v_conn, '<' || v_sender || '>'); --设置发件人
sub_splite_str(p_recipient); --处理邮件地址
for k in 1 .. my_address_list.count loop
v_receivers := v_receivers || my_address_list(k) || ';';
utl_smtp.rcpt(v_conn, my_address_list(k)); --设置收件人
end loop;
-- 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行
v_msg := v_msg || 'Date:' || TO_CHAR(SYSDATE, 'yyyy mm dd hh24:mi:ss') ||
UTL_TCP.CRLF;
v_msg := v_msg || 'From: ' || v_sender || '' || UTL_TCP.CRLF;
v_msg := v_msg || 'To: ' || v_receivers || '' || UTL_TCP.CRLF;
IF p_CC is not null then
sub_splite_str(p_CC); --处理邮件地址
for k in 1 .. my_address_list.count loop
v_CC := v_CC || my_address_list(k) || ';';
utl_smtp.rcpt(v_conn, my_address_list(k));
end loop;
v_msg := v_msg || 'Cc: ' || v_CC || UTL_TCP.CRLF; --抄送
END IF;
--v_msg := v_msg || 'Bcc: ' || p_recipient || UTL_TCP.CRLF;--密送
v_msg := v_msg || 'Subject: ' || '=?GB2312?B?' ||
replace(replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_subject))),
chr(10),
chr(13)),
chr(13),
'') || '?=' || UTL_TCP.CRLF; --邮件标题
v_msg := v_msg || 'Mime-Version: 1.0' || UTL_TCP.CRLF;
v_msg := v_msg || 'Content-Type: multipart/mixed;boundary=' || chr(34) ||
l_boundary || chr(34) || UTL_TCP.CRLF; -- 这前面是报头信息
--dbms_output.put_line(v_msg);
-- Write the headers 写入报头内容
dbms_lob.createtemporary(l_body_html, false, 10); --建立临时LOB
dbms_lob.write(l_body_html, length(v_msg), 1, v_msg); --把邮件内容写入临时LOB
IF p_message IS NOT NULL THEN
-- Write the text boundary,邮件分段(纯文本)
l_offset := dbms_lob.getlength(l_body_html) + 1;
v_msg := UTL_TCP.CRLF || '--' || l_boundary || UTL_TCP.CRLF;
v_msg := v_msg || 'content-type: text/plain; charset="GB2312"' ||
UTL_TCP.CRLF;
v_msg := v_msg || 'Content-Transfer-Encoding: base64' ||
UTL_TCP.CRLF || UTL_TCP.CRLF;
dbms_lob.write(l_body_html, length(v_msg), l_offset, v_msg);
--Write the plain text portion of the email，写入纯文本邮件内容
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,
length(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message)))),
l_offset,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message))));
END IF;
IF p_html IS NOT NULL THEN
-- Write the HTML boundary,邮件分段(html)
v_msg := UTL_TCP.CRLF || UTL_TCP.CRLF || '--' || l_boundary ||
UTL_TCP.CRLF;
v_msg := v_msg || 'Content-type: text/html; charset="GB2312"' ||
UTL_TCP.CRLF;
v_msg := v_msg || 'Content-Transfer-Encoding: base64' ||
UTL_TCP.CRLF || UTL_TCP.CRLF;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(v_msg), l_offset, v_msg);
-- Write the HTML portion of the message,写入html邮件内容
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,
length(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_html)))),
l_offset,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_html))));
END IF;
-- v_msg := v_msg || p_message || UTL_TCP.CRLF; -- 这个是邮件正文
-- v_msg := v_msg || 'X-Mailer: Foxmail 7, 1, 3, 48[cn]' || UTL_TCP.CRLF; --发送客户端
v_msg := null;
--添加附件
IF p_attachment_path is not null then
sub_splite_str(p_attachment_path, 2); --获取附件名称列表
for k in 1 .. my_acct_list.count loop
sub_attachment(filename => my_acct_list(k), dt_name => 'RCSDIR'); --把附件内容写入MIME
end loop;
END IF;
-- Write the final html boundary
v_msg := UTL_TCP.CRLF || UTL_TCP.CRLF || '--' || l_boundary || '--';
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(v_msg), l_offset, v_msg);
-------------------------------------------------------------------------------------------
UTL_SMTP.open_data(v_conn); --打开流
-- UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(l_body_html)); --这样写标题和内容都能用中文
l_offset := 1;
l_amount := 1900;
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_raw_data(v_conn,
utl_raw.cast_to_raw(dbms_lob.substr(l_body_html,
l_amount,
l_offset)));
l_offset := l_offset + l_amount;
l_amount := least(1900, dbms_lob.getlength(l_body_html) - l_amount);
end loop;
UTL_SMTP.close_data(v_conn); --关闭流
UTL_SMTP.quit(v_conn); --关闭连接
dbms_lob.freetemporary(l_body_html); --释放内存
o_statusflag := 'Y';
EXCEPTION
WHEN OTHERS THEN
o_statusflag := 'F';
o_errtext := DBMS_UTILITY.format_error_stack;
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
END rcs_send_email;
网友评论