ORACLE：在 PL/SQL 中使用 CTE（通用表表达式）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26890512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE: Using CTEs (Common Table Expressions) with PL/SQL
提问by VermontCoder
First off, my background is in SQL Server. Using CTEs (Common Table Expressions) is a breeze and converting it to a stored procedure with variables doesn't require any changes to the structure of the SQL other than replacing entered values with variable names.
首先，我的背景是 SQL Server。使用 CTE（通用表表达式）轻而易举，将其转换为带有变量的存储过程，除了用变量名替换输入的值外，不需要对 SQL 的结构进行任何更改。
In Oracle PL/SQL however, it is a completely different matter. My CTEs work fine as straight SQL, but once I try to wrap them as PL/SQL I run into a host of issues. From my understanding, a SELECT now needs an INTO which will only hold the results of a single record. However, I am wanting the entire recordset of multiple values.
然而，在 Oracle PL/SQL 中，情况完全不同。我的 CTE 可以像直接 SQL 一样正常工作，但是一旦我尝试将它们包装为 PL/SQL，我就遇到了许多问题。根据我的理解，SELECT 现在需要一个 INTO，它只保存一条记录的结果。但是，我想要多个值的整个记录集。
My apologies if I am missing the obvious here. I'm thinking that 99% of my problem is the paradigm shift I need to make.
如果我在这里遗漏了明显的内容，我深表歉意。我认为我的问题的 99% 是我需要进行的范式转变。
Given the following example:
给出以下示例：
NOTE: I am greatly over simplifying the SQL here. I do know the below example can be done in a single SQL statement. The actual SQL is much more complex. It's the fundamentals I am looking for here.
注意：我在这里大大简化了 SQL。我知道下面的例子可以在单个 SQL 语句中完成。实际的 SQL 要复杂得多。这是我在这里寻找的基础知识。
WITH A as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 200),
B as (SELECT * FROM A WHERE EMPLOYEE_START_DATE > date '2014-02-01'),
C as (SELECT * FROM B WHERE EMPLOYEE_TYPE = 'SALARY')
SELECT 'COUNTS' as Total,
(SELECT COUNT(*) FROM A) as 'DEPT_TOTAL',
(SELECT COUNT(*) FROM B) as 'NEW_EMPLOYEES',
(SELECT COUNT(*) FROM C) as 'NEW_SALARIED'
FROM A
WHERE rowcount = 1;
Now if I want to make this into PL/SQL with variables that are passed in or predefined at the top, it's not a simple matter of declaring the variables, popping values into them, and changing my hard-coded values into variables and running it. NOTE: I do know that I can simply change the hard-coded values to variables like :Department, :StartDate, and :Type, but again, I am oversimplifying the example.
现在，如果我想将其放入带有传入或在顶部预定义的变量的 PL/SQL，这不是声明变量、将值弹出到其中并将我的硬编码值更改为变量并运行它的简单问题. 注意：我知道我可以简单地将硬编码值更改为变量，如 :Department、:StartDate 和 :Type，但同样，我过于简化了示例。
There are three issues I am facing here that I am trying to wrap my head around:
我在这里面临三个问题，我正试图解决这些问题：
1) What would be the best way to rewrite this using PL/SQL with declared variables? The CTEs now have to go INTO something. But then I am dealing with one row at a time as opposed to the entire table. So CTE 'A' is a single row at a time, and CTE B will only see the single row as opposed to all of the data results of A, etc. I do know that I will most likely have to use CURSORS to traverse the records, which somehow seems to over complicate this.
1) 使用带有声明变量的 PL/SQL 重写它的最佳方法是什么？CTE 现在必须进入某些领域。但是我一次处理一行而不是整个表。所以 CTE 'A' 一次是一行，而 CTE B 只会看到单行而不是 A 的所有数据结果等等。我知道我很可能不得不使用 CURSORS 来遍历记录，这似乎使这件事变得过于复杂。
2) The output now has to use DBMS_OUTPUT. For multiple records, I will have to use a CURSOR with FETCH (or a FOR...LOOP). Yes?
2) 输出现在必须使用 DBMS_OUTPUT。对于多个记录，我将不得不使用带有 FETCH（或 FOR...LOOP）的 CURSOR。是的？
3) Is there going to a big performance issue with this vs. straight SQL in regards to speed and resources used?
3) 在速度和使用的资源方面，这个与直接 SQL 相比会有很大的性能问题吗？
Thanks in advance and again, my apologies if I am missing something really obvious here!
再次提前致谢，如果我在这里遗漏了一些非常明显的内容，我深表歉意！
回答by Olafur Tryggvason
Simplest way is to wrap it into an implicit for loop
最简单的方法是将它包装成一个隐式的 for 循环
begin
for i in (select object_id, object_name
from user_objects
where rownum = 1) loop
-- Do something with the resultset
dbms_output.put_line (i.object_id || ' ' || i.object_name);
end loop;
end;
Single row query without the need to predefine the variables.
无需预定义变量的单行查询。
回答by TommCatt
First, this has nothing to do with CTEs. This behavior would be the same with a simple select * from tablequery. The difference is that with T-SQL, the query goes into an implicit cursor which is returned to the caller. When executing the SP from Management Studio this is convenient. The result set appears in the data window as if we had executed the query directly. But this is actually non-standard behavior. Oracle has the more standard behavior which might be stated as "the result set of any query that isn't directed into a cursor must be directed to variables." When directed into variables, then the query must return only one row.
首先，这与 CTE 无关。此行为与简单select * from table查询相同。不同之处在于，使用 T-SQL，查询进入一个隐式游标，该游标返回给调用者。从 Management Studio 执行 SP 时，这很方便。结果集出现在数据窗口中，就像我们直接执行了查询一样。但这实际上是非标准行为。Oracle 具有更标准的行为，可以将其表述为“任何未定向到游标的查询的结果集都必须定向到变量”。当定向到变量时，查询必须只返回一行。
To duplicate the behavior of T-SQL, you just have to explicitly declare and return the cursor. Then the calling code fetches from the cursor the entire result set but one row at a time. You don't get the convenience of Sql Developer or PL/SQL Developer diverting the result set to the data display window, but you can't have everything.
要复制 T-SQL 的行为，您只需显式声明并返回游标。然后调用代码从游标中获取整个结果集，但一次一行。你没有得到 Sql Developer 或 PL/SQL Developer 将结果集转移到数据显示窗口的便利，但你不能拥有一切。
However, as we don't generally write SPs just to be called from the IDE, it is easier to work with Oracle's explicit cursors than SQL Server's implicit ones. Just google "oracle return ref cursor to caller" to get a whole lot of good material.
但是，由于我们通常不会编写只是为了从 IDE 调用的 SP，因此使用 Oracle 的显式游标比使用 SQL Server 的隐式游标更容易。只需谷歌“oracle return ref cursor to caller”即可获得大量优质资料。
