Writing a simple SELECT Stored Procedure in Oracle PL/SQL
You need to pass in a REFCURSOR
for the procedure to use as its output (OUT
) parameter.
Quick procedure to test with:
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;
/
Test in SQL*Plus:
SQL> variable mycursor refcursor; SQL> exec passenger_details ( :mycursor ); PL/SQL procedure successfully completed. SQL> print mycursor; FULL_NAME AGE NATIO CAT AIRL CLASS_ --------- ---------- ----- --- ---- ------ test test 10 alien foo Name wobble SQL>
You can use dbms_sql
to return implicit results. This was a feature added to Oracle 12 to ease the migration from 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;
/
Note that I replaced the ancient and outdated implicit joins with an explicit JOIN
operator.