存储过程-d

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;