Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL

oracleoracle-12cplsql

I am much more familiar with SQL Server, but I am doing a project now in Oracle. There is a concept from SQL Server that I am not sure how to accomplish in Oracle.

In SQL Server if I create a procedure

CREATE PROCEDURE usp_GetSomeStuff
(    
    @start_date DATE
    @end_date DATE
)
AS
BEGIN
SELECT
  Customer
, SUM( WidgetsPurchased ) AS WidgetsPurchased
FROM WidgetSales
WHERE TransactionDate >= @start_date
  AND TransactionDate <= @end_date
GROUP BY
  Customer
END

(Imagine a more complex query is involved). This gives application code a clean interface for 'selecting' this data without getting involved in a bunch of SQL. The application can just run usp_GetSomeStuff '2018-01-01', 2018-12-31' and get the results.

Oracle, on the other hand has a separation between the procedural language and the database language, and this is quite foreign to me.

When you are inside of a PL/SQL block you cannot perform DQL (unless assigning to cursors or variable).

For example, this is not legal:

create or replace procedure get_some_stuff
(
      in_start_date date
    , in_end_date date
)
is
begin
    select
      customer
    , sum( widgets_purchased ) as widgets_purchased
    from widgetsales
    where transactiondate >= in_start_date
      and transactiondate <= in_end_date
    group by
      customer
    ;
end;
/

PLS-00428: an INTO clause is expected in this SELECT statement

What is the 'Oracle Way' way of 'wrapping' a complex query for a caller that wants the results as if the caller had executed the select statement itself?

Note that we could also do this in a table valued function in SQL Server, which is even more convenient sometimes because we can call it as

select
 only
,these
,columns
from GetSomeStuff('2018-01-01', '2018-12-31')
where xyz = 123

With Oracle I just don't 'get it'. What's the pattern for doing this kind of thing in Oracle?

I am using Oracle 12c.

Best Answer

Oracle supports "table valued functions" as well. However they need a lot more boiler plate code than one would like. The main (annoying) "problem" is, that you need to explicitly define a type that can be returned: 

create type stuff_type as object
(
  customer varchar(50),
  widgets_purchased number
)
/

create stuff_list as table of stuff_type
/


create function get_stuff(p_start_date date, p_end_date date)
   return stuff_list
   pipelined
as
begin
  for rec in (select customer, sum(widgets_purchased) as widgets_purchased
              from widgetsales
              where transactiondate >= p_start_date
                and transactiondate <= p_end_date
              group by customer) 
  loop
    pipe row stuff_type(rec.customer, rec.widgets_purchased);     
  end loop;
end;
/

The usage is also a bit different:

select *
from table(get_stuff(current_date - 7, current_date));

Some more examples and insights can be found here: https://oracle-base.com/articles/misc/pipelined-table-functions

Starting with Oracle 12.1 you can also use implicit results:

create or replace procedure get_stuff(p_start_date date, p_end_date date)
as
  c1 SYS_REFCURSOR;  
BEGIN
  OPEN c1 FOR 
    select customer, sum(widgets_purchased) as widgets_purchased
    from widgetsales
    where transactiondate >= p_start_date
      and transactiondate <= p_end_date
    group by customer;

  DBMS_SQL.RETURN_RESULT(c1);
END;
/

Some more details and examples for implicit results: https://oracle-base.com/articles/12c/implicit-statement-results-12cr1

Related Question