Oracle 列表 ..组内 (ORDER BY ..) 在更新中被忽略。返回劳塞

考虑这个脚本 (我也把它放在Live SQL:https://livesql.oracle.com/apex/livesql/s/hbfb7x0c47ubwjpygs3klr3wj)

CREATE TABLE t ( 
  id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, 
  category NUMBER(10) NOT NULL, 
  counter NUMBER(10), 
  text VARCHAR2(10) NOT NULL 
);

INSERT INTO t (category, text) 
SELECT dbms_random.value(1, 10), dbms_random.string('a', 10) 
FROM dual 
CONNECT BY level <= 100;

DECLARE 
  v_text VARCHAR2(2000); 
  v_updated PLS_INTEGER := 0; 
BEGIN 
  UPDATE t 
  SET counter = nvl(counter, 0) + 1 
  WHERE category = 1 
  RETURNING 
    listagg (text, ', ') WITHIN GROUP (ORDER BY text DESC), 
    count(*) 
  INTO 
    v_text, 
    v_updated; 
     
  COMMIT; 
  dbms_output.put_line('Rows updated: ' || v_updated); 
  dbms_output.put_line('Returned:     ' || v_text); 
END; 

当我运行它，我得到这样的东西:

Rows updated: 8
Returned: EHjMWhyYYB, NeXOeOUOtl, eCKQeVNgii, vdfoaOEDbS, WnfPUiTNcZ, ciBYUHUgEe, ccZVEgaVAm, sPyXHepzae

无论我在inter GROUP (ORDER BY ..) 子句中放入什么，排序都被忽略并且似乎是随机的。这是bug还是已知限制？有变通办法吗？

我的猜测是一个错误 (我会记录下来)。

只要它被实现了，返回一直是关于简单的表达式或 “简单” 的聚合 (我使用术语 “简单” 没有什么意味着排序)，例如，我们不允许在聚合等。

因此，我愿意假设我们从未在返回子句中实现过 * ordering * 操作，而没有意识到LISTAGG是第一个需要聚合和顺序的聚合。

我对您的演示进行了一些修改-看起来就像在表中遇到的那样简单地汇总了，例如

SQL> CREATE TABLE t (
  2    id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
  3    category NUMBER(10) NOT NULL,
  4    counter NUMBER(10),
  5    text VARCHAR2(10) NOT NULL
  6  );

Table created.

SQL>
SQL> INSERT INTO t (category, text)
  2  SELECT mod(rownum,3), chr(64+rownum)
  3  FROM dual
  4  CONNECT BY level <= 10;

10 rows created.

SQL>
SQL> select * from t;

        ID   CATEGORY    COUNTER TEXT
---------- ---------- ---------- ----------
         1          1            A
         2          2            B
         3          0            C
         4          1            D
         5          2            E
         6          0            F
         7          1            G
         8          2            H
         9          0            I
        10          1            J

10 rows selected.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_text VARCHAR2(2000);
  3    v_updated PLS_INTEGER := 0;
  4  BEGIN
  5    UPDATE t
  6    SET counter = nvl(counter, 0) + 1
  7    WHERE category = 1
  8    RETURNING
  9      listagg (text, ', ') WITHIN GROUP (ORDER BY text ),
 10      count(*)
 11    INTO
 12      v_text,
 13      v_updated;
 14
 15    COMMIT;
 16    dbms_output.put_line('Rows updated: ' || v_updated);
 17    dbms_output.put_line('Returned:     ' || v_text);
 18  END;
 19  /
Rows updated: 4
Returned:     A, D, G, J

PL/SQL procedure successfully completed.

SQL>
SQL> delete t;

10 rows deleted.

SQL> INSERT INTO t (category, text)
  2  SELECT mod(rownum,3), chr(64+rownum)
  3  FROM dual
  4  CONNECT BY level <= 10
  5  order by 2 desc;              <====== changed order

10 rows created.

SQL>
SQL> select * from t;

        ID   CATEGORY    COUNTER TEXT
---------- ---------- ---------- ----------
        11          1            J
        12          0            I
        13          2            H
        14          1            G
        15          0            F
        16          2            E
        17          1            D
        18          0            C
        19          2            B
        20          1            A

10 rows selected.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_text VARCHAR2(2000);
  3    v_updated PLS_INTEGER := 0;
  4  BEGIN
  5    UPDATE t
  6    SET counter = nvl(counter, 0) + 1
  7    WHERE category = 1
  8    RETURNING
  9      listagg (text, ', ') WITHIN GROUP (ORDER BY text ),
 10      count(*)
 11    INTO
 12      v_text,
 13      v_updated;
 14
 15    COMMIT;
 16    dbms_output.put_line('Rows updated: ' || v_updated);
 17    dbms_output.put_line('Returned:     ' || v_text);
 18  END;
 19  /
Rows updated: 4
Returned:     J, G, D, A

PL/SQL procedure successfully completed.

解决方法有点笨拙，因为对行进行排序的责任落在您身上，例如

SQL> delete t;

10 rows deleted.

SQL> INSERT INTO t (category, text)
  2  SELECT mod(rownum,3), chr(64+rownum)
  3  FROM dual
  4  CONNECT BY level <= 10
  5  order by 2 desc;

10 rows created.

SQL>
SQL> select * from t;

        ID   CATEGORY    COUNTER TEXT
---------- ---------- ---------- ----------
        31          1            J
        32          0            I
        33          2            H
        34          1            G
        35          0            F
        36          2            E
        37          1            D
        38          0            C
        39          2            B
        40          1            A

10 rows selected.

SQL>
SQL> set serverout on
SQL> DECLARE
  2    v_text VARCHAR2(2000);
  3    v_updated PLS_INTEGER := 0;
  4
  5    l_list sys.odcivarchar2list := sys.odcivarchar2list();
  6  BEGIN
  7    UPDATE t
  8    SET counter = nvl(counter, 0) + 1
  9    WHERE category = 1
 10    RETURNING text bulk collect into l_list;
 11
 12    select listagg(column_value,',') within group ( order by column_value)
 13    into   v_text
 14    from   table(l_list);
 15
 16    COMMIT;
 17    dbms_output.put_line('Rows updated: ' || l_list.count);
 18    dbms_output.put_line('Returned:     ' || v_text);
 19  END;
 20  /
Rows updated: 4
Returned:     A,D,G,J

PL/SQL procedure successfully completed.