主页 / dba / 问题 / 182233
Accepted
stranger
Asked: 2017-08-01 01:14:57 +0800 CST

在 Oracle PL/SQL 中编写一个简单的 SELECT 存储过程

  • 772

这是我的 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:
stored-procedures plsql

2 个回答

  1. 您需要传入 aREFCURSOR以将该过程用作其输出 ( OUT) 参数。

    快速测试程序：

    CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_passenger_details FOR
SELECT 'test test' as Full_Name, 10 as Age, 'alien' as Nationality, 'foo' as Category, 
'Name' as Airline_Name, 'wobble' as Class_Type
FROM dual;
END passenger_details;
/

    在 SQL*Plus 中测试：

    SQL>变量 mycursor refcursor; 
SQL> exec 乘客详细信息 (:mycursor);

PL/SQL 过程成功完成。

SQL>打印光标；

FULL_NAME 年龄 NATIO CAT AIRL CLASS_
--------- ---------- ----- --- ---- ------
test test 10 外星人 foo Name wobble

SQL>
    • 5
  2. Best Answer

    您可以使用它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。

    • 5

相关问题