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值
| 
转播
