How to create a PL/SQL stored procedure with input parameter to return a dataset

Question

I am new to PL/SQL and trying to create a stored procedure with some input parameters that we will be using to generate our reports.

The aim is to be able to generate 100s or rows of data based on the input parameter that the user will input.

I can do it in SQL Server but I am not sure how to go about it in Oracle.

For example:

Create Procedure usp_employees  
     (@Deptid int not null, 
      @MaritalStatus varchar(10) null)
As
Begin
    Select Firstname, LastName, HireDate, DepartmentName, Marital_Status
    From EmployeeTable
    Where DeparatmentID = @Deptid
      and Marital_Status = @MaritalStatus
End;

And then I execute it like this:

Execute usp_employees 1,'F';

Can someone please point me on how to recreate and execute this using PL/SQL?

Thanks.

With a more up-to-date version you could use an implicit result set: https://stackoverflow.com/a/40360471 — a_horse_with_no_name, Jan 21 '19 at 08:02

Gauravsa · Accepted Answer · 2019-01-21T05:22:05.213

0

You can do this:

CREATE OR REPLACE PROCEDURE usp_employees 
  (p_dept_id           IN int,
   p_MaritalStatus     IN VARCHAR2(10), 
   p_cursor            OUT SYS_REFCURSOR)
IS
BEGIN
   Open p_cursor for
    select Firstname, LastName, HireDate,    
           DepartmentName, Marital_Status
    from EmployeeTable
    where DeparatmentID=p_dept_id
    and Marital_Status=p_maritalstatus;

EXCEPTION
WHEN no_data_found THEN 
NULL;
 END;
/

To run, through the GUI, right click on stored procedure, then click Test. Provide the input parameters and click on execute. You can either Step into the code or Over. Once complete, select “...” next to the ref cursor to view resultset.

Through the query,

SQL> var rc refcursor
SQL> execute myproc(10, “Single”, :test)
SQL> print rc

edited Jan 21 '19 at 05:22

answered Jan 19 '19 at 23:51

Gauravsa

5,551
1
13
23

Thanks Gauravsa. What is the best way to execute it to view the result? – Bdk Jan 20 '19 at 00:17
Usually I right click on stored procedure and click Test. Then provide input in the test window and click on “...” to view the resultset – Gauravsa Jan 20 '19 at 01:37
If you have a procedure with one output parameter then I would rather create a function `CREATE OR REPLACE FUNCTION usp_employees (p_dept_id IN integer, p_MaritalStatus IN VARCHAR2) RETURN SYS_REFCURSOR IS ...` – Wernfried Domscheit Jan 20 '19 at 12:17
Wernfried Domscheit, what are some of the pros of creating a function rather than a stored proc in this case? – Bdk Jan 21 '19 at 04:55

How to create a PL/SQL stored procedure with input parameter to return a dataset

1 Answers1