郵件群發反屏蔽的原理是這樣的:
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;
存儲過程,美麗優雅的波浪,呵呵....