Oracle-RETURNING与聚合函数结合 [英] Oracle - RETURNING combined with aggregate functions

问题描述

Oracle支持RETURNING子句，该子句可能非常有用.

Oracle supports RETURNING clause which could be very useful.

例如数据:

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

l_cnt: 4

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

It supports 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

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

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

Unfortunately when combined with DISTINCT keyword I get an error:

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<>小提琴演示

db<>fiddle demo

问题是为什么不允许带有DISTINCT的聚合函数? 我正在寻找官方消息的答案.

The question is why aggregate functions with DISTINCT are not allowed? I am looking for an answer drawing from official sources.

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

Please note that COUNT(DISTINCT ...) was only an example. Same behavior is for SUM(col)/SUM(DISTINCT col) and any aggregate function that supports DISTINCT keyword.

SUM(val)与SUM(DISTINCT val)

SUM(val) vs SUM(DISTINCT val)

推荐答案

首先，文档和实际功能有点不同步，因此官方资源"将无法阐明细节.

First of all, documentation and actual functionality is a bit out of sync so "official sources" will not shed a light on the details.

10g R2的语法图( https://docs .oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm )如下

Syntactic diagram for 10g R2 (https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm) is below

在11g中( https://docs.oracle. com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm )分为两部分:static_returning_clause(用于插入，更新，删除)和dynamic_returning_clause(用于立即执行).我们对DML感兴趣.

In 11g (https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm) this was split into two: static_returning_clause (for insert, update, delete) and dynamic_returning_clause (for execute immediate). We are interested in the one for DML.

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

So for 10g there was a single row expression which according to documentation is Expression that returns a single row of a table. It's a subtle question whether DML statement must affect a single row or single row can be derived after execution of the statement (say, by using aggregate functions). I assume the idea was to use this syntax when DML operation affects single row (as opposed to bulk collect into); not using aggregate functions which return single row for affected rows.

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

So aggregate functions in returning into clause are not documented clearly. Moreover, for 11g just a column name may appear after returning keyword, so even expression like abs(column_name) is not allowed not to mention aggregate_function(column_name), even though in reality it works.

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

So, strictly speaking, this functionality with aggregate functions is not documented, especially for 11g, 12c, 18c and you cannot rely on it.

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

Instead you can use "bulk collect into" (and set operator to get distinct set of the elements)

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.

还要注意错误消息.它清楚地说

Pay also attention to error message. It clearly says

ORA-00934:此处不允许使用组功能

不只是像这个例子中那样不允许有区别"

Not just "distinct is not allowed" like in this example

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

这篇关于Oracle-RETURNING与聚合函数结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！