存储过程-c

最新推荐文章于 2022-02-25 18:19:43 发布

最新推荐文章于 2022-02-25 18:19:43 发布 · 177 阅读

文章标签：

#C #C++ #C# #SQL

DataBase 专栏收录该内容

43 篇文章

订阅专栏

该过程将多个邮件活动表中的数据汇总到几个核心表中，通过PL/SQL实现数据的抽取、转换和加载。主要步骤包括：从符合特定条件的源表中提取数据并插入临时表；接着将临时表的数据按邮件地址进行汇总并插入主表；最后，计算每个用户的邮件发送与打开次数，并更新活跃度表。

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;