難以理解來自 T-SQL 的 Oracle DQL 過程和函式

我對 SQL Server 更熟悉，但我現在在 Oracle 做一個項目。SQL Server 中有一個概念，我不確定如何在 Oracle 中實現。

在 SQL Server 中，如果我創建一個過程

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

（想像一下涉及更複雜的查詢）。這為應用程式碼提供了一個乾淨的界面，用於“選擇”這些數據，而無需涉及一堆 SQL。該應用程序可以執行usp_GetSomeStuff '2018-01-01', 2018-12-31'並獲得結果。

另一方面，Oracle 將過程語言和數據庫語言分開，這對我來說很陌生。

當您在 PL/SQL 塊內時，您無法執行 DQL（除非分配給游標或變數）。

例如，這是不合法的：

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

什麼是“包裝”複雜查詢的“Oracle 方式”，用於呼叫者想要結果，就好像呼叫者已經執行了 select 語句本身一樣？

請注意，我們也可以在 SQL Server 中的表值函式中執行此操作，這有時甚至更方便，因為我們可以將其稱為

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

對於 Oracle，我只是不“明白”。在 Oracle 中做這種事情的模式是什麼？

我正在使用 Oracle 12c。

Oracle 也支持“表值函式”。然而，他們需要比人們想要的更多的樣板程式碼。主要（煩人的）“問題”是，您需要顯式定義可以返回的類型：

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;
/

用法也有點不同：

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

可以在此處找到更多範例和見解：https ://oracle-base.com/articles/misc/pipelined-table-functions

---

從 Oracle 12.1 開始，您還可以使用隱式結果：

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;
/ 

隱式結果的更多詳細資訊和範例：https ://oracle-base.com/articles/12c/implicit-statement-results-12cr1

引用自：https://dba.stackexchange.com/questions/221064