存储过程代码：

CREATE OR REPLACE PROCEDURE dm_rpt.rpt_consultation_community (tt1 in  timestamp)
/*
 * 作者：sunsong
 * 时间：2020-04-02 
 * 数据来源：ods_evonesns_orc.sgcc_merchant_news  商户_资讯表 
 *             ods_evonesns_orc.sgcc_efriend_post      e友板块_帖子表 
 * 目标表：dm_rpt.rpt_consultation_community 咨询社区
 * 功能：往表中插入昨日统计数据
 * 传入参数tt1参数格式：2020-04-01，如果为空，则取昨日的日期
 */
IS
    o_return INT
    o_msg STRING
    
BEGIN
    DELETE FROM dm_rpt.rpt_consultation_community WHERE stas_day = tt1;
    insert into dm_rpt.rpt_consultation_community
    (
      stas_day                        -- 统计日期
    , consultation_accum_pv         -- 咨询累计pv
    , consultation_accum_uv         -- 咨询累计uv
    , consultation_day_pv           -- 咨询昨日新增pv
    , consultation_day_active_pv    -- 咨询昨日新增活跃用户数
    , community_accum_pv            -- 社区累计pv
    , community_accum_uv            -- 社区累计uv
    , community_day_pv              -- 社区昨日新增pv
    , community_day_active_pv       -- 社区昨日新增活跃用户数
    , etl_time                      -- etl_time
    
    
    )
    select tt1 as stas_day
      ,sum(consultation_accum_pv)
      ,sum(consultation_accum_uv)
      ,sum(consultation_day_pv)
      ,sum(consultation_day_active_pv)
      ,sum(community_accum_pv)
      ,sum(community_accum_uv)
      ,sum(community_day_pv)
      ,sum(community_day_active_pv)
      ,current_timestamp() as create_time
      from (
    -- 咨询累计pv
        SELECT  
       SUM(read_num)  as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , 0 as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv
     , 0 as community_day_pv
     , 0 as community_day_active_pv
    FROM
    ods_evonesns_orc.`sgcc_merchant_news`
      
    union all
    -- 咨询累计uv 没有uv数据库，友盟也不提供uv数据
    SELECT 
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , 0 as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv 
     , 0 as community_day_pv
     , 0 as community_day_active_pv

    FROM
    ods_evonesns_orc.`sgcc_merchant_news` limit 1
    union all
    -- 咨询昨日新增pv
    SELECT  
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , SUM(read_num) as consultation_day_pv
     , 0 as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv
     , 0 as community_day_pv
     , 0 as community_day_active_pv     FROM  ods_evonesns_orc.`sgcc_merchant_news` t1
    WHERE 
        t1.gmt_created  >=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  00:00:00')
    AND    t1.gmt_created  <=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  23:59:59')
    
    union all
    -- 咨询昨日新增活跃用户数#######
    SELECT  
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , SUM(real_read_num) as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv
     , 0 as community_day_pv
     , 0 as community_day_active_pv     FROM  ods_evonesns_orc.`sgcc_merchant_news` t2
    WHERE 
        t2.gmt_created  >=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  00:00:00')
    AND    t2.gmt_created  <=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  23:59:59')    
    union all
    -- 社区累计pv
    SELECT  
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , 0 as consultation_day_active_pv
     , SUM(read_num) as community_accum_pv
     , 0 as community_accum_uv
     , 0 as community_day_pv
     , 0 as community_day_active_pv

    FROM
    ods_evonesns_orc.`sgcc_efriend_post`
    union all
    -- 社区累计uv (没有uv数据库，友盟也不提供uv数据，无法查询到此结果)
    SELECT 
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , 0 as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv 
     , 0 as community_day_pv
     , 0 as community_day_active_pv

    FROM
    ods_evonesns_orc.`sgcc_efriend_post` limit 1
    union all
    -- 社区昨日新增pv
    SELECT  
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , 0 as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv
     , SUM(read_num) as community_day_pv
     , 0 as community_day_active_pv

    FROM
    ods_evonesns_orc.`sgcc_efriend_post` t3
    WHERE 
        t3.gmt_created  >=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  00:00:00')
    AND    t3.gmt_created  <=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  23:59:59')
    union all
    -- 社区昨日新增活跃用户数##########(无法查询到此结果)
    SELECT  
       0 as consultation_accum_pv
     , 0 as consultation_accum_uv
     , 0 as consultation_day_pv
     , 0 as consultation_day_active_pv
     , 0 as community_accum_pv
     , 0 as community_accum_uv
     , 0 as community_day_pv
     , SUM(real_read_num) as community_day_active_pv

    FROM
    ods_evonesns_orc.`sgcc_efriend_post` t4
    WHERE 
        t4.gmt_created  >=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  00:00:00')
    AND    t4.gmt_created  <=  CONCAT(DATE_SUB(  tt1 , 0  ),  '  23:59:59')
   ) t
    ;
     COMMIT;
     o_return := 0;
     o_msg := 'rpt_consultation_community 执行成功';
     DBMS_OUTPUT.PUT_LINE('o_return:' || o_return);
     DBMS_OUTPUT.PUT_LINE('o_msg:' || o_msg);
EXCEPTION
     WHEN OTHERS THEN
         ROLLBACK;
         o_return := -1;
         o_msg := 'OraError=' || SQLCODE || ',' || SQLERRM(SQLCODE);
         DBMS_OUTPUT.PUT_LINE('o_return:' || o_return);
         DBMS_OUTPUT.PUT_LINE('o_msg:' || o_msg);
END;

后面定时调用存储过程即可。