Oracle 使用序列實現自增列 及重置序列
序列是oracle用來生產一組等間隔的數值。序列是遞增，而且連續的。oracle主鍵沒有自增類型，所以一般使用序列產生的值作為某張表的主鍵,實現主鍵自增。序列的編號不是在插入記錄的時候自動生成的，必須調用序列的方法來生成（一般調用nextval方法）。我們也可以編寫表的insert觸發器來進自動生成。
創建語法：
create sequence 序列名稱
[start with 初始量]
[increment by 遞增量]
[maxvalue 最大值| nomaxvalue]
[minvalue 最小值| nominvalue]
[cycle | nocycle]
[cache 緩存個數| nocache];
start with：生成第一個序列號,對于升序列,其默認值為序列最小值;對于降序序列,其默認值為序列的最大值 。
increment by：用于指定序列號之間的間隔,其默認值為1,如果integer為正值,則生成的序列按升序排列,如果integer為負值,則生成的序列將按降序排列。
maxvalue：序列可以生成的最大值。
nomaxvalue：oracle將升序序列的最大值設為1027,將降序序列的最大值設為-1.這是默認選項。
minvalue：minvalue必須小于或等于start with的值,并且必須小于maxvalue的值。
nominvalue：oracle將升序的最小值設為1,或將降序序列的最小值設為-1026.這是默認值。
cycle：序列在達到最大值或最小值后,將繼續從頭開始生成值。
nocycle：序列在達到最大值或最小值后,將不能再繼續生成值。不寫默認為nocycle這是默認選項 。
cache：預先分配一組序列號,并將其保留在內存中,這樣可以更快地訪問序列號.當用完緩存中的所有序列號.oracle將生成另一組數值,并將其保留在緩存中。
nocache：不會加快訪問速度而預先分配序列號,如果在創建序列時忽略了cache和nocache,orcale將默認緩存20個序列號。
修改語法：
alter sequence 序列名稱
[start with 初始量]
[increment by 遞增量]
[maxvalue 最大值| nomaxvalue]
[minvalue 最小值| nominvalue]
[cycle | nocycle]
[cache 緩存個數| nocache];
二、例子
以下代碼person表如下：
DROP TABLE person ; CREATE TABLE person ( id NUMBER(11) NOT NULL , username VARCHAR2(255 ) NULL , age NUMBER(11) NULL , password VARCHAR2(255) NULL , PRIMARY KEY (id) )
創建序列
create sequence seq_tb_person minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 20;
使用序列
insert into person (id, username, age, password) values (seq_tb_person.nextval, '張三', 20, 'zhang123')
修改序列
alter sequence seq_tb_person minvalue 1 maxvalue 999999999999999999999999999 -- start with 49 對于已經啟動的序列，無法設置初始值 increment by 1 cache 20;
查看用戶的序列：
select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
刪除序列
drop sequence 序列名;
通過USER_OBJECTS可以查看用戶擁有的序列，USER_SEQUENCES可以查看序列的設置。
重置序列
oracle序列創建以后，如果想重置序列從 0 開始，逐漸遞增1，可以采用如下存儲過程：
1 create or replace 2 procedure reset_seq( p_seq_name in varchar2 ) 3 is 4 l_val number; 5 begin 6 execute immediate 7 'select ' || p_seq_name || '.nextval from dual' INTO l_val; 8 9 execute immediate 10 'alter sequence ' || p_seq_name || ' increment by -' || l_val || 11 ' minvalue 0'; 12 13 execute immediate 14 'select ' || p_seq_name || '.nextval from dual' INTO l_val; 15 16 execute immediate 17 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; 18 end;
存儲過程創建以后，調用該存儲過程，參數是要重置的序列名稱：
call reset_seq(‘test_seq’);
使用下面的SQL語句查詢需要重置的序列：
1 SELECT a.sequence_name 序列名稱,
2 a.min_value 序列最小值,
3 to_char(to_number( a.max_value)) 序列最大值,
4 a.last_number 序列當前值,
5 CASE a.last_number WHEN 1 THEN '--不需要重置;' ELSE
6 'CALL seq_reset(''' || a.sequence_name || ''');' END 重置序列腳本,
7 'DROP SEQUENCE ' || a.sequence_name 刪除序列腳本,
8 'RENAME ' || a.sequence_name || ' TO {newname}' 重命名腳本
9 FROM USER_SEQUENCES a WHERE a.last_number<>1
10 ORDER BY a.sequence_name ASC;
重置序列腳本這列查詢出來的值就是調用存儲過程重置序列的SQL語句，直接執行SQL將重置序列。
三、注意點
- 一個序列可以被多張別使用，不過一般建議為每個表建立單獨的序列。
- 當使用到序列的事務發生回滾。會造成序列號不連續。在用生成的序列值作為編號做插入數據庫操作時，可能遇到事務提交失敗，從而導致序號不連續。
- 大量語句發生請求，申請序列時，為了避免序列在運用層實現序列而引起的性能瓶頸。Oracle序列允許將序列提前生成 n個先存入內存，在發生大量申請序列語句時，可直接到運行最快的內存中去得到序列。但cache個數最好不要設置過大，因為在數據庫重啟時，會清空內存信息，預存在內存中的序列會丟失，當數據庫再次啟動后，序列從上次內存中最大的序列號+1 開始存入n個。這種情況也能會在數據庫關閉時也會導致序號不連續。
浙公網安備 33010602011771號