oracle设置默认值sql,12c 使用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语句操作,比传统自己编写触发器效率原则上更加高