快速查找

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

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,(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '中型车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '大型车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '特大型车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '中小客车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '大客车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '小型货车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '中型货车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '大型货车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '特大型货车'),(SELECT M.F_MAPKEY
              FROM DM_BAS_MAP M
             WHERE M.F_MAPVALUE = '集装箱车'),'MI') <= v_lst_time;

  COMMIT;
END sp_trafficdata_to_realvflow;

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!