ORACLE 存储过程 邮件服务器 反屏蔽

邮件群发反屏蔽的原理是这样的:
    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;

    存储过程,美丽优雅的波浪,呵呵....