邮件反弹处理-优快云博客

本文介绍了一个PL/SQL过程，用于处理邮件系统的反弹记录。该过程首先清空临时表，然后从多个源表中按条件提取数据并插入到临时表中，最后将符合条件的数据更新到正式表中，确保数据的唯一性。

create or replace PROCEDURE Pro_Drivemail_rebound
Is
V_SQL VARCHAR2(5000);
--CNT    NUMBER;

Begin

     EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_REBOUND_TMP';
     COMMIT;
------------------------------------------------------------------------------------------
     FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND LENGTH(SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1))>=6) LOOP
        V_SQL:='INSERT /*+ APPEND */ INTO DRIVEMAIL_REBOUND_TMP NOLOGGING
                (ID,EMAIL,SENDING_TIME)
                SELECT ID,EMAIL,SENDING_TIME
                  FROM '||C.TNAME||' WHERE ACTIVE<-50 AND ACTIVE>-500';
         --dbms_output.put_line(v_sql);
         EXECUTE IMMEDIATE V_SQL;
         COMMIT;
     END LOOP;
------------------------------------------------------------------------------------------
     INSERT /*+ APPEND */ INTO DRIVEMAIL_REBOUND Nologging
     SELECT *
       FROM DRIVEMAIL_REBOUND_TMP A
      WHERE NOT EXISTS (SELECT 1 FROM DRIVEMAIL_REBOUND B WHERE A.EMAIL = B.EMAIL);
     COMMIT;
------------------------------------------------------------------------------------------
     EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_REBOUND_TMP';
     COMMIT;

End Pro_Drivemail_rebound;