是否有相当于 SQL Server 的 OUTPUT INSERTED.* 的 Oracle？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1074843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there an Oracle equivalent to SQL Server's OUTPUT INSERTED.*?
提问by Jason Baker
In SQL Server, you can do things like this:
在 SQL Server 中，您可以执行以下操作：
INSERT INTO some_table (...) OUTPUT INSERTED.*
VALUES (...)
So that you can insert arbitrary sets of columns/values and get those results back. Is there any way to do this in Oracle?
这样您就可以插入任意组的列/值并返回这些结果。有没有办法在 Oracle 中做到这一点？
The best I can come up with is this:
我能想到的最好的是：
INSERT INTO some_table (...)
VALUES (...)
RETURNING ROWID INTO :out_rowid
...using :out_rowid as a bind variable. And then using a second query like this:
...使用 :out_rowid 作为绑定变量。然后使用这样的第二个查询：
SELECT *
FROM some_table
WHERE ROWID = :rowid
...but this isn't quite the same as it returns everything within the column, not just the columns I inserted.
...但这并不完全相同，因为它返回列中的所有内容，而不仅仅是我插入的列。
Is there any better way to do this without using a lot of PL/SQL and preferably with only one query?
有没有更好的方法来做到这一点而不使用大量的 PL/SQL，最好只使用一个查询？
回答by Michal Pravda
Maybe I don't understand the question, but wouldn't this do it? (you must know what you want back)
也许我不明白这个问题，但这不是吗？（你必须知道你想要什么回来）
INSERT INTO some_table (...)
VALUES (...)
RETURNING some_column_a, some_column_b, some_column_c, ... INTO :out_a, :out_b, :out_c, ...
@Vincent returning bulk collect into for multi-row insert works only in conjunction with forall (in another words if you insert from collection you can retrieve "results" into another)
@Vincent 返回批量收集以进行多行插入仅与 forall 结合使用（换句话说，如果您从集合中插入，您可以将“结果”检索到另一个中）
回答by Vincent Malgrat
The RETURNINGclause supports the BULK COLLECT INTOsynthax. Consider (10g):
该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
It works with multi-row UPDATEand DELETEwith my version (10.2.0.3.0) but NOTwith INSERT:
它的工作原理与多行UPDATE和DELETE我的版本（10.2.0.3.0），但不具有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
Maybe you have a more recent version (11g?) and the BULK COLLECT INTOis supported for multi-row INSERTs ?
也许您有更新的版本（11g？）并且BULK COLLECT INTO支持多行INSERTs ？
