Oracle - ВОЗВРАЩЕНИЕ в сочетании с агрегатными функциями

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 ‹> демонстрация скрипта

Возникает вопрос, почему агрегатные функции с DISTINCT недопустимы? Ищу рисунок ответа из официальных источников.

РЕДАКТИРОВАТЬ:

Обратите внимание, что COUNT(DISTINCT ...) был только примером. То же самое для SUM(col)/SUM(DISTINCT col) и любой агрегатной функции, поддерживающей ключевое слово DISTINCT.

SUM (val) vs SUM (DISTINCT val)


sql oracle aggregate-functions sql-returning
person Lukasz Szozda    schedule 14.09.2018    source источник
comment
Ни ответов, ни комментариев. У меня тоже нет ничего официального, но вот что я думаю: RETURNING - это, на самом деле, SQL% ROWCOUNT замаскированный. Итак, когда вы вставляете, обновляете или - как в вашем случае - удаляете несколько строк, SQL% ROWCOUNT знает, сколько строк вы удалили всего. Он не (не может?) Подсчитывать различные значения. Как я уже сказал: это то, что я думаю, и это может быть очень неправильно.   -  person Littlefoot    schedule 15.09.2018
comment
@Littlefoot На самом деле RETURNING - это намного больше, чем SQL% ROWCOUNT, он может возвращать одно / несколько значений / выполнять агрегацию / возвращать коллекции / .... В моем случае одна функция agg, такая как COUNT(val), работает нормально, но COUNT(DISTINCT val) обрабатывается по-другому db ‹› демоверсия скрипки   -  person Lukasz Szozda    schedule 15.09.2018
comment
Да, я согласен. Возможно, мне следовало сказать, что у меня сложилось впечатление, что returning count(*) действует как sql%rowcount.   -  person Littlefoot    schedule 15.09.2018
comment
@Littlefoot Вот почему я ввел примеры сMIN/MAX/LISTAGG. В любом случае SUM(id) будет работать, но SUM(DISTINCT id) не будет. Итак, корень проблемы - DISTINCT.   -  person Lukasz Szozda    schedule 15.09.2018
comment
Причина может заключаться в том, что count(distinct ...) равно T = O (n log n), тогда как остальная часть потенциальной агрегации functions - это T = O (n). Другими словами, вам запрещено запрашивать дополнительный вид данных, возвращаемых вашим запросом. Другими словами, движок PL / SQL не хочет хранить результаты запроса где-либо в памяти, он только хочет прокрутить их в конвейерном режиме и вычислить ваш агрегат. Однако мне также не известны какие-либо официальные источники этого конкретного поведения.   -  person peter.hrasko.sk    schedule 17.09.2018
comment
@ nop77svk Я мог почти согласиться с таким объяснением, но я мог бы написать Returning (SELISTIN COUNT ) ОТ т) ВО ..... Таким образом, возврат с subquery позволит запустить произвольный агрегат, но, к сожалению, данные берутся из моментального снимка непосредственно перед операцией DELETE.   -  person Lukasz Szozda    schedule 17.09.2018

Ответы (2)


arrow_upward
3
arrow_downward

Прежде всего, документация и фактическая функциональность немного не синхронизированы, поэтому "официальные источники" не проливают свет на детали.

Синтаксическая диаграмма для 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 было одно выражение строки, которое, согласно документации, является выражением, возвращающим единственную строку таблицы. Это тонкий вопрос, должен ли оператор DML влиять на одну строку или одна строка может быть получена после выполнения оператора (скажем, с помощью агрегатных функций). Я предполагаю, что идея заключалась в том, чтобы использовать этот синтаксис, когда операция DML затрагивает одну строку (в отличие от bulk collect into); не использовать агрегатные функции, которые возвращают одну строку для затронутых строк.

Таким образом, агрегатные функции при возврате в предложение четко не документированы. Более того, для 11g после возврата ключевого слова может появиться только имя столбца, поэтому даже в выражении вроде abs (имя_столбца) нельзя не упоминать агрегатную функцию (имя_столбца), хотя на самом деле оно работает.

Итак, строго говоря, эта функциональность с агрегатными функциями не документирована, особенно для 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: Функция группа здесь не разрешена

Не просто «отдельный не допускается», как в этом примере

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
person Dr Y Wit    schedule 18.09.2018

arrow_upward
0
arrow_downward

Основная причина в том, что SQL нельзя компоновать. К. Дж. Дэйт показал, по крайней мере, в классе 2009 года, который я посещал в Северном Техасе, что SQL не компонуем. Поскольку это невозможно составить, некоторые вещи не предоставляются бесплатно. А под бесплатным я имею в виду, даже не задумываясь. Но люди из серверных технологий довольно умны, и я уверен, что те, кто руководил проектом «добавить возвращающуюся функцию», сознательно определили, где провести черту. Очевидно, они решили, что не будут «полностью» писать синтаксический анализатор. Я подозреваю, что это потому, что они знали, что если они займут позицию 100% поддерживаемости, то, как только какая-то другая часть SQL будет улучшена, им также придется потратить время на улучшение других частей языка.

Я восхищаюсь ST за то, насколько быстро выполняется их анализатор SQL и как редко он дает неверные результаты. Но мне интересно, хотя и не так страстно, как мистер Дат, насколько лучше мог бы быть мир, если бы доминирующий язык запросов был хотя бы компонуемым.

person Jeff Holt    schedule 17.09.2018
comment
У вас есть ссылка на материалы о том, что SQL не может быть составлен? Уверен, это будет интересная лекция. - person Lukasz Szozda; 17.09.2018