Sql-Server

如何將 SQL Server 儲存過程轉換為 Oracle 儲存過程?

  • February 22, 2019

這是我的 SQL Server 儲存過程,如下所示:

CREATE PROCEDURE flight_details
AS
BEGIN 
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END

EXECUTE flight_details

上述 SQL Server 儲存過程成功執行。

接下來,我嘗試將其轉換為如下所示的 Oracle 儲存過程程式碼:

CREATE OR REPLACE PROCEDURE flight_details
(p_flight_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_flight_details FOR
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END flight_details;

SET SERVEROUTPUT ON;

EXECUTE flight_details;

我嘗試在 Oracle SQL Developer 中創建此過程,但它顯示以下錯誤:

Error(5,1): PL/SQL: SQL Statement ignored

Error(7,133): PL/SQL: ORA-00933: SQL command not properly ended

Error(8,19): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array 

僅供參考,您可以訪問以下連結並將您的塊程式碼從 SQL 轉換為 Oracle。

http://sqlines.com/online

您在游標的 select 語句末尾缺少一個冒號。

WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No;

引用自:https://dba.stackexchange.com/questions/182217