是否有与SQL Server的OUTPUT INSERTED *相当的Oracle? [英] Is there an Oracle equivalent to SQL Server's OUTPUT INSERTED.*?

问题描述

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

In SQL Server, you can do things like this:

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

这样您就可以插入任意组的列/值并将其返回.在Oracle中有什么方法可以做到这一点?

So that you can insert arbitrary sets of columns/values and get those results back. Is there any way to do this in Oracle?

我能想到的最好的方法是:

The best I can come up with is this:

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

...使用:out_rowid作为绑定变量.然后使用第二个查询，如下所示:

...using :out_rowid as a bind variable. And then using a second query like this:

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.

在不使用大量PL/SQL且最好仅使用一个查询的情况下，还有其他更好的方法吗?

Is there any better way to do this without using a lot of PL/SQL and preferably with only one query?

推荐答案

也许我听不懂这个问题，但这不是吗? (您必须知道想要返回什么)

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将批量收集返回到多行插入仅与forall结合使用(换句话说，如果从集合中插入，则可以将结果"检索到另一个结果中)

@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)

这篇关于是否有与SQL Server的OUTPUT INSERTED *相当的Oracle?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！