Oracle - 结合聚合函数使用 RETURNING

Question

Oracle支持RETURNING子句，它非常有用。

例如，对于数据：

CREATE TABLE t(Id INT, Val varchar2(50));

INSERT INTO t(Id, Val)
SELECT 10,'a' FROM dual
UNION ALL SELECT 20,'b' FROM dual
UNION ALL SELECT 30,'a' FROM dual
UNION ALL SELECT 40,'b' FROM dual;

查询：

DECLARE
   l_cnt INT;
BEGIN
   DELETE FROM t RETURNING COUNT(*) INTO l_cnt;
   DBMS_OUTPUT.put_line('l_cnt: ' || l_cnt);
END;

l_cnt: 4

它支持MIN/MAX/AVG/SUM/LISTAGG：

DECLARE
   l_max INT;
   l_min INT;
   l_str VARCHAR2(100);
BEGIN
   DELETE FROM t 
   RETURNING MAX(id), MIN(id), LISTAGG(id, ',') WITHIN GROUP(ORDER BY id) 
   INTO l_max, l_min, l_str;
   DBMS_OUTPUT.put_line('l_max:'||l_max||' l_min:'||l_min||' l_str:'|| l_str);
END;

l_max:40 l_min:10 l_str:10,20,30,40

不幸的是，当与 DISTINCT 关键字结合使用时，会出现错误：

DECLARE
   l_distinct_cnt INT;
BEGIN
   DELETE FROM t 
   RETURNING COUNT(DISTINCT val) INTO l_distinct_cnt ;
   DBMS_OUTPUT.put_line('l_distinct_cnt:' || l_distinct_cnt );
END;

ORA-00934: 不允许在此使用群组函数

db<>fiddle演示

问题是为什么不允许使用带有DISTINCT的聚合函数？ 我正在寻找从官方来源得出的答案。

---

编辑：

请注意，COUNT(DISTINCT ...) 仅是一个示例。对于支持 DISTINCT 关键字的任何聚合函数，都适用相同的行为，例如 SUM(col)/SUM(DISTINCT col)。

SUM(val) vs SUM(DISTINCT val)

- Lukasz Szozda

---

我没有任何官方的东西，但是这是我的想法：RETURNING 实际上是 SQL%ROWCOUNT 的伪装。所以，当你插入、更新或者 - 就像在你的情况下 - 删除一些行时，SQL%ROWCOUNT 知道你总共删除了多少行。它不能计算不同的值。正如我所说的：这只是我的想法，可能非常错误。 - Littlefoot

@Littlefoot，实际上，RETURNING比SQL%ROWCOUNT更强大，它可以返回单个/多个值/执行聚合操作/返回集合等等。我的问题是为什么一个聚合函数如COUNT(val)可以正常工作，但COUNT(DISTINCT val)被视为不同的db<>fiddle演示。 - Lukasz Szozda

是的，我同意。也许我应该说，我的印象是returning count(*)的作用类似于sql%rowcount。 - Littlefoot

@Littlefoot，这就是为什么我介绍了带有MIN/MAX/LISTAGG的示例。无论如何，SUM(id)会起作用，但是SUM(DISTINCT id)不会起作用。因此问题的根源在于DISTINCT。 - Lukasz Szozda

可能的原因是 count(distinct ...) 的时间复杂度为 T = O(n log n )，而其他潜在的聚合函数则为 T = O(n)。换句话说，您不能要求从查询返回的数据进行额外的排序。换句话说，PL/SQL 引擎不想在内存中存储查询结果，它只想以管道方式循环遍历它们并计算您的聚合。但是，我也不知道这种特定行为的任何官方来源。 - peter.hrasko.sk

@nop77svk 我几乎同意这样的解释，但我可以写 Returning (SELECT COUNT(DISTINCT id) FROM t) INTO ....。因此，使用 子查询 返回将允许运行任意聚合函数，但不幸的是数据是在执行“DELETE”操作之前从快照中获取的。 - Lukasz Szozda

Answer 1

首先，文档和实际功能有些不同步，因此“官方来源”无法详细解释。

10g R2的句法图表（https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm）如下所示

在11g中(https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm)，此功能被分为两部分：static_returning_clause（用于insert、update、delete）和dynamic_returning_clause（用于execute immediate）。我们感兴趣的是DML的那一个。 因此，在10g中，有一个单行表达式，根据文档说明是返回表的一行的表达式。这是一个微妙的问题，即DML语句是否必须影响单个行或者可以在语句执行后派生出单个行（例如，使用聚合函数）。我假设这个语法的想法是在DML操作影响单个行时使用它（而不是bulk collect into），不使用返回受影响行的单个行的聚合函数。

聚合函数在返回到子句中的使用并没有得到明确的文档说明。此外，在11g版本中，仅允许在returning关键字后出现列名，因此即使是像abs(column_name)这样的表达式也是不允许的，更不用说aggregate_function(column_name)了，尽管在实际操作中是可以正常工作的。

严格来说，特别是对于11g、12c和18c版本，这种带有聚合函数的功能并没有得到文档说明，因此您不能依赖它。

相反，您可以使用“bulk collect into”（并设置运算符以获取元素的不同集合）。

SQL> create type str_tab as table of varchar2(4000)
  2  /

Type created.

SQL> set serveroutput on
SQL> declare
  2    i int;
  3    a str_tab;
  4  begin
  5    delete from t returning val bulk collect into a;
  6    dbms_output.put_line('cnt all ' || a.count || ' cnt distinct ' || set(a).count);
  7    rollback;
  8  end;
  9  /
cnt all 4 cnt distinct 2

PL/SQL procedure successfully completed.

请注意错误信息。它清楚地说明了：

ORA-00934: group函数不允许在这里使用。

不仅是像这个示例中的“distinct不允许”。

SQL> select listagg(distinct val) within group (order by val) str from t;
select listagg(distinct val) within group (order by val) str from t
       *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

- Dr Y Wit

Answer 2

主要是因为SQL不可组合。C. J. Date 在我参加的2009年北德克萨斯州课程中展示了SQL不可组合性。由于它不可组合，某些东西并不是免费的。所以说“免费”，我的意思是即使没有想过也能做到。但是服务器技术部门的人非常聪明，我相信管理“返回功能”的项目的人有意识地决定了划界限的位置。他们显然决定不会“完全”编写解析器。我猜这是因为他们知道如果他们采取100％的支持立场，那么只要 SQL 的其他部分得到增强，他们也必须花时间增强语言的其他部分。

我确实钦佩 ST 的SQL解析器能够快速执行并且很少产生错误结果。但是我想，虽然不像Date先生那样狂热，如果主导查询语言至少具有组合能力，世界会更好吧。

- Jeff Holt

---

你有关于 SQL 不可组合部分的材料链接吗？我相信这将是一场有趣的讲座。 - Lukasz Szozda