清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

CREATE OR REPLACE PROCEDURE sp_trafficdata_to_realvflow is
  v_lst_time DATE;
  v_lst_max  DATE;
  v_tot_time DATE;
  v_tot_max  VARCHAR2(32);
BEGIN
  SELECT MAX(RECORD_TIME) INTO v_lst_max FROM DM_VFLW_LST;
  IF v_lst_max IS NULL THEN
    RETURN;
  END IF;

  SELECT NVL(MAX(F_TIME), '2000-01-01 00:00:00')
    INTO v_tot_max
    FROM DM_REAL_VFLOW;

  v_lst_time := TRUNC(v_lst_max - 1 / 24 / 60, 'MI');
  v_tot_time := TRUNC(TO_DATE(v_tot_max, 'YYYY-MM-DD HH24:MI:SS') +
                      1 / 24 / 60,
                      'MI');
  IF v_lst_time < v_tot_time THEN
    RETURN;
  END IF;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '小型车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '中型车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '大型车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '特大型车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '中小客车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '大客车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '小型货车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '中型货车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '大型货车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '特大型货车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  INSERT INTO DM_REAL_VFLOW
    (F_MTID,
     F_EQUIPNUM,
     F_MSE_ID,
     F_VEHICLETYPE,
     F_TIME,
     F_DIRECTIONNUM,
     F_VEHICLENUM,
     F_AVERAGESPEED)
    SELECT V.F_MTID,
           V.F_EQUIPNUM,
           V.F_MSE_ID,
           (SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '集装箱车'),
           TO_CHAR(V.RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS'),
           V.DIRECTION,
           V.SMALL_VEHICLE,
           V.SMALL_VEHICLE_SPEED
      FROM DM_VFLW_LST V
     WHERE TRUNC(V.RECORD_TIME, 'MI') >= v_tot_time
       AND TRUNC(V.RECORD_TIME, 'MI') <= v_lst_time;

  COMMIT;
END sp_trafficdata_to_realvflow;