动态SQLEXECUTE IMMEDIATE USING及物化实物图用法

最新推荐文章于 2025-02-19 16:40:16 发布

Snowballl

最新推荐文章于 2025-02-19 16:40:16 发布

阅读量722

点赞数

CC 4.0 BY-SA版权

分类专栏：程序

本文链接：https://blog.youkuaiyun.com/minwang593/article/details/13167239

程序专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了如何通过PL/SQL创建GPRS-GGSN小时粒度DM表的物化视图，包括视图定义、建立过程以及相关SQL操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

      v_sql := 'DROP MATERIALIZED VIEW mv_fds_GPRS_GGSN_hour_dm';
      EXECUTE IMMEDIATE 'INSERT INTO log_gprs_1 (context,content,sql,time) VALUES (:1,:2,:3,sysdate)'
        USING 7,'DROP物化视图mv_fds_gprs_ggsn_hour_dm',v_sql;
      COMMIT;
      EXECUTE IMMEDIATE v_sql;
      COMMIT;
    END IF;
    /*
    SELECT COUNT(*) INTO cnt FROM user_mviews WHERE mview_name = 'MV_FDS_GPRS_GGSN_DAY_DM';
    IF cnt > 0 THEN
      v_sql := 'DROP MATERIALIZED VIEW mv_fds_gprs_ggsn_day_dm';
      EXECUTE IMMEDIATE 'INSERT INTO log_gprs_1 (context,content,sql,time) VALUES (:1,:2,:3,sysdate)'
        USING 8,'DROP物化视图mv_fds_gprs_ggsn_day_dm',v_sql;
      COMMIT;
      EXECUTE IMMEDIATE v_sql;
      COMMIT;
    END IF;
*/
    /*   2.拼装视图定义、建立视图 */
    -- 小时粒度 DM表物化视图
    v_sql := 'CREATE MATERIALIZED VIEW mv_fds_gprs_ggsn_hour_dm
      COMPRESS
      NOLOGGING
      BUILD DEFERRED
      REFRESH COMPLETE
      --START WITH trunc(SYSDATE,''DD'')+1/24 NEXT trunc(SYSDATE+1,''DD'') + 1/24
      AS
      SELECT NULL AS timeid,NULL AS statslevel,NULL AS CG,NULL AS ggsnaddr,NULL as file_name,NULL AS apn,NULL AS uplink_traffic ,NULL AS downlink_traffic,NULL AS duration,NULL AS RECORD_CNT FROM dual where 1 = 2';

-- FOR i IN 1 .. daynum LOOP
v_sql := v_sql || '

      UNION ALL
       --小时粒度
       select /*+ PARALLEL(fact_gprs_ggsn_cdr 16) */ a.timeid,a.statslevel,a.cg,b.equi_name,a.file_name,a.apn,a.datavolumegprsuplink,a.datavolumegprsdownlink,a.duration,a.RECORD_CNT from
      (SELECT TRUNC(timeid / 100) * 100 AS timeid,60 statslevel,SYSTEM_ID AS CG,ggsnaddress,file_name,UPPER(accesspointnameni) AS APN,sum(datavolumegprsuplink) as datavolumegprsuplink,sum(datavolumegprsdownlink) as datavolumegprsdownlink ,
              sum(duration) as duration,COUNT(*) AS RECORD_CNT
      FROM fact_gprs_ggsn_cdr partition (PART_' || to_char(SYSDATE - daynum,'yyyymmdd') || ')
      GROUP BY TRUNC(timeid / 100) * 100,60,SYSTEM_ID,ggsnaddress,file_name,UPPER(accesspointnameni)
      ) a,
      cfg_gprs_ip b
      where a.ggsnaddress=b.equi_ip(+)';



--END LOOP;

    EXECUTE IMMEDIATE 'INSERT INTO log_gprs_1 (context,content,sql,time) VALUES (:1,:2,:3,sysdate)'
      USING 9,'CREATE物化视图mv_fds_gprs_ggsn_hour_dm',v_sql;
    COMMIT;
    EXECUTE IMMEDIATE v_sql;
    COMMIT;