Oracle - 插入自增ID的新行

Question

我有一个工作队列表，其中有一个workid列。workid列的值会自动递增。是否有一种方法可以在后端运行查询来插入新行，并使workid列自动递增？
当我尝试插入null时，它会抛出ORA01400错误 - 无法将null插入到workid中。

insert into WORKQUEUE  (facilitycode,workaction,description) values ('J', 'II',    'TESTVALUES')

我到目前为止尝试了什么 - 我尝试查看表细节，没有看到任何自增。表脚本如下：

"WORKID" NUMBER NOT NULL ENABLE,

数据库: Oracle 10g

一些现有数据的截图。

---

回答：

我要感谢每一个人的帮助。今天是一个非常好的学习体验，没有你们的支持，我无法完成。底线是，我试图向已经有序列和触发器的表中插入一行。我所要做的就是找到适合我的问题的正确序列，并将该序列调用到我的查询中。

你们提供给我的链接帮助我查找这些序列，并找到适合此workid列的序列。感谢你们所有人，我向大家竖起了拇指，今天我能够应对另一个难题并帮助患者护理向前迈进！

- Shaji

---

可能是重复的问题：Oracle - 修改现有表以自动递增列 - Li0liQ

@Li0liQ - 不，我并不想修改表格。我只是想按照现有的方式在现有表格中插入一行。 - Shaji

Answer 1

这是一种简单的方法，无需任何触发器或序列：

insert into WORKQUEUE (ID, facilitycode, workaction, description)
  values ((select max(ID)+1 from WORKQUEUE), 'J', 'II', 'TESTVALUES')

对我来说有效，但我猜对于一个空表格无效。

- Zsolt Sky

---

非空表格非常适合，使用这种方法不需要处理触发器或序列。 - Siick

这对我来说非常好，因为我无法创建一个序列。我将我的第一个插入更改为将ID设置为1，它完美地工作了。 - wheeleruniverse

对于空表，您可以使用nvl（max（id），0）+1将null值转换为您传递的数字。 - fedeb

Answer 2

为了获得自增数字，你需要在Oracle中使用序列。 （参见这里和这里）。

CREATE SEQUENCE my_seq;

SELECT my_seq.NEXTVAL FROM DUAL; -- to get the next value

-- use in a trigger for your table demo
CREATE OR REPLACE TRIGGER demo_increment 
BEFORE INSERT ON demo
FOR EACH ROW

BEGIN
  SELECT my_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

- Johnny Graber

---

请问您能回答我的问题吗？我没有足够的声望来回答我的问题并关闭它。请复制并粘贴以下感激之词：“谢谢大家”。有关我的解决方案，请查看我的问题下方。 - Shaji

Answer 3

Oracle中没有内置的auto_increment。

您需要使用sequences和triggers。

在这里阅读如何正确地执行此操作。（逐步操作“在Oracle中创建自动增量列”的说明）

- SlavaNov

Answer 4

ELXAN@DB1> 创建表cedvel，其中包含两列：id和ad。
Table created.
ELXAN@DB1> 向cedvel表添加主键约束pk_ad，该约束的主键为id。
Table altered.
ELXAN@DB1> 创建序列test_seq，从1开始以1递增。
Sequence created.
ELXAN@DB1> 创建或替换触发器ad_insert，在向cedvel表插入数据之前执行以下操作：
引用NEW AS NEW OLD AS OLD
对于每一行
begin
    从dual中选择test_seq.nextval到:new.id;
end;
/  2    3    4    5    6    7    8
Trigger created.
ELXAN@DB1> 向cedvel表插入一行数据，其中ad为'nese'。
1 row created.

- elxan

Answer 5

您可以使用SEQUENCE或TRIGGER来自动递增数据库表中给定列的值，但是使用TRIGGERS会更合适。请查看Oracle的以下文档，其中包含与触发器一起使用的主要子句和适当的示例。

使用CREATE TRIGGER语句创建并启用数据库触发器，该触发器是：

• 与表、模式或数据库关联的存储PL/SQL块或

• 匿名PL/SQL块或调用在PL/SQL或Java中实现的过程

Oracle数据库在指定条件发生时自动执行触发器。请参见。

---

以下是一个简单的TRIGGER示例，它根据该列的最大值将主键值插入到指定表中。您可以修改模式名称、表名称等并使用它。试试吧。

/*Create a database trigger that generates automatically primary key values on the CITY table using the max function.*/

CREATE OR REPLACE TRIGGER PROJECT.PK_MAX_TRIGGER_CITY
BEFORE INSERT ON PROJECT.CITY
FOR EACH ROW
DECLARE 
    CNT NUMBER;
    PKV CITY.CITY_ID%TYPE;
    NO NUMBER;
BEGIN
    SELECT COUNT(*)INTO CNT FROM CITY;

    IF CNT=0 THEN
        PKV:='CT0001';
    ELSE
        SELECT 'CT'||LPAD(MAX(TO_NUMBER(SUBSTR(CITY_ID,3,LENGTH(CITY_ID)))+1),4,'0') INTO PKV
        FROM CITY;
    END IF; 
    :NEW.CITY_ID:=PKV;
END;

会自动生成类似于 CT0001，CT0002，CT0003 等值，并插入到指定表的指定列中。

- Lion

Answer 6

为了完整起见，我提到Oracle 12c支持这个功能。此外，据说它比触发器方法更快。例如：

CREATE TABLE foo
  (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (
    START WITH 1 NOCACHE ORDER ) NOT NULL ,
    name       VARCHAR2 (50)
  )
  LOGGING ;
ALTER TABLE foo ADD CONSTRAINT foo_PK PRIMARY KEY ( id ) ;

- interestedparty333

Answer 7

我已经包含了触发器和序列的示例，这是完整的知识。

create table temasforo(
idtemasforo NUMBER(5) PRIMARY KEY,
autor       VARCHAR2(50) NOT NULL,
fecha       DATE DEFAULT (sysdate),
asunto      LONG  );

create sequence temasforo_seq
  start with 1
  increment by 1
  nomaxvalue;

create or replace
trigger temasforo_trigger
  before insert on temasforo
  referencing OLD as old NEW as new
  for each row
  begin
      :new.idtemasforo:=temasforo_seq.nextval;
    end;

reference: http://thenullpointerexceptionx.blogspot.mx/2013/06/llaves-primarias-auto-incrementales-en.html

- Daniel Kennedy

---

第二行中的“idtemasfor”应该改为“idtemasforo”吗？ - interestedparty333

Answer 8

SQL trigger for automatic date generation in oracle table:

CREATE OR REPLACE TRIGGER name_of_trigger

BEFORE INSERT

ON table_name

REFERENCING NEW AS NEW

FOR EACH ROW

BEGIN

SELECT sysdate INTO :NEW.column_name FROM dual;

END;

/

- Vivek R

Answer 9

最佳方法：从序列获取下一个值

最好的方法是从与表“关联”的SEQUENCE中获取NEXTVAL。由于序列没有直接关联到任何特定的表，我们需要手动从序列名称约定中引用相应的表。

如果遵循序列命名规范，在表上使用的序列名称将在其名称中提及表名。类似于 <table_name>_SEQ。一旦看到它，您将立即认出它。

首先，请检查Oracle系统中是否有与该表“关联”的序列。

SELECT * FROM all_sequences
WHERE SEQUENCE_OWNER = '<schema_name>';

会展示类似以下的内容:

获取那个 SEQUENCE_NAME 并在你的 INSERT 查询中评估它的 NEXTVAL

INSERT INTO workqueue(id, value) VALUES (workqueue_seq.NEXTVAL, 'A new value...')

额外提示

如果您不确定此序列实际上是否与表相关联，请快速比较序列的LAST_NUMBER（即当前值）与该表的最大ID。只要间隙不太可疑，预期LAST_NUMBER大于或等于表中当前最大ID值。

SELECT LAST_NUMBER
FROM all_sequences
WHERE SEQUENCE_OWNER = '<schema_name>' AND SEQUENCE_NAME = 'workqueue_seq';

SELECT MAX(ID)
FROM workqueue;

参考: Oracle CURRVAL 和 NEXTVAL

替代方案：从表中获取当前最大ID

另一种方法是从表中获取最大值，请参考Zsolt Sky在同一个问题中的回答

- ThangLeQuoc

Answer 10

这是一种简单的方法，不需要任何触发器或序列：

插入到WORKQUEUE（ID，facilitycode，workaction，description）中 值（（从WORKQUEUE中选择计数（1）+1），'J'，'II'，'TESTVALUES'）;

注意：这里需要使用count（1）代替max（id）列

它完美地适用于空表。

- ShivaP

---

如果已创建的ID值大于行数，那么在某些情况下，此解决方案将无法工作。例如：具有以值7开头的6行ID。 - Luciano Brum