使用序列向两个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来完成。

- Richard A

---

只需执行两个插入操作。第一个使用 nextval，下一个使用 currval 引用生成的 ID。 - user330315

谢谢，a_horse_with_no_name。如果另一个会话在我的currval调用被处理之前调用nextval，那么这是否会存在序列问题的风险？ - Richard A

不。这就是序列的整个目的。由序列生成的值是“会话私有”的。除非序列被配置为循环，否则两个会话永远不会从nextval或currval调用中获得相同的值。 - user330315

啊，太好了，我没意识到它们是会话私有的，我以为它们是全局的。谢谢。 - Richard A

在某种程度上，它们都是“全局的”和“私有的”。调用nextval显然具有“全局”的范围，因为调用nextval的其他会话将获得一个新值。另一方面，currval始终是“私有的”。 - user330315

Answer 1

你可以利用insert all多表插入语法的一个副作用来实现这个目的：

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.

根据限制:

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

显然我在values子句中使用了序列，所以第一句话似乎不太精确；但是您不能在select部分中使用它。（我不确定在所有版本中都可以在values中使用，但文档有点误导，并且自相矛盾）。

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

- Alex Poole

---

谢谢，我没有意识到nextval会返回相同的值。我尝试将其放入select子句中，正如你所说，它没有起作用。 - Richard A

我会采用这种方法，谢谢。我曾尝试使用INSERT ALL，但没有想到可以像那样调用nextval两次。我承认两个单独的INSERT语句也可以工作，但对于我来说，这个更整洁易实现。 - Richard A

Answer 2

您可以使用jobidsequence.currval来获取序列的当前值（在一个会话中，即在您之前调用nextval之后，并在再次调用nextval之前，直到进行提交）。

参见：Oracle管理员手册：管理序列

- arnep

---

commit 对 currval 的值（或可访问性）没有影响。 - user330315

该事务对序列处理没有任何影响。序列值完全忽略事务，仅限于会话范围。 - user330315

从您提供的文档中可以看到：“要使用或引用会话的当前序列值，请引用seq_name.CURRVAL。只有在当前用户会话（在当前事务或先前事务中）中引用了seq_name.NEXTVAL，才能使用CURRVAL。” - Alex Poole

Answer 3

试试这样做：

PROCEDURE ADD_CAMPAIGN (
                          configXML IN CLOB
                        ) IS 
        vn_CAMPAIGNID number;
BEGIN
   vn_CAMPAIGNID := CAMPAIGN_SEQUENCE.NEXTVAL;

  INSERT INTO CAMPAIGN_INFO (campaign_id,name,start_date,period,handset_statu,bscs_service_id)
  VALUES(vn_CAMPAIGNID ,
  XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/name/text()').getStringVal() ,
  TO_DATE(XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/startDate/text()').getStringVal()) ,
  XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/period/text()').getNumberVal() ,
  XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/handsetStatu/text()').getStringVal(),
  XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/bscsServiceID/text()').getNumberVal()

  );
  INSERT INTO PROMO_INFO(CAMPAIGN_ID,NAME) 
    VALUES(vn_CAMPAIGNID,
    XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/promoName/text()').getStringVal());

 INSERT INTO UTILITY_INFO(CAMPAIGN_ID,NAME)
     VALUES(vn_CAMPAIGNID,
     XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/utilityName/text()').getStringVal())  ;

 INSERT   INTO FREE_UNIT_INFO(CAMPAIGN_ID,NAME) 
     VALUES(vn_CAMPAIGNID,
     XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/packageName/text()').getStringVal()) ;

   INSERT INTO HANDSET_INFO(HANDSET_ID,NAME,CAMPAIGN_ID)
     VALUES(HANDSET_SEQUENCE.NEXTVAL,
     XMLTYPE(configXML).EXTRACT('/campaign/campaignInfo/handsetName/text()').getStringVal(),
     vn_CAMPAIGNID);



END ADD_CAMPAIGN;

- Cansu Arslan

Answer 4

更简单的方法...

    begin
      insert into job (
        col1,
        ...
        coln)
      select
        col1,
        ....
        coln
      from
        tableA
    <condition>;

      insert into job2 (
        col1,
        ...
        coln)
      select
        col1,
        ....
        coln
      from
        tableB
    <condition>;

    End;

- Sudheej

---

但是原贴的要求是非PL/SQL解决方案；而且正在插入新数据而不是从现有表数据中选择；您也没有展示如何保留序列号（可能会将其生成为本地变量，或使用returning子句返回到本地变量）。 - Alex Poole