联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
官方文档创建表语句部分说明
在12c中,表支持默认列为sequence值,而且不用使用传统的触发器来实现该功能.
12c创建表使用默认sequence测试过程
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit 0 PL/SQL Release 12.1.0.0.2 0 CORE 12.1.0.0.2 0 TNS for Linux: Version 12.1.0.0.2 0 NLSRTL Version 12.1.0.0.2 0 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语句操作,比传统自己编写触发器效率原则上更加高