Oracle是否有类似于SQL Server的OUTPUT INSERTED.*的等效选项？

Question

在 SQL Server 中，您可以执行以下操作：

INSERT INTO some_table (...) OUTPUT INSERTED.*
VALUES (...)

你可以插入任意列/值集合并获取这些结果。在 Oracle 中有什么方法可以做到这一点吗？

我能想到的最好方法是：

INSERT INTO some_table (...)
VALUES (...)
RETURNING ROWID INTO :out_rowid

...使用 :out_rowid 作为绑定变量。然后使用第二个查询，类似于这样：

SELECT *
FROM some_table
WHERE ROWID = :rowid

...但是这不完全相同，因为它返回列中的所有内容，而不仅仅是我插入的列。

有没有更好的方法可以在不使用大量 PL/SQL 的情况下完成，并且最好只使用一个查询？

- Jason Baker

---

如果您只对列（而不是行数据）感兴趣...... - 您如何推导出（...）？在那个时候，您肯定知道插入中引用了哪些列？ - Jeffrey Kemp

我刚刚阅读了关于OUTPUT INSERTED的内容（http://msdn.microsoft.com/en-us/library/ms177564.aspx）。显然，它允许您选择在任何表触发器运行之前或之后返回的行数据。Oracle的RETURNING子句不支持此功能-它只能在触发器有机会更改数据后提供数据。 - Jeffrey Kemp

@Jeffrey Kemp - 我想知道那些列是什么。不过，数据库应该也知道 :-) - Jason Baker

Answer 1

也许我没有理解问题，但这样做不就可以了吗？（你必须知道你想要什么返回）

INSERT INTO some_table (...)
VALUES (...)
RETURNING some_column_a, some_column_b, some_column_c,  ...  INTO :out_a, :out_b, :out_c, ...

@Vincent，将bulk collect返回到多行插入中，只有在与forall一起使用时才有效（换句话说，如果您从集合中插入，则可以将“结果”检索到另一个集合中）

- Michal Pravda

Answer 2

RETURNING子句支持BULK COLLECT INTO语法。请参考（10g）：

SQL> CREATE TABLE t (ID NUMBER);

Table created
SQL> INSERT INTO t (SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 5);

5 rows inserted
SQL> DECLARE
  2     TYPE tab_rowid IS TABLE OF ROWID;
  3     l_r tab_rowid;
  4  BEGIN
  5     UPDATE t SET ID = ID * 2
  6      RETURNING ROWID BULK COLLECT INTO l_r;
  7     FOR i IN 1 .. l_r.count LOOP
  8        dbms_output.put_line(l_r(i));
  9     END LOOP;
 10  END;
 11  /

AADcriAALAAAAdgAAA
AADcriAALAAAAdgAAB
AADcriAALAAAAdgAAC
AADcriAALAAAAdgAAD
AADcriAALAAAAdgAAE

我的版本（10.2.0.3.0）支持多行 UPDATE 和 DELETE，但不支持 INSERT：

SQL> DECLARE
  2     TYPE tab_rowid IS TABLE OF ROWID;
  3     l_r tab_rowid;
  4  BEGIN
  5     INSERT INTO t (SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 5)
  6      RETURNING ROWID BULK COLLECT INTO l_r;
  7     FOR i IN 1 .. l_r.count LOOP
  8        dbms_output.put_line(l_r(i));
  9     END LOOP;
 10  END;
 11  /

ORA-06550: line 7, column 5:
PL/SQL: ORA-00933: SQL command not properly ended

也许你有一个更新的版本（11g？）并且支持使用BULK COLLECT INTO进行多行INSERT操作？

- Vincent Malgrat

---

我不太担心行数，而是列数。行数几乎总是为1，而列数则是可变的。 - Jason Baker

@Jason：如果列数在编译时是可变的/未知的，我认为你将不得不使用dbms_sql。 - Vincent Malgrat