SQL Oracle - 插入带有自动增量 ID 的新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8717675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Insert New Row with Auto Incremental ID
提问by Shaji
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.
我有一个工作队列表，它有一个 workid 列。workID 列具有自动递增的值。有没有办法可以在后端运行查询以插入新行并自动增加 workID 列？
当我尝试插入一个空值时，它会抛出错误 ORA01400 - 无法将空值插入到工作 ID 中。
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
数据库：Oracle 10g
Screenshot of some existing data.
一些现有数据的屏幕截图。
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!"
你们提供给我的链接帮助我查找了这些序列并找到了用于此 workid 列的序列。多亏了大家，给了大家一个赞，今天我又可以战胜另一条龙了，帮助病人护理向前迈进了一步！”
采纳答案by Johnny Graber
To get an auto increment number you need to use a sequence in Oracle. (See hereand here).
要获得自动递增编号，您需要在 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;
/
回答by SlavaNov
回答by Zsolt Sky
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.
它对我有用，但我猜对空桌子不起作用。
回答by elxan
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.
回答by Lion
You can use either SEQUENCEor TRIGGERto increment automatically the value of a given column in your database table however the use of TRIGGERSwould be more appropriate. See the following documentation of Oracle that contains major clauses used with triggers with suitable examples.
您可以使用SEQUENCE或TRIGGER来自动增加数据库表中给定列的值，但是使用TRIGGERS会更合适。请参阅以下 Oracle 文档，其中包含与触发器一起使用的主要子句以及合适的示例。
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.
使用 CREATE TRIGGER 语句创建并启用数据库触发器，即：
与表、模式或数据库相关联的存储 PL/SQL 块或
一个匿名的 PL/SQL 块或对用 PL/SQL 或 Java 实现的过程的调用
当指定条件发生时，Oracle 数据库会自动执行触发器。见。
Following is a simple TRIGGERjust as an example for you that inserts the primary key value in a specified table based on the maximum valueof that column. You can modify the schema name, table name etc and use it. Just give it a try.
以下是一个简单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;
Would automatically generates values such as CT0001, CT0002, CT0002and so on and inserts into the given column of the specified table.
会自动产生的值，如CT0001，CT0002，CT0002等等和插入到指定表的给定的列。
回答by Vivek R
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;
/
/
回答by Daniel Kennedy
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
参考：http: //thenullpointerexceptionx.blogspot.mx/2013/06/llaves-primarias-auto-incrementales-en.html
回答by ragerdl
For completeness, I'll mention that Oracle 12c does support this feature. Also it's supposedly faster than the triggers approach. For example:
为完整起见，我将提到 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 ) ;
回答by ShivaP
This is a simple way to do it without any triggers or sequences:
这是一种简单的方法，无需任何触发器或序列：
insert into WORKQUEUE (ID, facilitycode, workaction, description) values ((select count(1)+1 from WORKQUEUE), 'J', 'II', 'TESTVALUES');
插入到工作队列（ID、设施代码、工作操作、描述）值（（从工作队列中选择计数（1）+1）、'J'、'II'、'TESTVALUES'）；
Note : here need to use count(1) in place of max(id) column
注意：这里需要使用 count(1) 代替 max(id) 列
It perfectly works for an empty table also.
它也适用于空桌子。
