oracle12c之前如果需要创建自增列必须要通过sequence+trigger来实现。但是oracle12c已经可以像mysql,sqlserver一样通过identity column来设置自增列了。
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0
Connected as My@TEST SQL>
SQL> create table tab_test2
2 (
3 id number(38) generated as identity (start with 1 increment by 1),
4 name varchar2(30),
5 constraint PK_tab_test2 primary key (id)
6 );
Table created SQL> insert into tab_test2(name)values('hello');
1 row inserted SQL> select * from tab_test2;
ID NAME
--------------------------------------- ------------------------------
1 hello SQL> insert into tab_test2(name)values('hello2');
1 row inserted SQL> insert into tab_test2(name)values('hello3');
1 row inserted SQL> select * from tab_test2;
ID NAME
--------------------------------------- ------------------------------
1 hello
2 hello2
3 hello3 SQL> drop table tab_test2;
Table dropped
当然也支持12c之前的版本所采用的方案：sequence+trigger的方案
SQL>
SQL> create table t_test (id number,name varchar2(100));
Table created
SQL> create sequence t_test_sequence
2 increment by 1
3 minvalue 1
4 nomaxvalue
5 start with 1
6 cache 20
7 order;
Sequence created
SQL> create or replace trigger t_test_id
2 before insert on t_test
3 for each row
4 begin
5 select t_test_sequence.nextval into :new.id from dual;
6 end;
7 /
Trigger created
SQL> insert into t_test(name) values('hello1');
1 row inserted
SQL> insert into t_test(name) values('hello2');
1 row inserted
SQL> insert into t_test(name) values('hello3');
1 row inserted SQL> select * from t_test;
ID NAME
---------- --------------------------------------------------------------------------------
1 hello1
2 hello2
3 hello3 SQL> drop table t_test;
Table dropped SQL>
查阅文档：
http://www.xifenfei.com/2015/03/oracle-12c-%E6%96%B0%E7%89%B9%E6%80%A7identity-columns-%E5%AE%9E%E7%8E%B0oracle%E8%87%AA%E5%A2%9E%E9%95%BF%E5%88%97%E5%8A%9F%E8%83%BD.html