Oracle 12C开始，支持使用序列来做列的默认值，今天测试一下，本测试环境是Oracle 12.2.0.1.0 for linux x86_64版本。

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

通过字面能就能看出，想用序列做列的默认值，肯定是要先有序列，下面创建一个名字为SEQ_1的序列。

SQL> create sequence seq_1 minvalue 1 maxvalue 99999 start with 1 increment by 1 cache 20 nocycle;       

Sequence created.

可以在建表的时候指定使用序列作为列的默认值，也可以通过ALTER TABLE的方式来设置。

SQL> create table t_test01(id number default seq_1.nextval,name varchar2(50));

Table created.
SQL> drop table t_test01 purge;

Table dropped.

SQL> create table t_test01(id number,name varchar2(50));

Table created.

SQL> alter table t_test01 modify id number default seq_1.nextval;

Table altered.

和其他常规的默认值一样，可以通过USER_TAB_COLUMNS视图来查看列的默认值信息。

SQL> select TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from user_tab_columns where TABLE_NAME='T_TEST01' and COLUMN_NAME='ID';

TABLE_NAME COLUMN_NAME     DATA_DEFAULT
---------- --------------- ------------------------------
T_TEST01   ID              "DBDREAM"."SEQ_1"."NEXTVAL"

像这张表插入数据时，如果不指定ID字段，就会使用序列的值作为默认值。

SQL> insert into t_test01 (name) values ('xxx');

1 row created.
SQL> select * from t_test01;

        ID NAME
---------- -------
         1 xxx

在向这张表插入数据时，如果指定ID字段并使用’’或者null值，都不会所以用序列作为默认值。

SQL> insert into t_test01 values ('','yyy');

1 row created.
SQL> commit;

Commit complete.
SQL> select * from t_test01;

        ID NAME
---------- --------
         1 xxx
           yyy
SQL> insert into t_test01 values (null,'zzz');

1 row created.
SQL> commit;

Commit complete.
SQL> select * from t_test01;

        ID NAME
---------- --------
         1 xxx
           yyy
           zzz

在向这张表插入数据时，指定default关键字，就会使用序列的值作为默认值。

SQL> insert into t_test01 values (default,'aaa');

1 row created.
SQL> commit;

Commit complete.
SQL> select * from t_test01;

        ID NAME
---------- ------
         1 xxx
           yyy
           zzz
         2 aaa

下面测试下有唯一约束的情况下，序列值和数据重复的情况，先为表添加一个唯一约束。

SQL> alter table t_test01 add constraint un_1 unique(id);

Table altered.

序列的下一个值是3，将一条记录的值修改为3。

SQL> update t_test01 set id=3 where name='yyy';

1 row updated.
SQL> commit;

Commit complete.
SQL> select * from t_test01;

        ID NAME
---------- ------
         1 xxx
         3 yyy
           zzz
         2 aaa

然后使用序列值插入数据。

SQL> insert into t_test01 values (default,'bbb');
insert into t_test01 values (default,'bbb')
*
ERROR at line 1:
ORA-00001: unique constraint (DBDREAM.UN_1) violated

这里遇到了违反唯一性约束的错误。这时，序列的当前值变成了4，update操作的时候也可以使用序列的值。

SQL> update t_test01 set id=default where name='zzz';

1 row updated.
SQL> commit;

Commit complete.
SQL> select * from t_test01;

        ID NAME
---------- --------
         1 xxx
         3 yyy
         4 zzz
         2 aaa

删除序列，表的默认值信息不会跟着改变，这时向表中插入数据，如果使用到序列作为默认值，就会报错。

SQL> drop sequence seq_1;

Sequence dropped.
SQL> select TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from user_tab_columns where TABLE_NAME='T_TEST01' and COLUMN_NAME='ID';

TABLE_NAME COLUMN_NAME     DATA_DEFAULT
---------- --------------- ------------------------------
T_TEST01   ID              "DBDREAM"."SEQ_1"."NEXTVAL"
SQL> insert into t_test01 (name) values('ddd');
insert into t_test01 (name) values('ddd')
          *
ERROR at line 1:
ORA-02289: sequence does not exist

把这个序列重新创建出来，如果有唯一约束，需要注意序列的起始值，这个问题就可以解决。

下面再测试下其他用户操作这张用序列做为列的默认值的表的情况。

先重新初始化一下测试环境。

SQL> create sequence seq_1 minvalue 1 maxvalue 99999 start with 1 increment by 1 cache 20 nocycle;       

Sequence created.
SQL> truncate table t_test01;

Table truncated.

然后创建测试用户，并授予插入这张表的权限。

SQL> create user u_test identified by dbdream;

User created.
SQL> grant connect,resource to u_test;

Grant succeeded.
SQL> grant insert on dbdream.t_test01 to u_test;

Grant succeeded.

然后切换到这个新建的用户，向测试表插入一条数据。

SQL> conn u_test/dbdream@localhost/pdb1 
Connected.
SQL> insert into dbdream.t_test01 (name) values('abc');
insert into dbdream.t_test01 (name) values('abc')
          *
ERROR at line 1:
ORA-00942: table or view does not exist

因为没有授予这个用户访问测试表使用的序列的权限，所以这里遇到了错误，这个错误竟然是表或视图不存在，看到这个错误很容易误以为是没有这张表的访问权限，其实是没有访问序列的权限。

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb1;

Session altered.
SQL> grant select on dbdream.seq_1 to u_test;

Grant succeeded.
SQL> conn u_test/dbdream@localhost/pdb1 
Connected.
SQL> insert into dbdream.t_test01 (name) values('abc');

1 row created.

授予这个用户查询测试序列的权限后，成功插入数据。

也就是说，其他用户操作这样的表时要使用序列做默认值，不但要有操作表的权限，还要有访问相关序列的权限才可以。

本文固定链接: https://www.dbdream.com.cn/2018/04/oracle-12c%e6%96%b0%e7%89%b9%e6%80%a7-%e4%bd%bf%e7%94%a8%e5%ba%8f%e5%88%97%e5%81%9a%e5%88%97%e7%9a%84%e9%bb%98%e8%ae%a4%e5%80%bc/ | 信春哥，系统稳，闭眼上线不回滚！