12C中identifiy自定义增长列

identifiy 是定义自动增加的列，就像是自动编号，以前在做.net开发中使用sqlserver的时候也有这类的方法。这个值的特点就是不会重复，所以叫它自动生成的唯一值。
以下是生成的两种方式：
GENERATED BY ALWAYS AS IDENTITY 
GENERATED BY DEFAULT AS IDENTITY


By always和by default是说明生成这个identifiy的方式
By  always是完全由系统自动生成；
by default是可以由用户指定一个值生成。
下面我用by default 的值来实验一下：


[oracle@dbsnc ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 28 11:18:10 2018


Copyright (c) 1982, 2016, Oracle.  All rights reserved.




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


--建表
SQL> create table customer(customer_id number generated by default as identity(start with 1 increment by 1),name varchar2(20));


Table created.


--新增数据
insert into customer(name)  values('A1');
insert into customer(name)  values('A2');
insert into customer(name)  values('A3');
insert into customer(name)  values('A4');
insert into customer(name)  values('A5');
insert into customer(name)  values('A6');


1 row created.


--提交
SQL> commit;


Commit complete.


--查看
SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
 4 A4
 5 A5
 6 A6


--删除数据
SQL> delete from customer where name='A3';


1 row deleted.


SQL> commit;


Commit complete.


--再次查看
SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
 4 A4
 5 A5
 6 A6






SQL> insert into customer(name) values('A7');
insert into customer(name) values('A8');
1 row created.


SQL> commit;


Commit complete.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
 4 A4
 5 A5
 6 A6
 7 A7
 8 A8


7 rows selected.


SQL> delete from customer where name='A8';


1 row deleted.


SQL> commit;


Commit complete.


SQL> insert into customer(name) values('A9');


1 row created.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
21 A9
 4 A4
 5 A5
 6 A6
 7 A7


7 rows selected.


SQL> commit;


Commit complete.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
21 A9
 4 A4
 5 A5
 6 A6
 7 A7


7 rows selected.


SQL> insert into customer(name) values('A10');


1 row created.


SQL> commit;


Commit complete.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
21 A9
 4 A4
 5 A5
 6 A6
 7 A7
22 A10


8 rows selected.


综上实验可以得出：自动生成的CUSTOMER_ID即使删除掉了，也不会重复回收利用，而是重新生成一个编号。