Oracle - 插入具有自动增量 ID 的新行

发布于 2024-12-24 19:17:01 字数 716 浏览 1 评论 0原文

我有一个工作队列表，其中有一个 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 专栏的序列。谢谢大家，我给大家竖起了大拇指，今天我又能够战胜一条龙，帮助病人护理向前迈进一步！”

原文

I have a workqueue table that has a workid column. The workID column has values that increment automatically. Is there a way I can run a query in the backend to insert a new row and have the workID column increment automatically?
When I try to insert a null, it throws error ORA01400 - Cannot insert null into workid.

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

What I have tried so far - I tried to look at the table details and didn't see any auto-increment. The table script is as follow

"WORKID" NUMBER NOT NULL ENABLE,

Database: Oracle 10g

Screenshot of some existing data.
enter image description here

ANSWER:

I have to thank each and everyone for the help. Today was a great learning experience and without your support, I couldn't have done. Bottom line is, I was trying to insert a row into a table that already has sequences and triggers. All I had to do was find the right sequence, for my question, and call that sequence into my query.

The links you all provided me helped me look these sequences up and find the one that is for this workid column. Thanks to you all, I gave everyone a thumbs up, I am able to tackle another dragon today and help patient care take a step forward!"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愛上了 2024-12-31 19:17:01

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

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

它对我有用，但我猜它不适用于空表。

This is a simple way to do it without any triggers or sequences:

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

It worked for me but would not work with an empty table, I guess.

回复收藏 0 原文

梦旅人picnic 2024-12-31 19:17:01

要获得自动递增编号，您需要在 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;
/

To get an auto increment number you need to use a sequence in Oracle.
(See here and here).

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

回复收藏 0 原文

东北女汉子 2024-12-31 19:17:01

Oracle中没有内置的auto_increment。

您需要使用序列和触发器。

请阅读此处如何正确执行此操作。（“在 Oracle 中创建自动增量列”的分步操作方法）

回复收藏 0 原文

撩起发的微风 2024-12-31 19:17:01

您可以使用 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、CT0002 等值，并插入到指定表的给定列中。

You can use either SEQUENCE or TRIGGER to increment automatically the value of a given column in your database table however the use of TRIGGERS would be more appropriate. See the following documentation of Oracle that contains major clauses used with triggers with suitable examples.

Use the CREATE TRIGGER statement to create and enable a database trigger, which is:
A stored PL/SQL block associated with a table, a schema, or the
database or
An anonymous PL/SQL block or a call to a procedure implemented in
PL/SQL or Java
Oracle Database automatically executes a trigger when specified conditions occur. See.

Following is a simple TRIGGER just as an example for you that inserts the primary key value in a specified table based on the maximum value of that column. You can modify the schema name, table name etc and use it. Just give it a try.

/*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;

Would automatically generates values such as CT0001, CT0002, CT0002 and so on and inserts into the given column of the specified table.

回复收藏 0 原文

泛滥成性 2024-12-31 19:17:01

ELXAN@DB1> create table cedvel(id integer,ad varchar2(15));

Table created.

ELXAN@DB1> alter table cedvel add constraint pk_ad primary key(id);

Table altered.

ELXAN@DB1> create sequence test_seq start with 1 increment by 1;

Sequence created.

ELXAN@DB1> create or replace trigger ad_insert
before insert on cedvel
REFERENCING NEW AS NEW OLD AS OLD
for each row
begin
    select test_seq.nextval into :new.id from dual;
end;
/  2    3    4    5    6    7    8 

Trigger created.

ELXAN@DB1> insert into cedvel (ad) values ('nese');

1 row created.

ELXAN@DB1> create table cedvel(id integer,ad varchar2(15));

Table created.

ELXAN@DB1> alter table cedvel add constraint pk_ad primary key(id);

Table altered.

ELXAN@DB1> create sequence test_seq start with 1 increment by 1;

Sequence created.

ELXAN@DB1> create or replace trigger ad_insert
before insert on cedvel
REFERENCING NEW AS NEW OLD AS OLD
for each row
begin
    select test_seq.nextval into :new.id from dual;
end;
/  2    3    4    5    6    7    8 

Trigger created.

ELXAN@DB1> insert into cedvel (ad) values ('nese');

1 row created.

回复收藏 0 原文

天涯沦落人 2024-12-31 19:17:01

完整的知识，我已经包含了触发器和序列

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;

参考的示例：
http://thenullpointerexceptionx.blogspot.mx/2013/06 /llaves-primarias-auto-incrementales-en.html

the complete know how, i have included a example of the triggers and sequence

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

回复收藏 0 原文

叹梦 2024-12-31 19:17:01

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;

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;

回复收藏 0 原文

离线来电— 2024-12-31 19:17:01

为了完整起见，我将提到 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 ) ;

For completeness, I'll mention that Oracle 12c does support this feature. Also it's supposedly faster than the triggers approach. For example:

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

回复收藏 0 原文

扭转时空 2024-12-31 19:17:01

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

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

表上使用的序列名称，如果遵循序列命名约定，将在其名称中提及表名称。类似于 _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 在同一问题中的回答

Best approach: Get the next value from sequence

The nicest approach is getting the NEXTVAL from the SEQUENCE "associated" with the table. Since the sequence is not directly associated to any specific table,
we will need to manually refer the corresponding table from the sequence name convention.

The sequence name used on a table, if follow the sequence naming convention, will mention the table name inside its name. Something likes <table_name>_SEQ. You will immediately recognize it the moment you see it.

First, check within Oracle system if there is any sequence "associated" to the table

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

will present something like this

Grab that SEQUENCE_NAME and evaluate the NEXTVAL of it in your INSERT query

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

Additional tip

In case you're unsure if this sequence is actually associated with the table, just quickly compare the LAST_NUMBER of the sequence (meaning the current value) with the maximum id of
that table. It's expected that the LAST_NUMBER is greater than or equals to the current maximum id value in the table, as long as the gap is not too suspiciously large.

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

SELECT MAX(ID)
FROM workqueue;

Reference: Oracle CURRVAL and NEXTVAL

Alternative approach: Get the current max id from the table

The alternative approach is getting the max value from the table, please refer to Zsolt Sky answer in this same question

回复收藏 0 原文

微暖i 2024-12-31 19:17:01

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

插入 WORKQUEUE（ID、设施代码、工作操作、描述）
值（（从工作队列中选择计数（1）+1），'J'，'II'，'TESTVALUES'）；

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

它也非常适合空表。

回复收藏 0 原文

~没有更多了~

关于作者

倥絔

暂无简介

文章

25 人气

关注发私信

友情链接

文江博客

Oracle - 插入具有自动增量 ID 的新行

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（10）

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

替代方法：从表中获取当前最大 id

Best approach: Get the next value from sequence

Alternative approach: Get the current max id from the table

关于作者

相关话题

热门标签

推荐作者

π浅易

吃兔兔

檐上三寸雪

横笛休吹塞上声

mb_OO8gCSDD

不学无术

友情链接

Oracle - 插入具有自动增量 ID 的新行

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（10）

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

替代方法：从表中获取当前最大 id

Best approach: Get the next value from sequence

Alternative approach: Get the current max id from the table

关于作者

相关话题

热门标签

推荐作者

π浅易

吃兔兔

檐上三寸雪

横笛休吹塞上声

mb_OO8gCSDD

不学无术

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。