Linux安全网 - Linux操作系统_Linux 命令_Linux教程_Linux黑客

会员投稿 投稿指南 本期推荐:
搜索:
您的位置: Linux安全网 > Linux编程 > 数据库管理 > » 正文

Oracle有参数带返回值的存储过程简单例子

来源: yuefengyuan 分享至:
 

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;


Tags:
分享至:
最新图文资讯
1 2 3 4 5 6
验证码:点击我更换图片 理智评论文明上网,拒绝恶意谩骂 用户名:
关于我们 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 发展历史