Oracle SQL插入多行并返回某些内容 [英] Oracle SQL inserting multiple rows and returning something

问题描述

在Oracle中，可以通过执行类似的查询来插入多行

In Oracle, one can insert multiple rows by doing a query like

INSERT ALL
   INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
   INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
   INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

并使用准备好的语句，像这样进行单个插入

And with a prepared statement, doing a single insert like this one

BEGIN INSERT 
   INTO mytable (column1, column2, column3) VALUES (null, 'val1.2', 'val1.3')
RETURNING column1 INTO ?; END;

将导致返回column1的值(假设在插入之前有一个触发器为其分配了一个值).

will result in returning column1's value (supposing there is a trigger assigning a value to it before insert).

是否有办法将两者结合起来?意思是，在插入多个值的同时仍通过单个查询返回所有column1值(结果集)?

Is there a way, if possible at all, to combine both? Meaning, inserting multiple values while still returning all the column1 values (a resultset) with a single query?

推荐答案

来自

From the documentation (at least the 10g version), one of the restrictions to the returning clause:

您不能指定 returning_clause用于多表 插入.

You cannot specify the returning_clause for a multitable insert.

这篇关于Oracle SQL插入多行并返回某些内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！