在不使用触发器的情况下在 Oracle 中自动递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/317001/
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
Auto-increment in Oracle without using a trigger
提问by Lakshmi
What are the other ways of achieving auto-increment in oracle other than use of triggers?
除了使用触发器，还有哪些实现oracle自增的方式？
采纳答案by Salamander2007
As far as I can recall from my Oracle days, you can't achieve Auto Increment columns without using TRIGGER. Any solutions out there to make auto increment column involves TRIGGER and SEQUENCE (I'm assuming you already know this, hence the no trigger remarks).
据我回忆，在我的 Oracle 时代，不使用 TRIGGER 就无法实现自动增量列。任何制作自动增量列的解决方案都涉及 TRIGGER 和 SEQUENCE（我假设您已经知道这一点，因此没有触发备注）。
回答by Dheer
You can create and use oracle sequences. The syntax and details are at http://www.techonthenet.com/oracle/sequences.php
您可以创建和使用 oracle 序列。语法和详细信息位于 http://www.techonthenet.com/oracle/sequences.php
Also read the article http://rnyb2.blogspot.com/2006/02/potential-pitfall-with-oracle-sequence.htmlto understand the limitations with respect to AUTONUMBER in other RDBMS
另请阅读文章 http://rnyb2.blogspot.com/2006/02/potential-pitfall-with-oracle-sequence.html以了解其他 RDBMS 中 AUTONUMBER 的限制
回答by angus
If you don't need sequential numbers but only a unique ID, you can use a DEFAULT of SYS_GUID(). Ie:
如果您不需要序列号而只需要唯一 ID，则可以使用 SYS_GUID() 的默认值。IE：
CREATE TABLE xxx ( ID RAW(16) DEFAULT SYS_GUID() )
回答by Tony Andrews
A trigger to obtain the next value from a sequence is the most common way to achieve an equivalent to AUTOINCREMENT:
从序列中获取下一个值的触发器是实现等价于 AUTOINCREMENT 的最常用方法：
create trigger mytable_trg
before insert on mytable
for each row
when (new.id is null)
begin
select myseq.nextval into :new.id from dual;
end;
You don't need the trigger if you control the inserts - just use the sequence in the insert statement:
如果您控制插入，则不需要触发器 - 只需使用插入语句中的序列：
insert into mytable (id, data) values (myseq.nextval, 'x');
This could be hidden inside an API package, so that the caller doesn't need to reference the sequence:
这可以隐藏在 API 包中，以便调用者不需要引用序列：
mytable_pkg.insert_row (p_data => 'x');
But using the trigger is more "transparent".
但是使用触发器更“透明”。
回答by FerranB
Create a sequence:
创建一个序列：
create sequence seq;
Then to add a value
然后添加一个值
insert into table (id, other1, other2)
values (seq.nextval, 'hello', 'world');
Note: Look for oracle docs for more options about sequences (start value, increment, ...)
注意：查找 oracle 文档以获取有关序列的更多选项（起始值、增量...）
回答by Ben
From 12c you can use an identity column, which makes explicit the link between table and auto-increment; there's no need for a trigger or a sequence. The syntax would be:
从 12c 开始，您可以使用标识列，它明确了表和自动增量之间的链接；不需要触发器或序列。语法是：
create table <table_name> ( <column_name> generated as identity );
回答by Reinier
In addition to e.g. FerranB's answer:
It is probably worth to mention that, as opposed to how auto_incement works in MySQL:
除了例如 FerranB 的回答：
可能值得一提的是，与 auto_incement 在 MySQL 中的工作方式相反：
回答by XpiritO
If you don't really want to use a "trigger-based" solution, you can achieve the auto-increment functionality with a programmatical approach, obtaining the value of the auto increment key with the getGeneratedKeys()method.
如果您真的不想使用“基于触发器”的解决方案，则可以通过编程方法实现自动增量功能，使用方法获取自动增量键的值getGeneratedKeys()。
Here is a code snippet for your consideration:
这是供您考虑的代码片段：
Statement stmt = null;
ResultSet rs = null;
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTable");
stmt.executeUpdate("CREATE TABLE autoIncTable ("
+ "priKey INT NOT NULL AUTO_INCREMENT, "
+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");
stmt.executeUpdate("INSERT INTO autoIncTable (dataField) "
+ "values ('data field value')",
Statement.RETURN_GENERATED_KEYS);
int autoIncKeyFromApi = -1;
rs = stmt.getGeneratedKeys();
if (rs.next()) {
autoIncKeyFromApi = rs.getInt(1);
}
else {
// do stuff here
}
rs.close();
source: http://forums.oracle.com/forums/thread.jspa?messageID=3368856
来源：http: //forums.oracle.com/forums/thread.jspa?messageID=3368856
回答by kedar kamthe
SELECT max (id) + 1
FROM table
