当前位置: 首页 > Oracle, Oracle 12c > 正文

Oracle 12C新特性-使用序列做列的默认值

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.

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

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

本文固定链接: http://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/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2018年04月26日发表在 Oracle, Oracle 12c 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: Oracle 12C新特性-使用序列做列的默认值 | 信春哥,系统稳,闭眼上线不回滚!
关键字:

Oracle 12C新特性-使用序列做列的默认值:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter