百分求把这个T-SQL存储过程改成PL-SQL的存储过程

Question

如题,我正迁移一个原来基于MS-SQL的系统到Oracle上,以下是我从原MS-SQL的存储过程改写的,但有错误,请帮忙修改,谢谢PROCEDURE usp_closestvehiclesbyaddress(n_centerlat IN number,n_centerlong IN number,n_customerid IN int,N_ID OUT int,V_Name OUT varchar2,N_Distance OUT number,N_Direction OUT number,N_Latitude OUT number,N_Longitude OUT number) ASBEGINSELECT units.id, units.name, Results.distance, lastfix.direction, lastfix.latitude longitude, lastfix.longitude latitudeINTOn_ID,V_NAME,N_DISTANCE,N_DIRECTION,N_LATITUDE,N_LongitudeFROM units,lastfix,(SELECT lastfix.address,MIN(ufn_CalculateDistance(n_centerlat, n_centerlong, longitude, latitude)) distanceFROM lastfix, units, unitprivWHERE unitpriv.CustomerID = n_customeridAND unitpriv.UnitID = units.IDAND units.ip_address = lastfix.addressAND rownum <=10GROUP BY lastfix.addressORDER BY distance ASC) ResultsWHERE units.ip_address = Results.addressand Results.address= lastfix.addressORDER BY distance ASC;END;
----------------------------------------------------------
FUNCTION ufn_calculatedistance(n_longitude1 in number,n_latitude1 in number,n_longitude2 in number,n_latitude2 in number) return numberasn_distance number;pi number;beginn_distance :=0;pi := 3.14159265358979323;select 1000 * ( 6371 * ACOS(COS((pi * n_latitude1/180)-(pi * n_latitude2/180)) *COS((pi * n_longitude1/180))* COS(pi * n_longitude2/180) +SIN(pi * n_longitude1/180)* SIN(pi * n_longitude2/180)))into n_distance from dual;return n_distance;end;

Answer 1

估计是
SELECT lastfix.address .
.
.ORDER BY distance ASC; 
出现了多条纪录
你的存储过程没有异常处理的啊
一般都会在end前加上这两句
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      raise;
   WHEN OTHERS
   THEN
      raise;
另外你这条语句没必要用order by的，因为正常应该是只出现一行纪录（你的错误估计就是出现不正常情况，出现多条纪录）

Answer 2

谢谢各位,以下是错误信息:9:19:06  
9:19:06  *** SCRIPT START :  Session:AVL@AVLDB(1)   18-Jun-2008 9:19:06 *** 
9:19:06  Start SQL Editor Execution ...
9:19:06  Processing ...
9:19:06  declare
9:19:06   *
9:19:06  ORA-01422: exact fetch returns more than requested number of rows
9:19:06  ORA-06512: at "AVL.USP_CLOSESTVEHICLESBYADDRESS", line 25
9:19:06  ORA-06512: at line 17
9:19:06  *** Script stopped due to error ***
9:19:06  *** SCRIPT END :  Session:AVL@AVLDB(1)   18-Jun-2008 9:19:06 *** 
9:19:06  End SQL Editor Execution

Answer 3

果然是的返回多行   如果有多个值返回：ORA-01422: 实际返回的行数超出请求的行数
存储过程内的select只能返回一条纪录的，不能返回多条

Answer 4

TO:Hebo2005:
那应该如何改呢?

Answer 5

不知道你这存储过程是要干什么？多条纪录用游标处理PROCEDURE usp_closestvehiclesbyaddress( n_centerlat IN number ,n_centerlong IN number ,n_customerid IN int, N_ID OUT int, V_Name OUT varchar2, N_Distance OUT number, N_Direction OUT number, N_Latitude OUT number, N_Longitude OUT number ) AS cursor cur_a 
      as
        SELECT units.id, units.name, Results.distance, lastfix.direction, lastfix.latitude longitude, lastfix.longitude latitude INTO FROM units ,lastfix ,( SELECT lastfix.address ,MIN(ufn_CalculateDistance(n_centerlat, n_centerlong, longitude, latitude)) distance FROM lastfix, units, unitpriv WHERE unitpriv.CustomerID = n_customerid AND unitpriv.UnitID = units.ID AND units.ip_address = lastfix.address AND rownum <=10 GROUP BY lastfix.address ORDER BY distance ASC ) Results WHERE units.ip_address = Results.address and Results.address= lastfix.address
 ORDER BY distance ASC; begin 
     open cur_a;
loop
     fetch cur_a into
     n_ID,  V_NAME,N_DISTANCE,N_DIRECTION, N_LATITUDE, N_Longitude ;
     exit when cur_a%notfound;
     作相应的处理
end loop;EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      null;
   WHEN OTHERS
   THEN
      raise;
END;

Answer 6

To:hebo2005,我copy 你的代码进去，但有如下错误：
(1): PLS-00103: Encountered the symbol "AS" when expecting one of the following:(1):    ( ; is return
(2): PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
(2):    , from
(3): PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
请问是语法错误吗？第一次用oracle，请多帮忙，谢谢

Answer 7

to hebo2005,对了，以下是原SQL2005的代码，谢谢USE [GPS]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClosestVehiclesByAddress]    Script Date: 06/18/2008 12:00:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO/****** Object:  Stored Procedure dbo.usp_ClosestVehiclesByAddress    Script Date: 1/5/2007 10:56:37 ******/
ALTER PROCEDURE [dbo].[usp_ClosestVehiclesByAddress](
@centerlat real 
,@centerlong real
,@customerid int
) ASSELECT units.id, units.name, Results.distance as distance, lastfix.direction, lastfix.latitude as longitude, lastfix.longitude as latitude
FROM units
,lastfix
,(
SELECT TOP 10
lastfix.address
,MIN(dbo.ufn_CalculateDistanceKM(@centerlat, @centerlong, longitude, latitude)) as distance
FROM 
lastfix, units, unitpriv
WHERE 
unitpriv.CustomerID = @customerid
AND unitpriv.UnitID = units.ID
AND units.ip_address = lastfix.address
GROUP BY lastfix.address
ORDER BY distance ASC
) AS Results
WHERE 
units.ip_address = Results.address
and Results.address= lastfix.address
ORDER BY distance ASC

Answer 8

PROCEDURE usp_closestvehiclesbyaddress( n_centerlat IN number ,n_centerlong IN number ,n_customerid IN int, N_ID OUT int, V_Name OUT varchar2, N_Distance OUT number, N_Direction OUT number, N_Latitude OUT number, N_Longitude OUT number ) iS cursor cur_a 
      is
        SELECT units.id, units.name, Results.distance, lastfix.direction, lastfix.latitude longitude, lastfix.longitude latitude FROM units ,lastfix ,( SELECT lastfix.address ,MIN(ufn_CalculateDistance(n_centerlat, n_centerlong, longitude, latitude)) distance FROM lastfix, units, unitpriv WHERE unitpriv.CustomerID = n_customerid AND unitpriv.UnitID = units.ID AND units.ip_address = lastfix.address AND rownum <=10 GROUP BY lastfix.address ORDER BY distance ASC ) Results WHERE units.ip_address = Results.address and Results.address= lastfix.address
 ORDER BY distance ASC; begin 
     open cur_a;
loop
     fetch cur_a into
     n_ID,  V_NAME,N_DISTANCE,N_DIRECTION, N_LATITUDE, N_Longitude ;
     exit when cur_a%notfound;
     作相应的处理
end loop;EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      null;
   WHEN OTHERS
   THEN
      raise;
END;

Answer 9

谢谢 hebo2005,等我调试成功后,马上结贴

Answer 10

 FOR cur_tm IN cur_a  LOOP
    作相应的处理
 End Loop;
为什么不用隐形的游标呢，我们老大都让我们这样用。说这样简单，不用OPEN后，在CLOSE了
不过，还是要UP10楼的

Answer 11

hi,hebo2005,事实上,我是要用这存储过程返回多条记录的,但您帮我改的只返回一条记录,以下是我在SQL2005中运行结果:
非常感谢你的帮忙
 id          name               distance      direction  longitude      latitude
-----------------------------------------------------------------------------------
18162 Teco 16-3009 R Smith 3105.040 52 -80.15749000 25.92065000
18393 Teco 51-3014 R Wall 3124.511 336 -80.37097000 26.09171000
17074 Teco 16-3012 J Johnson 3155.028 179 -81.74311000 26.30448000
17073 Teco 16-2008 J Kosty 3157.504 0 -81.77171000 26.32486000
14067 Teco 16-2003 G Mendez 3176.639 6 -81.83705000 26.49293000
18392 Teco 07-1130 C Byrd 3194.658 0 -81.80403000 26.65776000
18366 Teco 16-2004         3194.665 95 -81.80348000 26.65786000
17082 Teco 16-2007 G Workman 3194.673 85 -81.80387000 26.65790000
17648 Teco 16-2005 D Diaz 3194.678 342 -81.80366000 26.65796000
18318 Teco 16-4001 D Snail 3194.695 0 -81.80389000 26.65810000

Answer 12

我使用 SQL Navigtor 编辑和调试的,请问如何可以看到返回的结果集,以下是调试脚本:declareN_ID int;V_Name varchar2(50);N_Distance int;N_Direction int;N_Latitude int;N_Longitude int;beginusp_closestvehiclesbyaddress(40.00747,-82.95899, 1, N_ID,V_Name,N_Distance,N_Direction,N_Latitude,N_Longitude);end; 

Answer 13

不知道你的存储过程派什么用处的？
如果没有其它处理，就不需要存储过程，直接用sql就行了
如果想在存储过程里输出显示数据，可以用
DBMS_OUTPUT.put_line(变量)  来显示

Answer 14

非常谢谢hebo2005,我用包+存储过程实现了,但在ASP中调用有问题,后来直接用sql statement了,能把你的Email用私信发我吗?