SQL PLSQL 插入子查询和返回子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5325033/
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
PLSQL Insert into with subquery and returning clause
提问by Stephan Schielke
I can't figure out the correct syntax for the following pseudo-sql:
我无法弄清楚以下伪 sql 的正确语法：
INSERT INTO some_table
(column1,
column2)
SELECT col1_value,
col2_value
FROM other_table
WHERE ...
RETURNING id
INTO local_var;
I would like to insert something with the values of a subquery. After inserting I need the new generated id.
我想插入带有子查询值的内容。插入后我需要新生成的 id。
Heres what oracle doc says:
这是oracle doc所说的：
OK i think it is not possible only with the values clause... Is there an alternative?
好的，我认为仅使用 values 条款是不可能的......还有其他选择吗？
采纳答案by Harrison
You cannot use the RETURNING BULK COLLECT from an INSERT. This methodology can work with updates and deletes howeveer:
不能从 INSERT 使用 RETURNING BULK COLLECT。这种方法可以用于更新和删除，但是：
create table test2(aa number)
/
insert into test2(aa)
select level
from dual
connect by level<100
/
set serveroutput on
declare
TYPE t_Numbers IS TABLE OF test2.aa%TYPE
INDEX BY BINARY_INTEGER;
v_Numbers t_Numbers;
v_count number;
begin
update test2
set aa = aa+1
returning aa bulk collect into v_Numbers;
for v_count in 1..v_Numbers.count loop
dbms_output.put_line('v_Numbers := ' || v_Numbers(v_count));
end loop;
end;
You can get it to work with a few extra steps (doing a FORALL INSERT utilizing TREAT) as described in this article:
如本文所述，您可以通过一些额外的步骤（使用 TREAT 执行 FORALL INSERT）来使其工作：
T
吨
to utilize the example they create and apply it to test2 test table
利用他们创建的示例并将其应用于 test2 测试表
CREATE or replace TYPE ot AS OBJECT
( aa number);
/
CREATE TYPE ntt AS TABLE OF ot;
/
set serveroutput on
DECLARE
nt_passed_in ntt;
nt_to_return ntt;
FUNCTION pretend_parameter RETURN ntt IS
nt ntt;
BEGIN
SELECT ot(level) BULK COLLECT INTO nt
FROM dual
CONNECT BY level <= 5;
RETURN nt;
END pretend_parameter;
BEGIN
nt_passed_in := pretend_parameter();
FORALL i IN 1 .. nt_passed_in.COUNT
INSERT INTO test2(aa)
VALUES
( TREAT(nt_passed_in(i) AS ot).aa
)
RETURNING ot(aa)
BULK COLLECT INTO nt_to_return;
FOR i IN 1 .. nt_to_return.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
'Sequence value = [' || TO_CHAR(nt_to_return(i).aa) || ']'
);
END LOOP;
END;
/
回答by Tony Andrews
Unfortunately that's not possible. RETURNING is only available for INSERT...VALUES statements. See this Oracle forum threadfor a discussion of this subject.
不幸的是，这是不可能的。RETURNING 仅适用于 INSERT...VALUES 语句。有关此主题的讨论，请参阅此 Oracle 论坛主题。
回答by Michael Broughton
Because the insert is based on a select, Oracle is assuming that you are permitting a multiple-row insert with that syntax. In that case, look at the multiple row version of the returning clause document as it demonstrates that you need to use BULK COLLECT to retrieve the value from all inserted rows into a collection of results.
由于插入基于选择，因此 Oracle 假设您允许使用该语法进行多行插入。在这种情况下，请查看返回子句文档的多行版本，因为它表明您需要使用 BULK COLLECT 将所有插入行的值检索到结果集合中。
After all, if your insert query creates two rows - which returned value would it put into an single variable?
毕竟，如果您的插入查询创建了两行 - 它会将哪个返回值放入单个变量中？
EDIT - Turns out this doesn't work as I had thought.... darn it!
编辑 - 结果这不像我想的那样工作......该死！
回答by diagonalbatman
This isn't as easy as you may think, and certainly not as easy as it is using MySQL. Oracle doesn't keep track of the last inserts, in a way that you can ping back the result.
这并不像您想象的那么容易，当然也不像使用 MySQL 那样容易。Oracle 不跟踪最后的插入，以一种您可以 ping 回结果的方式。
You will need to work out some other way of doing this, you can do it using ROWID - but this has its pitfalls.
您将需要找出其他一些方法来做到这一点，您可以使用 ROWID 来做到这一点 - 但这有其缺陷。
This link discussed the issue: http://forums.oracle.com/forums/thread.jspa?threadID=352627
这个链接讨论了这个问题：http: //forums.oracle.com/forums/thread.jspa?threadID=352627
