梦幻之旅

DEBUG - 天道酬勤

   :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  671 随笔 :: 6 文章 :: 256 评论 :: 0 Trackbacks
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;


posted on 2008-05-11 23:53 HUIKK 阅读(94) 评论(0)  编辑  收藏 所属分类: DataBase

只有注册用户登录后才能发表评论。


网站导航: