IT技术博客

要求数据库版本12c及以上

语法

GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY
[sequence_options,...]

语法解析

语法	语法说明
GENERATED ALWAYS AS IDENTITY	该列永远使用序列产生的值
GENERATED BY DEFAULT AS IDENTITY	该类未赋值时使用序列产生的值
GENERATED BY DEFAULT ON NULL AS IDENTITY	该类为空时使用序列产生的值

举个栗子

示例1

1.创建表

CREATE TABLE  tname1 ( id INTEGER GENERATED ALWAYS AS IDENTITY, 
name varchar2(10))

2.插入数据

--错误的使用方式
HR@pdb1>insert into tname1 values (1,'angu')
  2  ;
insert into tname1 values (1,'angu')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

解释： 因为已经显示的指定了序列，所以在插入的时候不能再指定了

--正确的使用方式
HR@pdb1>insert into tname1 (name )  values ('angu');

HR@pdb1>select * from tname1;
        ID NAME
---------- ------------------------------
         1 angu

示例2

1.创建表

CREATE TABLE  tname2 (
id number GENERATED BY DEFAULT AS IDENTITY, 
name varchar2(10));

2.插入数据

insert into tname2 values (2,'angu');
insert into tname2 (name) values ('ansiheng');

3.查询数据

HR@pdb1>select * from tname2;

        ID NAME
---------- ------------------------------
         2 angu
         1 ansiheng

解释：插入第二条数据的时候未指定id的值，此时使用序列的值。

4.插入无效数据

HR@pdb1>insert into tname2 values (null, 'invalid');
insert into tname2 values (null, 'invalid')
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TNAME2"."ID")

指定了ID列，此时不使用序列产生的值，但插入的值为NULL，违反了非空约束。(带序列的列不能为空)

示例3

1.创建表

CREATE TABLE  tname3 (
id number GENERATED BY DEFAULT AS IDENTITY, 
name varchar2(10),
PRIMARY KEY (id)
);

2.插入数据

--第一次插入
>insert into tname3 values (1,'angu')
  2  ;
--第二次插入
HR@pdb1>insert into tname3 (name) values ('ansiheng');
insert into tname3 (name) values ('ansiheng')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C007585) violated
--第三次插入
HR@pdb1>insert into tname3 (name) values ('ansiheng');

解释：第二次插入的时候因为未指定ID，所以使用序列产生的值，该序列产生的值是1，但被第一次插入的时候使用了，所以报了主键冲突，再次执行时产生的序列是2，这次插入成功了。

示例4

1.创建表

CREATE TABLE  tname4 (
id number GENERATED BY DEFAULT ON NULL AS IDENTITY, 
name varchar2(10)
)

2.插入数据

--第一次插入数据
HR@pdb1>insert into tname4 values (1,'angu');
1 row created.
--第二次插入数据
HR@pdb1>insert into tname4 (name) values ('ansiheng1');
1 row created.
--第三次插入数据
HR@pdb1>insert into tname4  values (null,'chuwu');
1 row created.

解释 第二次插入数据由于未提供ID列，使用了序列产生的值，第三次插入出入的数据为NULL，同样使用了序列产生的值

3.查询数据

HR@pdb1>select * from tname4;
        ID NAME
---------- ------------------------------
         1 angu
        10 ansiheng1
        11 chuwu

示例5

1.创建表

CREATE Table tname5 (
id number   GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 2 
CYCLE 
MAXVALUE 100000 
CACHE 200),
name varchar2(10))

2.插入数据

insert into tname5 (name) values('angu');

insert into tname5 (name) values('ansiheng');

3.查询数据

HR@pdb1>select * from tname5;

        ID NAME
---------- ------------------------------
         1 angu
         3 ansiheng

解释 因为序列中的步长(INCREMENT BY)是2, 所以第二次插入数据产生的序列值是3，还有因为指定了CYCLE，必须同时指定 MAXVALUE