存储过程-c

create or replace Procedure Pro_Drivemail_Open
Is
  V_SQL  VARCHAR2(5000);
  --CNT    NUMBER;

BEGIN
  --SELECT COUNT(*) INTO CNT FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND LENGTH(SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1))>=6;

     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 EDM_SYSTEM_SEND_TMP NOLOGGING
                (ID,EMAIL,OPEN_TIME,SENDING_TIME,OPEN,CLICK)
                SELECT ID,EMAIL,OPEN_TIME,SENDING_TIME,OPEN,CLICK
                  FROM '||C.TNAME||' WHERE ACTIVE = 1 AND SENDING_TIME IS NOT NULL';
         --dbms_output.put_line(v_sql);
         EXECUTE IMMEDIATE V_SQL;
         COMMIT;
     END LOOP;
--------------------------------------------------------------------

     INSERT /*+ APPEND */ INTO EDM_SYSTEM_SEND NOLOGGING
     SELECT A.*,
            ROW_NUMBER() OVER(PARTITION BY EMAIL ORDER BY NVL(OPEN_TIME,SYSDATE-3650) DESC) AS RK
       FROM EDM_SYSTEM_SEND_TMP A;
     COMMIT;
--------------------------------------------------------------------

     INSERT /*+ APPEND */ INTO EDM_SYSTEM_ACTIVE NOLOGGING
            (EMAIL,SENDDATE_REC,OPENDATE_REC,SENDTIMES_TOT,OPENTIMES_TOT,SENDTIMES_MON,OPENTIMES_MON)
     SELECT A.EMAIL,A.SENDING_TIME,A.OPEN_TIME,B.SENDTIMES_TOT,B.OPENTIMES_TOT,C.SENDTIMES_MON,C.OPENTIMES_MON
       FROM EDM_SYSTEM_SEND A,   ---ALL Mails Table
            (SELECT EMAIL,
                    COUNT(*) SENDTIMES_TOT,
                    SUM(CASE WHEN OPEN <> 0 THEN 1 ELSE 0 END) OPENTIMES_TOT
               FROM EDM_SYSTEM_SEND
              GROUP BY EMAIL ) B,    ---Total Times Table
             (SELECT EMAIL,
                    COUNT(*) SENDTIMES_MON,
                    SUM(CASE WHEN OPEN <> 0 THEN 1 ELSE 0 END) OPENTIMES_MON
               FROM EDM_SYSTEM_SEND
              WHERE (SENDING_TIME-SYSDATE)<=60
              GROUP BY EMAIL ) C     ---Month Times Table
      WHERE A.RK = 1
        AND A.EMAIL = B.EMAIL(+)
        AND A.EMAIL = C.EMAIL(+);
      Commit;
      DELETE EDM_SYSTEM_SEND_TMP NOLOGGING;
      COMMIT;
End Pro_Drivemail_Open;