邮件群发反屏蔽的原理是这样的:
1.每封邮件内容不同(针对反垃圾邮件的HASH技术)
2.每封邮件主题不同(针对反垃圾邮件的HASH技术\关键词过滤技术)
3.邮件发件人不同(针对反垃圾邮件的HASH技术)
4.发送邮件的ip不同(针对反垃圾邮件的黑白名单技术\反向查询技术)
5.单位时间内向某SMTP发送的数量不能超过经验值(针对反垃圾邮件的黑白名单技术)
就本次项目,偶写了如下存过程:
包头:
CREATE OR REPLACE
PACKAGE "EDM_PACK" AS
PROCEDURE FETCH_SENDER(
i_current_time IN NUMBER,
i_other_smtp IN VARCHAR2,
i_unknown_smtp IN VARCHAR2,
o_email_id OUT NOCOPY NUMBER,
o_email OUT NOCOPY VARCHAR2,
o_password OUT NOCOPY VARCHAR2,
o_smtp OUT NOCOPY VARCHAR2);
END;
包体:
CREATE OR REPLACE
PACKAGE BODY "EDM_PACK" AS
PROCEDURE FETCH_SENDER(
i_current_time IN NUMBER,
i_other_smtp IN VARCHAR2,
i_unknown_smtp IN VARCHAR2,
o_email_id OUT NOCOPY NUMBER,
o_email OUT NOCOPY VARCHAR2,
o_password OUT NOCOPY VARCHAR2,
o_smtp OUT NOCOPY VARCHAR2)
IS
temp_count_other_smtp NUMBER DEFAULT 0;
temp_anti_shield_id NUMBER DEFAULT NULL;
temp_current_day DATE DEFAULT NULL;
BEGIN
SELECT COUNT(DISTINCT other_smtp)
INTO temp_count_other_smtp
FROM anti_shields
WHERE other_smtp = i_other_smtp;
--查看i_other_smtp是否在反屏蔽的smtp之内
IF temp_count_other_smtp = 0
THEN
--此other_smtp未列入返屏蔽之内
--从anti_shields中随机取出一条符合指定other_smtp,
--且sending_time大于最小间隔的记录的id
UPDATE anti_shields
SET sending_time = i_current_time
WHERE id IN (
SELECT * FROM
(SELECT id
FROM anti_shields
WHERE i_current_time -
NVL(sending_time,i_current_time) >
86400000/NVL(count_per_day,500)
AND other_smtp = i_unknown_smtp
ORDER BY DBMS_RANDOM.Value
)
WHERE ROWNUM <=1)
RETURNING id INTO temp_anti_shield_id;
ELSE
--此other_smtp已列入返屏蔽之内
--从anti_shields中随机取出一条符合指定other_smtp,
--且sending_time大于最小间隔的记录的id
UPDATE anti_shields
SET sending_time = i_current_time
WHERE id IN (
SELECT * FROM
(SELECT id
FROM anti_shields
WHERE i_current_time -
NVL(sending_time,i_current_time) >
86400000/NVL(count_per_day,500)
AND other_smtp = i_other_smtp
ORDER BY DBMS_RANDOM.Value
)
WHERE ROWNUM <=1)
RETURNING id INTO temp_anti_shield_id;
END IF;
--从anti_shields中随机取出一条符合指定other_smtp,
--且sending_time大于最小间隔的记录的id
UPDATE anti_shields
SET sending_time = i_current_time
WHERE id IN (
SELECT * FROM
(SELECT id
FROM anti_shields
WHERE i_current_time -
NVL(sending_time,i_current_time) >
86400000/NVL(count_per_day,500)
AND other_smtp = i_other_smtp
ORDER BY DBMS_RANDOM.Value
)
WHERE ROWNUM <=1)
RETURNING id INTO temp_anti_shield_id;
--如果得到了记录
IF temp_anti_shield_id IS NOT NULL
THEN
--得到指定id的记录的current_day并存入temp_current_day中
SELECT current_day
INTO temp_current_day
FROM anti_shields
WHERE id = temp_anti_shield_id;
--如果得到了当前时间
IF temp_current_day IS NOT NULL
THEN
--看是否更新current_day和count_day
IF TO_CHAR(SYSDATE,'YYMMDD') !=
TO_CHAR(temp_current_day,'YYMMDD')
THEN
--更新current_day和count_day
UPDATE anti_shields
SET current_day = SYSDATE,count_day = 0
WHERE id = temp_anti_shield_id;
END IF;
END IF;
--得到我们自己SMTP服务器的ip
SELECT ourself_smtp
INTO o_smtp
FROM anti_shields
WHERE id = temp_anti_shield_id;
--如果ourself_smtp不为NULL
IF o_smtp IS NOT NULL
THEN
--随机从自已的SMTP服务器上取得一帐号
SELECT id
INTO o_email_id
FROM
(SELECT id
FROM senders
WHERE ourself_smtp = o_smtp
ORDER BY DBMS_RANDOM.Value
)
WHERE ROWNUM <=1;
--如果得到帐号
IF o_email_id IS NOT NULL
THEN
--得到email帐号
SELECT email
INTO o_email
FROM senders
WHERE id = o_email_id;
--得到email密码
SELECT password
INTO o_password
FROM senders
WHERE id = o_email_id;
END IF;
--判断是否更改日发送量和发送总量
IF o_email_id IS NOT NULL
AND o_email IS NOT NULL
AND o_password IS NOT NULL
AND o_smtp IS NOT NULL
THEN
--更改日发送量和发送总量
UPDATE anti_shields
SET count_day = count_day + 1,
total_count = total_count + 1
WHERE id = temp_anti_shield_id;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
o_email_id := NULL;
o_email := NULL;
o_password := NULL;
o_smtp := NULL;
END;
END;
存储过程,美丽优雅的波浪,呵呵....