我找不到以下伪sql的正确语法：
INSERT INTO some_table
(column1,column2)
SELECT col1_value,col2_value
FROM other_table
WHERE ...
RETURNING id
INTO local_var;
我想插入一些含有子查询值的东西.
插入后,我需要新的生成的ID.
什么oracle文件说：
@L_403_0@
好吧,我认为只有使用值子句才是可能的
有办法吗
您不能使用INSERT中的RETURNING BULK COLLECT.
这种方法可以用于更新和删除howeveer：
原文链接：https://www.f2er.com/oracle/205532.html这种方法可以用于更新和删除howeveer：
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;
您可以使用几个额外的步骤(使用TREAT进行FORALL INSERT)
如本文所述：
Ť
利用他们创建的示例并将其应用于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;
/
