create table test(id nu..."/>
您好,登錄后才能下訂單哦!
1.CON_ID,0為cdb,1為cdb$root, 2為pdb seed,3以上為pdb
2.自增長列
在12c之前,Oracle只能通過sequence來實現(xiàn)這個功能
sys@newtestCDB> create table test(id number generated always as identity , name varchar2(20));
Table created.
Elapsed: 00:00:00.04
sys@newtestCDB> insert into test(name) values('smith');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith3');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith4');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> select * from test;
ID NAME
1 smith
2 smith3
3 smith4
Elapsed: 00:00:00.02
sys@newtestCDB> update test set id=1 where id=2;
update test set id=1 where id=2
*
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(id,name) values(null,'smith4');
insert into test(id,name) values(null,'smith4')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(id,name) values(2,'smith4');
insert into test(id,name) values(2,'smith4')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
sys@newtestCDB> delete from test where id=3;
1 row deleted.
Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(name) values('smith5');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> select * from test;
ID NAME
2 smith3
4 smith5
Elapsed: 00:00:00.01
結論:
GENERATED ALWAYS AS IDENTITY 可以不指定該列進行插入
GENERATED ALWAYS AS IDENTITY不能在該列中插入NULL值
GENERATED ALWAYS AS IDENTITY不能指定具體值插入
GENERATED ALWAYS AS IDENTITY 不能使用update更新該列
sys@newtestCDB> create table test(id number generated by default as identity , name varchar2(20));
Table created.
Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(name) values('smith');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith3');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith4');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(id,name) values(null,'smith4');
insert into test(id,name) values(null,'smith4')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."TEST"."ID")
Elapsed: 00:00:00.02
sys@newtestCDB> insert into test(id,name) values(2,'smith3');
1 row created.
Elapsed: 00:00:00.01
sys@newtestCDB> update test set id = NULL where id=2;
update test set id = NULL where id=2
*
ERROR at line 1:
ORA-01407: cannot update ("SYS"."TEST"."ID") to NULL
結論:
GENERATED BY DEFAULT AS IDENTITY 可以不指定該列進行插入
GENERATED BY DEFAULT AS IDENTITY不能在該列中插入NULL值
GENERATED BY DEFAULT AS IDENTITY 可以指定具體值插入
GENERATED BY DEFAULT AS IDENTITY 可以使用update更新該列,但不能更新為NULL
sys@newtestCDB> create table test(id number generated by default ON NULL as identity , name varchar2(20));
Table created.
Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(id,name) values(null,'smith4');
1 row created.
Elapsed: 00:00:00.01
結論:
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以不指定該列進行插入
GENERATED BY DEFAULT ON NULL AS IDENTITY 方式可以指定具體值插入
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以在該列中插入null值
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以使用update更新該列
sys@newtestCDB> col tablename format A20
sys@newtestCDB> col table_name format A20
sys@newtestCDB> col sequence_name format A20
sys@newtestCDB> SELECT a.name AS table_name,
2 b.name AS sequence_name
3 FROM sys.idnseq$ c
4 JOIN obj$ a ON c.obj# = a.obj#
5 JOIN obj$ b ON c.seqobj# = b.obj#
6 where a.name='TEST';
TABLE_NAME SEQUENCE_NAME
TEST ISEQ$$_83962
Elapsed: 00:00:00.01
sys@newtestCDB> create table test(id number generated by default as identity , name varchar2(20));
Table created.
Elapsed: 00:00:00.03
sys@newtestCDB> SELECT a.name AS table_name,
2 b.name AS sequence_name
3 FROM sys.idnseq$ c
4 JOIN obj$ a ON c.obj# = a.obj#
5 JOIN obj$ b ON c.seqobj# = b.obj#
6 where a.name='TEST';
TABLE_NAME SEQUENCE_NAME
TEST ISEQ$$_83964
Elapsed: 00:00:00.01
sys@newtestCDB> SELECT object_name, object_type FROM user_objects where object_name='ISEQ$$_83964';
ISEQ$$_83964
SEQUENCE
Elapsed: 00:00:00.05
sys@newtestCDB> drop table test;
Table dropped.
Elapsed: 00:00:00.04
sys@newtestCDB> SELECT object_name, object_type FROM user_objects where object_name='ISEQ$$_83964';
no rows selected
Elapsed: 00:00:00.00
sys@newtestCDB> create table test(id number generated by default as identity , name varchar2(20));
Table created.
Elapsed: 00:00:00.06
sys@newtestCDB> SELECT a.name AS table_name,
2 b.name AS sequence_name
3 FROM sys.idnseq$ c
4 JOIN obj$ a ON c.obj# = a.obj#
5 JOIN obj$ b ON c.seqobj# = b.obj#
6 where a.name='TEST';
TABLE_NAME SEQUENCE_NAME
TEST ISEQ$$_83966
Elapsed: 00:00:00.01
sys@newtestCDB> drop SEQUENCE ISEQ$$_83966;
drop SEQUENCE ISEQ$$_83966
*
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence
Elapsed: 00:00:00.02
結論:
Identity Columns 是基于序列實現(xiàn)的
GENERATED IDENTITY 中sequence不能單獨被刪除
GENERATED IDENTITY 中sequence 表被刪除時同時刪除
這是12.1的圖,12.2還有新變化
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。