Oracle有参数带返回值的存储过程简单例子
Oracle有参数带返回值的存储过程简单例子
Oracle有参数带返回值的存储过程简单例子:
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<currDate order by id desc ;
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值
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;
评论暂时关闭