案例操作:Oracle12c 使用sequence作为列默认值

官方文档创建表语句部分说明
在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语句操作,比传统自己编写触发器效率原则上更加高
 

-----------------温馨提示--------------------
操作有风险，动手需谨慎
Oracle研究中心
www.oracleplus.net
本文由大师惜分飞原创分享，转载请尽量保留本站网址。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例操作:Oracle12c 使用sequence作为列默认值

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/77.html

Oracle研究中心

关键词：

Oracle12c 使用sequence作为列默认值