ORACLE：在PL/SQL中使用CTE(公共表表达式)

1rhkuytd 于 2023-06-22 发布在 Oracle

关注(0)|答案(3)|浏览(153)

首先，我的背景是SQL Server。使用CTE（公共表表达式）是一件轻而易举的事，将其转换为具有变量的存储过程不需要对SQL的结构进行任何更改，而只是将输入的值替换为变量名。
但是在Oracle PL/SQL中，这是一个完全不同的问题。我的CTE作为直接的SQL工作得很好，但是一旦我试图将它们 Package 为PL/SQL，我就会遇到许多问题。据我所知，SELECT现在需要一个INTO，它只保存单个记录的结果。但是，我想要多个值的整个记录集。
我很抱歉，如果我在这里错过了明显的。我在想，我99%的问题是我需要做出的范式转变。
给出下面的例子：

注意：我在这里大大简化了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;

现在，如果我想把它变成PL/SQL，并在顶部传递或预定义变量，这不是一个简单的问题，声明变量，将值弹出到它们中，并将我的硬编码值转换为变量并运行它。* 注意：我知道我可以简单地将硬编码的值更改为变量，如：Department，：StartDate和：Type，但再次强调，我过于简化了示例。
这里有三个问题，我正试图把我的头周围：
1)使用PL/SQL声明变量重写它的最佳方法是什么？现在，CTEs必须进入一些东西。但是我一次处理一行，而不是整个表。因此CTE“A”一次是单行，并且CTE B将仅看到单行，而不是A的所有数据结果，等等。我确实知道我很可能不得不使用CURSORS来遍历记录，这在某种程度上似乎使此过于复杂。
2)输出现在必须使用DBMS_OUTPUT。对于多个记录，我必须使用带FETCH的CURSOR（或FOR…LOOP）。什么事？
3)这与直接SQL的速度和使用的资源？
再次感谢，如果我错过了一些非常明显的东西，我道歉！

oracle

来源：https://stackoverflow.com/questions/26890512/oracle-using-ctes-common-table-expressions-with-pl-sql

3条答案

按热度按时间

mbyulnm01#

首先，这与CTE无关。这个行为与简单的select * from table查询相同。不同之处在于，使用T-SQL时，查询将进入返回给调用者的隐式游标。从Management Studio执行SP时，这很方便。结果集显示在数据窗口中，就好像我们直接执行了查询一样。但这实际上是非标准行为。Oracle具有更标准的行为，可以声明为“任何未定向到游标的查询的结果集必须定向到变量”。当定向到变量时，查询必须仅返回一行。
要复制T-SQL的行为，只需显式声明并返回游标。然后，调用代码从游标中提取整个结果集，但一次只提取一行。您不会得到Sql Developer或PL/SQL Developer将结果集转移到数据显示窗口的便利，但您不能拥有一切。
但是，由于我们通常不会编写SP，只是为了从IDE调用，因此使用Oracle的显式游标比使用SQL Server的隐式游标更容易。只要谷歌一下“oracle return ref cursor to caller”就可以得到很多好的材料。

赞(0）回复(0）举报 2023-06-22

e7arh2l62#

最简单的方法是将其 Package 到隐式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;

无需预定义变量的单行查询。

赞(0）回复(0）举报 2023-06-22

y1aodyip3#

为什么你不能简单地用这种方式来尝试：

select count(department),
count(case when joined date <= '04-May-2021' then 1 else null end) as New joined,
count(case when Employee type='SALARY' then 1 else null end) as New salaried
from EMPLOYEE where department=200.

赞(0）回复(0）举报 2023-06-22

我来回答

ORACLE：在PL/SQL中使用CTE(公共表表达式)

3条答案

相关问题

热门标签

最新问答