按顺序插入两个Oracle表 [英] Inserting into two oracle tables with a sequence

问题描述

我在Oracle中有两个表，作业和参考.

我想用从序列中生成的键将新记录插入两个表中.像这样:

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来做到这一点.

解决方案

您可以使用 SQL小提琴.

根据限制:

您不能在多表插入的任何部分中指定序列 陈述.多表插入被视为单个SQL语句. 因此，对NEXTVAL的第一个引用将生成下一个数字， 语句中的所有后续引用都返回相同的数字.

很明显，我在values子句中使用了一个序列，因此第一句话似乎不太准确.但是您不能在select部分中使用它. (我不确定100％是否可以在所有版本的values中使用它，但是该文档在任何情况下都会产生误导，并且相互矛盾.)

所以我利用了这样一个事实，因为它是一条语句，所以对nextval的两个引用都具有相同的数字，如第三句所说，因此在两个表中使用相同的序列值. /p>

I have two tables in Oracle, job and reference.

I want to insert a new record into both tables, with a key generated from a sequence. Something like:

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

This, of course, leads to:

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

Is there a way of doing this without using PL/SQL? I'd dearly love to do it using just SQL.

解决方案

You can use a side-effect of the insert all multi-table insert syntax for this:

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.

From the restrictions:

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.

Clearly I am using a sequence in the values clause, so the first sentence doesn't seem quite accurate; but you can't use it in the select part. (I'm not 100% sure if it can be used in the values in all versions, but the documentation is a little misleading in any case, and contradicts itself).

So I'm taking advantage of the fact that because it is a single statement, the two references to nextval get the same number, as the third sentence says, so the same sequence value is used in both tables.

这篇关于按顺序插入两个Oracle表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！