溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下Oracle12c如何實現自增列，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
眾所周知，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在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如何實現自增列”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
