官方文檔建立表語句部分說明
在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語句操作,比傳統自己編寫觸發器效率原則上更加高