CREATE OR REPLACE PROCEDURE InsertGPSData
(
gpsTime date,
lon number,
lat number,
spe number,
dir number,
mobile varchar2
)
as
tempID number;
temPosID number;
BEGIN
tempID :=0;
temPosID :=0;
select id into tempID from b_vehicle_info_pos_v where gps_mobile_no=mobile;
if tempID>0 then
begin
insert into b_vehicle_history (vehicle_id,gps_mobile_no,gps_time,x,y,speed,direction,receive_time) values(tempID,mobile,gpsTime,lon,lat,spe,dir,sysdate);
select ID into temPosID from b_vehicle_pos where gps_mobile_no=mobile;
if temPosID>0 then
begin
update b_vehicle_pos set gps_time=gpsTime,x=lon,y=lat,speed=spe,direction=dir,receive_time=sysdate where gps_mobile_no=mobile;
end;
else
begin
insert into b_vehicle_pos (id,gps_mobile_no,gps_time,x,y,speed,direction,receive_time) values(vehicle_pos_id_seq.nextval,
mobile,gpsTime,lon,lat,spe,dir,sysdate);
end;
end if;
end;
end if;
commit;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
rollback work;
END InsertGPSData;
大家帮我看看执行总是有问题提示非法字符,大家帮我改改
(
gpsTime date,
lon number,
lat number,
spe number,
dir number,
mobile varchar2
)
as
tempID number;
temPosID number;
BEGIN
tempID :=0;
temPosID :=0;
select id into tempID from b_vehicle_info_pos_v where gps_mobile_no=mobile;
if tempID>0 then
begin
insert into b_vehicle_history (vehicle_id,gps_mobile_no,gps_time,x,y,speed,direction,receive_time) values(tempID,mobile,gpsTime,lon,lat,spe,dir,sysdate);
select ID into temPosID from b_vehicle_pos where gps_mobile_no=mobile;
if temPosID>0 then
begin
update b_vehicle_pos set gps_time=gpsTime,x=lon,y=lat,speed=spe,direction=dir,receive_time=sysdate where gps_mobile_no=mobile;
end;
else
begin
insert into b_vehicle_pos (id,gps_mobile_no,gps_time,x,y,speed,direction,receive_time) values(vehicle_pos_id_seq.nextval,
mobile,gpsTime,lon,lat,spe,dir,sysdate);
end;
end if;
end;
end if;
commit;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
rollback work;
END InsertGPSData;
大家帮我看看执行总是有问题提示非法字符,大家帮我改改
vehicle_pos_id_seq 是表b_vehicle_pos建的一个序列