在 Oracle PL/SQL 中編寫一個簡單的 SELECT 儲存過程

這是我的 SQL Server 儲存過程，如下所示：

CREATE PROCEDURE passenger_details
AS
BEGIN
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No
END

EXECUTE passenger_details

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

然後我嘗試在 Oracle PL/SQL 中執行相同的儲存過程，如下所示：

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_passenger_details FOR
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No;
END passenger_details;

以上Oracle PL/SQL中的儲存過程編譯成功。

然後我嘗試執行它，如下所示：

SET SERVEROUTPUT ON;
EXECUTE passenger_details;

在嘗試執行儲存過程時，我收到以下錯誤消息，如下所示：

Error starting at line : 12 in command -
BEGIN passenger_details; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PASSENGER_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

您可以使用它dbms_sql來返回隱式結果。這是 Oracle 12 中添加的一項功能，用於簡化從 SQL Server 的遷移：

CREATE PROCEDURE passenger_details
AS
 c1 SYS_REFCURSOR;  
BEGIN

 open c1 for
 SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
 FROM Passenger
   JOIN Ticket on Passenger.Passenger_No = Ticket.Passenger_No
   JOIN Airline ON Airline.Airline_No = Ticket.Airline_No
   JOIN Class ON Class.Class_No = Ticket.Class_No;

 DBMS_SQL.RETURN_RESULT(c1);
END;
/

請注意，我用顯式運算符替換了古老且過時的隱式連接JOIN。

引用自：https://dba.stackexchange.com/questions/182233