sql - 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: group function is not allowed here

db<>fiddle demo

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

编辑:

请注意， COUNT(DISTINCT ...)仅是示例。 SUM(col)/SUM(DISTINCT col)和支持 DISTINCT关键字的任何聚合函数的行为相同。

SUM(val) vs SUM(DISTINCT val)

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(用于插入，更新，删除)和dynamic_returning_clause(用于立即执行)。我们对DML感兴趣。


因此，对于10g，有一个单行表达式，根据文档，该表达式为 Expression，它返回表的单行。执行该语句后可以得出DML语句必须影响单行还是单行这是一个微妙的问题(例如，使用聚合函数)。我假设这个想法是在DML操作影响单行(而不是bulk collect into)时使用这种语法。不使用会返回受影响行的单行的聚合函数。

因此，返回in子句中的聚合函数没有明确记录。而且，对于11g，仅在返回关键字后可能会出现一个列名，因此即使实际上像abs(column_name)之类的表达式也不能不提及gregation_function(column_name)。

因此，严格来说，没有记录具有聚合功能的功能，尤其是对于11g，12c，18c，您不能依赖它。

相反，您可以使用“大量收集到”(和set运算符来获取不同的元素集)

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 function is not allowed here

不仅像这个例子中的“不允许有区别”

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