sql - 用序列插入两个oracle表

Question

我在 Oracle 中有两个表，job 和 reference。

我想在两个表中插入一条新记录，并使用从序列生成的键。就像是:

insert into (
select j.jobid, j.fileid, j.jobname, r.reffileid 
from job j
inner join reference r on j.jobid=r.jobid)
values (jobidsequence.nextval, 4660, 'name', 4391);

这当然会导致:

ORA-01776: cannot modify more than one base table through a join view

有没有不使用 PL/SQL 的方法来做到这一点？我非常喜欢只使用 SQL 来完成它。

Answer 1

您可以使用 insert all multi-table insert 的副作用语法:

insert all
into job (jobid, fileid, jobname)
values (jobidsequence.nextval, fileid, jobname)
into reference (jobid, reffileid)
values (jobidsequence.nextval, reffileid)
select  4660 as fileid, 'name' as jobname, 4391 as reffileid
from dual;

2 rows inserted.

select * from job;

     JOBID     FILEID JOBNAME  
---------- ---------- ----------
        42       4660 name       

select * from reference;

     JOBID  REFFILEID
---------- ----------
        42       4391 

SQL Fiddle .

从限制:

You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

显然我在 values 中使用了一个序列从句，所以第一句话似乎不太准确；但你不能在 select 中使用它部分。 (我不是 100% 确定它是否可以在所有版本的 values 中使用，但文档在任何情况下都有点误导，并且自相矛盾)。

所以我利用了这样一个事实，因为它是一个单一的语句，对 nextval 的两个引用。得到相同的数字，如第三句所说，所以在两个表中使用相同的序列值。