如何在dup_val_on_index oracle上处理自动增量序列

Question

我有一张甲骨文桌

1. 使用序列的自动增量主键。
2. 唯一键
3. 非唯一字段/

create table FOO (
    ai_id number primary key,
    name varchar(20),
    bar varchar(20)
    CONSTRAINT foo_uk_name UNIQUE (name)
);
create sequence  FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.ai_id from dual;
end;

我有单独的存储过程在表的上方。

create PROCEDURE UPSERT_FOO(
  name_input IN VARCHAR2,
  bar_input IN VARCHAR2
begin
  begin
    insert into FOO ( name, bar )
    values ( name_input, bar_input )
  exception
    when dup_val_on_index then
      update FOO 
      set  bar = bar_input
      where name = name_input
  end;
end;

这很好，但唯一的问题是，无论是更新还是插入(在插入" FOO_seq“之前，FOO_seq会在"FOO_trg”中增加)，“FOO_trg”序列总是会增加。

是否有一种方法来增加序列，只有当有一个插入，而不损害性能？

Answer 1

甲骨文有一个内置的语句来执行一个“向上插入”：

create PROCEDURE UPSERT_FOO(
  name_input IN VARCHAR2,
  bar_input IN VARCHAR2
) as
begin
  merge into foo
  using (
    select name_input as name, bar_input as bar from dual
  ) src
  on (foo.name = src.name)
  when matched then
    update set foo.bar = src.bar
  when not matched then
    insert (name, bar)
    values (src.name, src.bar);
end;
/

插入只发生(因此触发器只触发，增加序列)，如果没有匹配的话。

当然，这不一定要通过一个过程来完成；您可以直接发出一个合并，插入当前必须传递给该过程的name/bar值。

顺便说一句，您的触发器可以稍微简化一下，以便执行任务：

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  :new.ai_id := FOO_seq.nextval;
end;
/

db<>fiddles使用你的原始代码和以上代码。注意最后查询中'b‘的ID；在第一个查询中为5，在第二个查询中只有2个。

当然，序列中的空隙并不重要；它们肯定会增加，并且是唯一的(如果它们不循环的话)，而不是无缝隙的。或者，如果您有缓存并正在使用RAC，则必须按严格顺序发出。不过，您的方法可能会无缘无故地浪费大量的值，而且它也不需要那么复杂。