Oracle Stored Procedures that Return a Dataset

Question

I'm a developer on the Microsoft stack (C#, SQL Server, EF, etc) that has inherited a webforms app that connects to an Oracle 11g database. The app is currently laden with inline SQL statements which I'd like to convert to parameterized stored procedures. However, being accustomed to T-SQL, I'm finding the move to PL/SQL a fairly steep learning curve.

Most of the SQL statements are fairly simple statements which return filtered datasets from the base table

select field1, field2, fieldn 
from foo 
where field1 = 'blah'

In T-SQL, this would be fairly straightforward

create procedure fooproc
  @filter varchar(100)
as
begin
    select field1, field2, field3 
    from foo 
    where field1 = @filter
end

Unfortunately, it doesn't seem to be this straightforward in PL/SQL. Upon searching, I've found answers which include:

1. Use a function instead of a procedure (which leads me to wonder if procedures in SQL Server map one-to-one to procedures in Oracle)
2. Create a "package" for the procedure (still not quite sure what that is)
3. Use a cursor or for loop (which seems unholy and just wrong)

In addition, most of the examples I've found online of Oracle stored procedures return a scalar value or no value at all. I'd think this to be a fairly common task that many people want to perform, but my google-fu must not be very strong on this one. So if anyone can help me translate, I'd be appreciative.

Thanks

Answer 1

A SQL Server stored procedure that just returns a result set would most naturally translate into an Oracle stored function that returns a cursor. Something like

CREATE OR REPLACE FUNCTION fooFunc( p_field1 IN foo.field1%type )
  RETURN sys_refcursor
IS
  l_rc sys_refcursor;
BEGIN
  OPEN l_rc
   FOR SELECT field1, field2, field3
         FROM foo
        WHERE field1 = p_field1;
  RETURN l_rc;
END;

In Oracle 12.1, there is some syntactic sugar for implicit results to make conversions from SQL Server easier by allowing procedures to return ref cursors implicitly but your question indicates that you're still on 11g so that probably isn't an option.

You could also have a procedure that has an out parameter of type sys_refcursor. Normally, though, you should use functions for objects that merely return results and procedures for objects that modify the data.

Normally, all of your Oracle procedures and functions would be wrapped up into packages that group together bits of related functionality. If you have half a dozen functions that let you query foo using different criteria, you'd want to put all of those functions in a single package just to keep things organized.