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

  
      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;