巡邏車即時定位系統：SQL 與 PL/SQL 寫法解析與效能優化實戰

車隊管理系統中，需即時顯示每輛巡邏車最新 GPS 座標。本文以 Oracle 為例，對比錯誤與正確寫法，介紹分析函數 ROW_NUMBER() OVER 解決「分組取最新」問題，並透過觸發器維護「最新軌跡表」大幅提升查詢效能。

一、基本查詢：避開 ROWNUM 陷阱

錯誤寫法

SELECT GIS_X, GIS_Y
FROM car_trace
WHERE VEHICLE_ID = :carid AND ROWNUM = 1
ORDER BY RECORD_TIME DESC

原因：Oracle 先過濾 WHERE 再取 ROWNUM=1，最後才 ORDER BY，導致取到的非最新筆。

正確寫法：子查詢先排序

SELECT GIS_X, GIS_Y
FROM (SELECT * FROM car_trace ORDER BY RECORD_TIME DESC)
WHERE VEHICLE_ID = :carid AND ROWNUM = 1

二、全車隊各車最新位置：分析函數一行搞定

SELECT GIS_X, GIS_Y, VEHICLE_ID
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY VEHICLE_ID ORDER BY RECORD_TIME DESC) AS rn,
           GIS_X, GIS_Y, VEHICLE_ID
    FROM car_trace
)
WHERE rn = 1

原理：PARTITION BY VEHICLE_ID 分組，ORDER BY RECORD_TIME DESC 組內排序，ROW_NUMBER() 產生序號，外層篩選 rn=1 即得各車最新一筆。

三、效能優化：最新軌跡表 + 觸發器

car_trace 隨時間暴增，全表掃描極慢。解決方案：建立 car_trace_now (每車僅一筆)，寫入觸發器同步維護。

1. 判斷函數

CREATE OR REPLACE FUNCTION IS_EXISTS_REC (p_id VARCHAR2) RETURN NUMBER AS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM car_trace_now WHERE VEHICLE_ID = p_id;
  RETURN v_cnt;
END;

2. AFTER INSERT 觸發器

CREATE OR REPLACE TRIGGER TRI_CAR_TRACE_NOW
AFTER INSERT ON car_trace FOR EACH ROW
BEGIN
  IF IS_EXISTS_REC(:NEW.VEHICLE_ID) = 0 THEN
    INSERT INTO car_trace_now (GIS_X, GIS_Y, VEHICLE_ID, RECORD_TIME, SPEED, ANGLE, KM_NUM)
    VALUES (:NEW.GIS_X, :NEW.GIS_Y, :NEW.VEHICLE_ID, :NEW.RECORD_TIME, :NEW.SPEED, :NEW.ANGLE, :NEW.KM_NUM);
  ELSE
    UPDATE car_trace_now
    SET GIS_X = :NEW.GIS_X, GIS_Y = :NEW.GIS_Y, RECORD_TIME = :NEW.RECORD_TIME,
        SPEED = :NEW.SPEED, ANGLE = :NEW.ANGLE, KM_NUM = :NEW.KM_NUM
    WHERE VEHICLE_ID = :NEW.VEHICLE_ID;
  END IF;
END;

效益

• car_trace_now 資料量 = 車輛數 (極小)，查詢無需索引即毫秒級返回。
• 避免分區表、Materialized View 等複雜架構，維護成本低。
• 適用於任何「高寫入、只需最新狀態」的 IoT/車聯網場景。