Stored-Procedures
在 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
。