官方文档创建表语句部分说明
在12c中,表支持默认列为sequence值,而且不用使用传统的触发器来实现该功能.
12c创建表使用默认sequence测试过程
SQL> create table t_xifenfei
2 (
3 id number GENERATED ALWAYS as identity (
4 start with 1
5 increment by 1
6 ),
7 name varchar2(200)
8 );
Table created.
SQL> insert into t_xifenfei(name) values('www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> col name for a30
SQL> select * from t_xifenfei;
ID NAME
---------- ------------------------------
1 www.xifenfei.com
SQL> insert into t_xifenfei(name) values('www.orasos.com');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
ID NAME
---------- ------------------------------
1 www.xifenfei.com
2 www.orasos.com
SQL> insert into t_xifenfei values(5,'www.xifenfei.com');
insert into t_xifenfei values(5,'www.xifenfei.com')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> insert into t_xifenfei(name) values('www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
ID NAME
---------- ------------------------------
1 www.xifenfei.com
2 www.orasos.com
3 www.xifenfei.com
补充说明
1.如果设置了列默认值为seq,则不能手工插入一个该列值否则报ORA-32795
2.通过10046跟踪该insert语句未发现trigger对应sql语句操作,比传统自己编写触发器效率原则上更加高