*IT 存储过程：足迹第七十八步：存储过程里的循环与定时

1.存储过程

CREATE OR REPLACE 
procedure f_12113298 as
//声明变量
v_imei NVARCHAR2(52);
v_lat  NUMBER;
v_lon  NUMBER;
v_alt NUMBER;
v_acc NUMBER;
v_speed NUMBER;
v_dir NUMBER;
v_time DATE;
v_add NVARCHAR2(520);
v_status VARCHAR2(80);
s NUMBER;
m NUMBER;
d DATE;
begin
//为启动条件和循环条件赋值
select count(1) into s from VEHICLE_GPS_TRACK t where t.imei='12113289'and to_char(t.TIME,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
select count(1) into m from VEHICLE_GPS_TRACK t where t.imei='12113298'and to_char(t.TIME,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
select nvl(max(TIME),TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')) into d from VEHICLE_GPS_TRACK where imei='12113289';
//启动条件
if(s<m) then

DECLARE 
temp_emp VEHICLE_GPS_TRACK%rowtype;
//声明游标=所针对遍历的集合对象
cursor mycursor is select * from VEHICLE_GPS_TRACK t where t.imei='12113298' and TIME > d and to_char(t.TIME,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') order by TIME;


BEGIN
open mycursor;
//遍历的边界
for i in 0..m loop
fetch mycursor into temp_emp;
exit when mycursor%notfound;

//循环执行的业务
v_imei :='12113289';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS) values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);

 v_imei :='12113359';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS) values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);
 
v_imei :='8080523011';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS) values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);
 
v_imei :='8080523032';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS)values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);
 
v_imei :='8080523028';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS) values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);
 
v_imei :='11962367';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS)values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);
 
v_imei :='11962365';
insert into VEHICLE_GPS_TRACK (IMEI,LATITUDE,LONGITUDE,ALTITUDE,ACCURACY,SPEED,DIRECTION,TIME,ADDRESS,STATUS)values (v_imei,temp_emp.LATITUDE,temp_emp.LONGITUDE,temp_emp.ALTITUDE,temp_emp.ACCURACY,temp_emp.SPEED,temp_emp.DIRECTION,temp_emp.TIME,temp_emp.ADDRESS,temp_emp.STATUS);
 

end loop;
close mycursor;
end;
end if;
  
commit;

end;

2.任务定时，实现定时增量同步

declare
job1 number; 
begin
 dbms_job.submit(job1,
     what => 'f_12113298;',
     next_date => sysdate,
     interval => 'sysdate+1/(24*60*60)'); -- 每隔1s处理一次用户表
   commit;
end;