巡逻车显示用到的sql和pl/sql介绍

Java代码　　

select　GIS_X,GIS_Y　from　car_trace　where　VEHICLE_ID　='"　&　carid　&　"'　and　rownum=1　order　by　record_time　desc

　　当我们做巡逻车显示时，常常需要显示某一辆巡逻车的最新位置，并且需要在所有的巡逻车中取得每一辆车的最新数据。这里需要用到一些sql语句和一些pl/sql函数，这里简要介绍一下：

　　一：基本的查询：

　　1.要查询某一辆车的最新的经纬度值需要用如下的语句：

　　Sql代码

select　GIS_X,GIS_Y　from　(select　*　from　car_trace　order　by　record_time　desc)　where　VEHICLE_ID　='"　&　carid　&　"'　and　rownum=1

　　而有些人会写成如下（错误的写法）：

　　Sql代码

select　GIS_X,GIS_Y　from　car_trace　where　VEHICLE_ID　='"　&　carid　&　"'　and　rownum=1　order　by　record_time　desc

　　分析：

　　明白为什么要用类似于第一句sql嵌套查询，如果不用的话，像第二句，先查出了满足where条件的记录再order by,即“rownum=1”了再来排序所以肻定是错误的。所以正确的是，应该先在排好顺序的记录中去查找满足的记录，而不是查找出需要条件的记录了再去排序，这是初学者容易弄错的地方。

　　另外，这种sql嵌套查询在稍微复杂些的地方还是用得比较多的。

　　2.如果需要在所有的巡逻车中取得每一辆车的最新数据应该怎么做呢？需要用到sql里的分组后排序的写法。语法如下（具体的语法请自行参照文档）：

　　row_number() over(partition by .. order by ..)

　　则取所有的巡逻车中取得每一辆车的最新数据的sql语句如下：

　　Sql代码

select　gis_x,gis_y,VEHICLE_ID　from　(select　row_number()　over(partition　by　vehicle_id　order　by　record_time　desc)　flagnum,gis_x,gis_y,vehicle_id　from　car_trace)　where　flagnum=1

　　分析：

　　分组后排序，分组是知道了有多少车，排序是取了每辆车最新的记录，通过"flagnum=1"来取最新的这条记录。

　　二：关于性能的优化：

　　我们的car_trace时时接收GPS传过来的经纬度、速度、方向、角度等信息，那么这张表随着时间的增加也会变得越来越大，虽然可以用oracle的“”来解决问题，但我们可以用较为简单的方法：建立一个最新的轨迹表（car_trace_now），写个触发器，当轨迹表有插入数据时就把这记录触发到car_trace_now中，没有则插入，有则更新。这样，car_trace_now里总是最新的数据，并且数据量很小，有多少辆车就有多少条数据。代码如下：

　　Sql代码　　　

--是否有相同的记录　
CREATE　OR　REPLACE　FUNCTION　IS_EXISTS_REC(ID　VARCHAR2)　
RETURN　NUMBER　
AS　
　iReturn　NUMBER;　
　CURSOR　REC_EXISTS　IS　
　SELECT　count(*)　FROM　CAR_TRACE_NOW　WHERE　VEHICLE_ID=ID;　
BEGIN　
　iReturn　:=　0;　
　OPEN　REC_EXISTS;　
　FETCH　REC_EXISTS　INTO　iReturn;　
　CLOSE　REC_EXISTS;　
　RETURN　iReturn;　
END;　
--触发器　
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;　

　　----------------------------------END--------------------------------