共計 4775 個字符，預計需要花費 12 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 Oracle12c 如何實現自增列，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
一．Mysql 自增列
眾所周知，mysql 有自增列功能。
Mysql 中每張表只能有一個自增列，并且自增列必須是主鍵或者唯一鍵。
mysql create table test2(id int not null auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql create table test2(id int not null
auto_increment
primary key);
Query OK, 0 rows affected (0.01 sec)
Mysql 插入空值，可以看到是允許的，默認會用自增列值插入。
mysql insert into test2 values();
Query OK, 1 row affected (0.01 sec)
mysql select * from test2;
+—-+
| id |
+—-+
| 1 |
+—-+
1 row in set (0.00 sec)
Mysql 也是允許插入的值不連續
mysql insert into test2 values(3);
Query OK, 1 row affected (0.00 sec)
mysql select * from test2;
+—-+
| id |
+—-+
| 1 |
| 3 |
+—-+
2 rows in set (0.00 sec)
表加一列，顯式插入新增列，自增列會隱式地從當前最大值自增。
mysql insert into test2(b) values(4);
Query OK, 1 row affected (0.00 sec)
mysql select * from test2;
+—-+——+
| id | b |
+—-+——+
| 1 | NULL |
| 3 | NULL |
| 4 | 4 |
+—-+——+
3 rows in set (0.00 sec)
二．Oracle 自增列
Oracle 在 12c 之前的版本，自增列功能只能通過序列 + 觸發器的方式實現。12c 版本終于迎來了這個新功能。
自增列語法
建表
SQL CREATE TABLE test1 (
2 id int
GENERATED ALWAYS AS IDENTITY
3 );
Table created.
SQL desc test1
Name Null? Type
—————————————– ——– —————————-
ID NOT NULL NUMBER(38)
自增列會自動加上 not null 約束。
此時會自動生成一個序列
SQL select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE from dba_sequences where SEQUENCE_OWNER= MING
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE
—————————— ———- ———- ———— ———-
ISEQ$$_45410
1 1.0000E+28 1 20
SQL set line 150
SQL select TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from dba_tab_columns where TABLE_NAME= TEST1 and COLUMN_NAME= ID
TABLE_NAME COLUMN_NAME DATA_DEFAULT
—————————— —————————— ————————————————–
TEST1 ID MING . ISEQ$$_45410 .nextval
注意下面這兩個動作
SQL select ISEQ$$_45410.nextval from dual;
NEXTVAL
———-
1
SQL select ISEQ$$_45410.nextval from dual;
NEXTVAL
———-
2
插入一個值
SQL insert into test1 values(1);
insert into test1 values(1)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
不能向自增列插入值。
SQL alter table test1 add b int;
Table altered.
SQL insert into test1(b) values(1);
1 row created.
SQL select * from test1;
ID B
———- ———-
3 1
可以看到 ID 列插入了值，但是不是 1，而是 3. 因為之前手動 select 從序列中取過值。
經過上面實驗不難猜測，下面語句會報錯
SQL insert into test1(id,b) values(4,1);
insert into test1(id,b) values(4,1)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
Update 和 delete
SQL update test1 set id=5;
update test1 set id=5
*
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
SQL delete from test1;
1 row deleted.
SQL commit;
Commit complete.
Update 不可以，但是 delete 是可以的。
可以用 default 指代
SQL insert into test1 values(default,1);
1 row created.
SQL select * from test1;
ID B
———- ———-
5 1
SQL commit;
Commit complete.
生成的序列其他用戶也是可以用的
SQL insert into tx(a) values(ISEQ$$_45410.nextval);
1 row created.
SQL commit;
Commit complete.
這個序列是不能刪除的。
SQL drop sequence ming.ISEQ$$_45410;
drop sequence ming.ISEQ$$_45410
*
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence
SQL drop table test1;
Table dropped.
SQL select ISEQ$$_45410.nextval from dual;
NEXTVAL
———-
6
Purge 回收站后就沒有了。
SQL purge recyclebin;
Recyclebin purged.
所以在刪除表時可以加 purge，加 cascade constraint 也不會馬上刪除序列。
SQL CREATE TABLE test2 (
2 id int
GENERATED BY DEFAULT ON NULL AS IDENTITY
3 );
Table created.
SQL insert into test2 values(
1 row created.
SQL select * from test2;
ID
———-
1
Insert 可以指定值，但是指定的值不會被序列識別，換句話說，如果手動插入值 2，序列的下一個值是 2，那么在此插入空的時候，會插入 2，而不是 3.
SQL insert into test2 values(4);
1 row created.
SQL insert into test2 values(3);
1 row created.
SQL commit;
Commit complete.
SQL select ISEQ$$_45418.nextval from dual;
NEXTVAL
———-
3
SQL select * from test2;
ID
———-
1
2
4
3
SQL insert into test2 values(
1 row created.
SQL select * from test2;
ID
———-
1
2
4
3
4
下面這個 Update 的動作有點意思，當前表上有 2 條 ID= 4 的值，用 default 更新的時候會用序列賦予不同的值。
SQL update test2 set id=default where id=4;
2 rows updated.
SQL select * from test2;
ID
———-
1
2
5
3
6
既然已經知道，自增列是用序列創建的，那么下面兩種方式也是可以的
create sequence ming$seq01 minvalue 1 maxvalue 9999999 start with 1
increment by 2
cache 20 nocycle;
create table test3(id int default ming$seq01.nextval);
create table test3(id number);
alter table test3 modify id number default seq_1.nextval;
區別在于是插入 null 的時候不會變成數字，該列也沒有 not null 約束。
SQL insert into test3 values(null);
1 row created.
SQL select * from test3;
ID
———-
l
Oracle 自增列是利用序列做到的。自增列會自動加上非空約束；
l
表刪除的時候，序列不會馬上刪除，這是因為刪除的表會進入回收站，要關聯刪除需要加 purge；
l
GENERATED ALWAYS AS IDENTITY 可以 delete，不能顯示 insert，不能 update；
l
GENERATED BY DEFAULT ON NULL AS IDENTITY 會自動將 null 值插入序列中的值，增刪改都可以，相比 GENERATED ALWAYS AS IDENTITY 更加靈活，但是列的唯一性不能保證。
l
受自增列啟發，可以自己創建序列，指定為表列的默認值。
l
系統自建序列的屬性不能更改，可以在創建自增列的時候手動修改，否則較小 cache 默認值，會造成性能問題。Like this:
CREATE TABLE test4 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10
cache 100));
l
其他用戶如果要向帶有自增列表中插入數據的話，那么需要序列權限
以上是“Oracle12c 如何實現自增列”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
