如何在Oracle上使用AUTO_INCREMENT创建ID？

如何在Oracle上使用AUTO_INCREMENT创建ID？

表定义：

CREATE TABLE departments (  IDNUMBER(10)    NOT NULL,  DEscriptION  VARCHAr2(50)  NOT NULL);ALTER TABLE departments ADD (  ConSTRAINT dept_pk PRIMARY KEY (ID));CREATE SEQUENCE dept_seq START WITH 1;

触发定义：

CREATE OR REPLACe TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROWBEGIN  SELECT dept_seq.NEXTVAL  INTO   :new.id  FROM   dual;END;/

更新：
IDENTITY 列现在在Oracle 12c上可用：

create table t1 (    c1 NUMBER GENERATED by default on null as IDENTITY,    c2 VARCHAr2(10)    );

或指定起始值和增量值，也防止任何插入到标识列（GENERATED ALWAYS）中（同样，仅适用于Oracle 12c +）

create table t1 (    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),    c2 VARCHAr2(10)    );

另外，Oracle 12还允许使用序列作为默认值：

CREATE SEQUENCE dept_seq START WITH 1;CREATE TABLE departments (  IDNUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,  DEscriptION  VARCHAr2(50)  NOT NULL);ALTER TABLE departments ADD (  ConSTRAINT dept_pk PRIMARY KEY (ID));