毕业设计做公交查询,在网上看到一个不错的存储过程可以用,可惜不是oracle可以用呢,因为自己是个菜鸟,改了几次都没成功,请高手帮忙看看,改成oracle可以用的
create or replace procedure InquiryT1(StartStop_in varchar,EndStop_in varchar)as
begin
select
r1.StartStop as 启始站点,
r1.route as 乘坐路线1,
r1.EndStop as 中转站点,
r2.route as 乘坐路线2,
r2.EndStop as 目的站点,
r1.StopCount+r2.StopCount as 总站点数
from
RouteT0 r1,
RouteT0 r2
where
r1.StartStop=StartStop_in
and r1.EndStop=r2.StartStop
and r2.EndStop=EndStop_in;
end ;
create or replace procedure InquiryT1(StartStop_in varchar,EndStop_in varchar)as
begin
select
r1.StartStop as 启始站点,
r1.route as 乘坐路线1,
r1.EndStop as 中转站点,
r2.route as 乘坐路线2,
r2.EndStop as 目的站点,
r1.StopCount+r2.StopCount as 总站点数
from
RouteT0 r1,
RouteT0 r2
where
r1.StartStop=StartStop_in
and r1.EndStop=r2.StartStop
and r2.EndStop=EndStop_in;
end ;
所以先定义一个包,然后返回游标类型就好了……
整个过程就一个select语句,如果要得到结果集需要用到输出游标的。建议这么写:
create or replace procedure InquiryT1(StartStop_in in varchar,EndStop_in in varchar, oc_result out sys_refcursor)
sql:='xxxxxxxxxxxxxxxxxxxxx';
open oc_result for sql;
end;
当然,一些异常处理要自己写咯~~