Oracle触发器创建一个自动编号 [英] Oracle trigger to create an autonumber

问题描述

我以前从未在Oracle中创建触发器，所以我正在寻找一些方向.

I have never created a trigger in Oracle before so I am looking for some direction.

我想创建一个触发器，如果​​插入语句中的ID不为ID时，则使ID递增1.

I would like to create a trigger that increments an ID by one if the ID isnt in the insert statement.

该ID应该从10000开始，并且当插入一条记录时，下一个ID应该是10001.如果insert语句包含一个ID，则它应该覆盖自动递增.

The ID should start at 10000, and when a record is inserted the next ID should be 10001. If the insert statement contains a ID, it should override the auto increment.

即

insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')

应如下所示:

名字的姓氏

Micahel Jordan 10000

Micahel Jordan 10000

拉里·伯德(Larry Bird)10001

Larry Bird 10001

insert into t1 (firstname, lastname, id) values ('Scottie','Pippen',50000)

应如下所示:

名字的姓氏

Micahel Jordan 10000

Micahel Jordan 10000

拉里·伯德(Larry Bird)10001

Larry Bird 10001

Scottie Pippen 50000

Scottie Pippen 50000

推荐答案

类似的方法适用于11g

Something like this will work on 11g

CREATE SEQUENCE t1_id_seq 
  start with 10000 
  increment by 1;

CREATE TRIGGER trigger_name
  BEFORE INSERT ON t1
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.id IS NULL )
  THEN
    :new.id := t1_id_seq.nextval;
  END IF;
END;

如果您使用的是较早版本，则需要执行SELECT INTO才能从序列中获取下一个值

If you're on an earlier version, you'll need to do a SELECT INTO to get the next value from the sequence

CREATE TRIGGER trigger_name
  BEFORE INSERT ON t1
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.id IS NULL )
  THEN
    SELECT t1_id_seq.nextval
      INTO :new.id
      FROM dual;
  END IF;
END;

请注意，Oracle序列并非没有间隙.因此，出于各种原因，有可能会跳过特定的值.如果在几分钟，几小时或几天之后完成，则第一个插入的ID可能为10000，第二个插入的ID可能为10020.

Be aware that Oracle sequences are not gap-free. So it is entirely possible that particular values will be skipped for a variety of reasons. Your first insert may have an ID of 10000 and the second may have an ID of 10020 if it's done minutes, hours, or days later.

此外，请注意，Oracle不像MySQL那样支持在VALUES子句中指定多个行.所以，而不是

Additionally, be aware that Oracle does not support specifying multiple rows in the VALUES clause as MySQL does. So rather than

insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')

您需要两个单独的INSERT语句

you'd need two separate INSERT statements

insert into t1 (firstname, lastname) values ('Michael','Jordan');
insert into t1 (firstname, lastname) values ('Larry','Bird');

这篇关于Oracle触发器创建一个自动编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！