oracle 12c 新特性自增序列的测试

1、generated always as identity
21:07:46 yaoxin@pdb1> select * from user_objects;

no rows selected

21:08:30 yaoxin@pdb1> create table test(id number generated always as identity ,name varchar2(20));

21:09:13 yaoxin@pdb1> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
TEST                           TABLE
ISEQ$$_91617                   SEQUENCE

可以看到，在创建自增序列的时候，oracle会自动的创建一个sequence;
我们来查看表的定义也能看到相同的结果
select dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL;
21:12:16 yaoxin@pdb1> select dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------

  CREATE TABLE "YAOXIN"."TEST"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY M
INVALUE 1 MAXVALUE 999999999999999999999
9999999 INCREMENT BY 1 START WITH 1 CACH
E 20 NOORDER  NOCYCLE  NOT NULL ENABLE,

        "NAME" VARCHAR2(20)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25
5
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

此时如果我们进行一次插入
21:14:55 yaoxin@pdb1>  insert into test values(1,'YAO');
 insert into test values(1,'YAO')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

21:15:29 yaoxin@pdb1> insert into test(name) values('YAO');

1 row created.

Elapsed: 00:00:00.05
21:16:05 yaoxin@pdb1> select * from test;

           ID NAME
------------- --------------------
            1 YAO

21:17:23 yaoxin@pdb1> insert into test(name) values('XIN');

1 row created.

Elapsed: 00:00:00.00
21:17:36 yaoxin@pdb1> commit;

Commit complete.

Elapsed: 00:00:00.00
21:17:38 yaoxin@pdb1> select * from test;

           ID NAME
------------- --------------------
            1 YAO
            2 XIN

Elapsed: 00:00:00.00
21:17:43 yaoxin@pdb1> update test set id=100 where name='YAO';
update test set id=100 where name='YAO'
                *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column

我们再来进行测试
21:38:03 yaoxin@pdb1> drop table test;

Table dropped.

Elapsed: 00:00:01.36
21:39:40 yaoxin@pdb1>  select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
ISEQ$$_91617                   SEQUENCE

Elapsed: 00:00:00.05
21:39:51 yaoxin@pdb1> purge recyclebin;

Recyclebin purged.

Elapsed: 00:00:00.43
21:40:04 yaoxin@pdb1>  select object_name,object_type from user_objects;

no rows selected

Elapsed: 00:00:00.05
这里的出来几个结论：
1. GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
2. GENERATED IDENTITY 本质也是通过sequence实现
3. GENERATED IDENTITY 中sequence不能单独被删除
4. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
5. GENERATED IDENTITY 中的sequence可以通过select 语句查询
6. 通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值

2、GENERATED BY DEFAULT AS IDENTITY
 create table test(id number generated by default as identity ,name varchar2(20));
 
 21:43:42 yaoxin@pdb1>  select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
TEST                           TABLE
ISEQ$$_91619                   SEQUENCE

Elapsed: 00:00:00.03
21:43:57 yaoxin@pdb1> insert into test(name) values('YAO');

1 row created.
21:44:27 yaoxin@pdb1> insert into test values(10,'XIN');

1 row created.
21:44:49 yaoxin@pdb1> select * from test;

           ID NAME
------------- --------------------
            1 YAO
           10 XIN

21:46:08 yaoxin@pdb1> insert into test (name) values('A');

1 row created.

Elapsed: 00:00:00.00
21:46:22 yaoxin@pdb1>  select * from test;

           ID NAME
------------- --------------------
            1 YAO
           10 XIN
            2 A
21:46:27 yaoxin@pdb1> desc test;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER
 NAME                                                                             VARCHAR2(20)
得出结论：
1. GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入

3、GENERATED BY DEFAULT ON NULL AS IDENTITY
create table test2(id number generated by default on null as identity ,name varchar2(20));

21:47:20 yaoxin@pdb1> create table test2(id number generated by default on null as identity ,name varchar2(20));

Table created.

Elapsed: 00:00:00.07
21:50:20 yaoxin@pdb1> desc test2;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER
 NAME                                                                             VARCHAR2(20)

21:50:25 yaoxin@pdb1> insert into test2 values(null,'YAO');

1 row created.

21:50:48 yaoxin@pdb1> select * from test2;

           ID NAME
------------- --------------------
            1 YAO
测试结论：GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值