A simple example of a stored procedure with parameters and returned values in 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 -- get the minimum ID value 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 -- get the largest ID value

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;

 

From yuefengyuan's column