create or replace procedure caf_trackdiffbyId(
sendid in number,
userid in varchar ,
diffnum in number,
lats in number,
latn in number,
lngw in number,
lnge in number,
minid out number ,
maxid out number
) is
currDate date;
minDate date;
maxDate date;
subMin number;
Cursor baseMinCursor is select * from caf_usertrack where datetime Cursor baseMaxCursor is select * from caf_usertrack where datetime>currDate order by id asc ; begin select datetime into currDate from caf_usertrack where id=sendid and userid=userid and lat >= lats and lat <= latn and lng >= lngw and lng <= lnge ; begin if currDate is not null then begin minDate := currDate; maxDate := currDate; end; end if; for sysd in baseMinCursor loop --取得最小的ID值 www.2cto.com begin subMin := round(to_number(minDate-sysd.datetime)*24*60,2); if subMin <= diffnum then begin minDate := sysd.datetime; end; else begin minid := sysd.id; dbms_output.put_line('min'||minid); exit; end; end if; end; end loop; for sysd in baseMaxCursor loop --取得最大的ID值 begin subMin := round(to_number(sysd.datetime-maxDate)*24*60,2); if subMin <= diffnum then begin maxDate := sysd.datetime; end; else begin maxid := sysd.id; dbms_output.put_line('maxid'||maxid); exit; end; end if; end; end loop; end; end caf_trackdiffbyId; 摘自 yuefengyuan的专栏