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;