ORA-01422:精确获取返回的行数超过RETURNING INTO中请求的行数

Question

我有下面的sql（oracle），它可以从表中删除除100个最新行之外的所有行。

DELETE FROM my_table tab_outer
WHERE tab_outer.rowid IN (
    -- Fetch rowids of rows to delete
    SELECT rid FROM (
        SELECT rownum r, rid FROM (
            SELECT tab.rowid rid
                FROM my_table tab
                ORDER BY tab.created_date DESC
            )
        )
        -- Delete everything but the 100 nesest rows
        WHERE r > 100
)
-- Return newest date that was removed
RETURNING max(tab_outer.created_date) INTO :latestDate

此代码有时会给出一个ORA-01422: exact fetch returns more than requested number of rows，声称latestDate中插入了多行。这怎么可能？RETURNING INTO子句中的聚合函数（max）应确保只返回一行，否？这与显式使用或rowid有什么关系吗（我不知道怎么回事）？

Answer 1

我认为在return子句中使用聚合是不可能的，因为我从未尝试过，文档中也没有提到它，但它确实有效（11gr2）！！

参见PL/SQL:

SQL> CREATE TABLE my_table (created_date DATE);
Table created
SQL> INSERT INTO my_table
  2     (SELECT SYSDATE + ROWNUM FROM dual CONNECT BY LEVEL <= 500);
500 rows inserted
SQL> DECLARE
  2     latestDate DATE;
  3  BEGIN
  4     DELETE FROM my_table tab_outer
  5     WHERE tab_outer.rowid IN (
  6         -- Fetch rowids of rows to delete
  7         SELECT rid FROM (
  8             SELECT rownum r, rid FROM (
  9                 SELECT tab.rowid rid
 10                     FROM my_table tab
 11                     ORDER BY tab.created_date DESC
 12                 )
 13             )
 14             -- Delete everything but the 100 nesest rows
 15             WHERE r > 100
 16     )
 17     -- Return newest date that was removed
 18     RETURNING max(tab_outer.created_date) INTO latestDate;
 19     dbms_output.put_line(latestDate);
 20  END;
 21  /
06/08/14

甚至在SQL*Plus（10.2.0.1.0客户端，11.2.0.3.0数据库）中：

SQL> VARIABLE latestDate VARCHAR2(20);
SQL> DELETE FROM my_table tab_outer
  2  WHERE tab_outer.rowid IN (
  3      -- Fetch rowids of rows to delete
  4      SELECT rid FROM (
  5          SELECT rownum r, rid FROM (
  6              SELECT tab.rowid rid
  7                  FROM my_table tab
  8                  ORDER BY tab.created_date DESC
  9              )
 10          )
 11          -- Delete everything but the 100 nesest rows
 12          WHERE r > 100
 13  )
 14  -- Return newest date that was removed
 15  RETURNING max(tab_outer.created_date) INTO :latestDate;
400 rows deleted.
SQL> select :latestDate from dual;
:LATESTDATE
--------------------------------------------------------------------------------
06/08/14

你能发布一个完整的例子和你的数据库/客户端版本吗。