ORACLE sequence各引數及建立修改刪除使用詳解示例

ORACLE沒有自增資料型別，如需生成業務無關的主鍵列或惟一約束列，可以用sequence序列實現。

CREATE SEQUENCE語句及引數介紹：

詳見官方文件：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm#SQLRF01314
建立序列：需要有CREATE SEQUENCE或者CREATE ANY SEQUENCE許可權，
CREATE SEQUENCE [ schema. ]sequence
     [ { INCREMENT BY | START WITH } integer
     | { MAXVALUE integer | NOMAXVALUE }
     | { MINVALUE integer | NOMINVALUE }
     | { CYCLE | NOCYCLE }
     | { CACHE integer | NOCACHE }
     | { ORDER | NOORDER }
     ];    

CREATE SEQUENCE各引數詳解：

schema  指定在哪個使用者的schema下建立sequence，如不指定，預設在當前使用者下建立。
sequence    指定要建立的sequence序列名
注意：如果只指定以上引數，將啟動一個從1開始，以1為單位遞增，沒有最大值限制的遞增序列。
如果要建立一個沒有約束的序列，遞增序列時：忽略MAXVALUE引數或指定NOMAXVALUE；遞減序列：省略MINVALUE引數或指定NOMINVALUE。
如果要建立一個有限制的序列，遞增序列時：指定MAXVALUE引數；遞減序列：指定MINVALUE引數。此時序列達到限制後會報錯：
如果要建立一個有限制的序列在達到限制後重新啟動，指定MAXVALUE和MINVALUE後，還需要指定CYCLE。如果不指定MINVALUE，預設為NOMINVALUE，這個值是1.
######
INCREMENT BY 指定序列號間的間隔，這個整數值可以是任何正整數或負整數，但不能是0。這個值最多有28個數字。絕對值必須小於MAXVALUE與MINVALUE的差異（如非在此區間報錯：ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE）。如果這個值是負的，則該序列下降。如果該值為正，則序列上升。如果省略此子句，則間隔預設為1。
START WITH指定要產生的第一個序列號。
此子句啟動一個遞增序列，要大於最小值；或啟動一個遞減序列，小於它最大值。對於遞增序列，預設值是序列中的最小值。對於遞減的序列，預設值是序列中的最大值。這個整數值可以最多28個數字。
這個值和達到限制的最大/最小值後重新啟動時的值沒有關係（如遞增序列建立時指定有最大值最小值且指定CYCLE，則序列達到最大值後，會從最小值開始；如未指定兼包最小值，預設1開始。
MaxValue 指定序列可生成的最大值。這個整數值可以最多28個數字。MAXVALUE必須>=START WITH、必須大於MINVALUE。
NOMAXVALUE：指定NOMAXVALUE表示遞增序列的最大值是10的27次方，或遞減序列最大值為-1。這是預設的。
MINVALUE：指定序列的最小值。這個整數值可以最多28個數字。MINVALUE必須<=START WITH的值和必須小於MAXVALUE。如此處不符，報：ORA-04006: START WITH cannot be less than MINVALUE。不指定此引數時，預設是1.
NOMINVALUE：指定NOMINVALUE來表示遞增的序列最小值為1，遞減序列為負10的26次方。這是預設的。
CYCLE：指定迴圈，表明序列在達到它的最大或最小值後生成的值。當遞增序列達到最大值後，再從最小值開始迴圈。當遞減序列達到最小值，從最大值開始迴圈。
NOCYCLE：指定NOCYCLE以指示該序列不能在達到其最大值或最小值後產生更多的值。這是預設的。達到最大值會報錯：ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
CACHE ：指定資料庫為序列預分配多少個值放在記憶體中以便更快訪問。這個整數值可以最多28個數字。該引數最小值為2；這個值必須小於一個CYCLE迴圈的數（比如從1-100是一個迴圈，CACHE要小於100，不然可能 一次CACHE的值要有重複的會出錯。報錯是：ORA-04013: number to CACHE must be less than one cycle）。
計算公式是：(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
如果系統故障，記憶體中未使用的CACHE值會丟失，將會導致序列不連續。    ORACLE建議在RAC中使用CACHE來提高效能。
NOCACHE  ：指定該序列值不被預分配。如果省略CACHE和NOCACHE，資料庫預設會快取20個序列號。
ORDER ：只有在RAC時需要指定，指定ORDER 是為了保證序列號是因為有請求才生成的。在使用序列號做為一個時間戳時很有用。
NOORDER：這是預設的。

使用sequence時對系統效能大致有以下影響： -->Seq$基表

詳見：http://blog.itpub.net/17203031/viewspace-717042
1.Seq$基表是記錄系統sequence的資料字典表.每次呼叫nextval，會遞迴呼叫並更新 、COMMIT Seq$基表。
2.更新Seq$基表並提交會產生redo log--幾百位元組，COMMIT頻繁會造成LGWR的壓力；過多redo log生成，造成LGWR壓力、恢復時費時等。
3.多個會話使用sequence可能出現爭用，等待事件row lock contention
對於nocache/cache引數：
nocache：每次使用nextval，都會更新Seq$基表並COMMIT。
cache：只有在記憶體中cache的序列號使用完後才會重新獲取sequence，才會更新Seq$基表並提交。比如cache設定為2000，則在使用sequence時對效能影響比nocache小上百倍。

所以一般情況下，建議設定一個較大的cache值，用於進行效能的優化。（預設不指定nocache時是20）

RAC時，如果序列號不要求有序建議用NOORDER+CACHE

#####################################################

ORACLE sequence建立示例：

create sequence bys.test_seq
increment by 3
start with 5
maxvalue 18
minvalue 4
cycle
cache 4;
在bys使用者下建立名為test_seq的sequence
從5開始，每次增加3，最大值是18，最小值是4
允許重用，cache 4 表示會快取四個序列號，比如5 8 11 14
當然在實驗中也可以使用最簡單的：create sequence bys.test_seq2;  其它引數不寫，使用系統預設哈哈
#####################################################

ORACLE sequence修改和刪除示例：

ALTER SEQUENCE [ schema. ]sequence
  { INCREMENT BY integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { CACHE integer | NOCACHE }
  | { ORDER | NOORDER }
  }
修改時的三個注意事項：
如果要使序列start with不同的數字，只能刪除序列重建。
如果在使用NEXTVAL初始化序列前改變INCREMENT BY的值，一些序列號會被跳過。解決跳過問題的方法--刪除重建
修改的各個引數的新值依然要滿足create sequence各引數介紹中的描述。
NEXTVAL初始化序列前改變INCREMENT BY的值示例：
create sequence bys.seq3
increment by 3
start with 5
maxvalue 18
nominvalue
cycle
cache 4;
BYS@ bys3>alter sequence bys.seq3 increment by 5;  --初始化前修改
Sequence altered.
BYS@ bys3>select seq3.nextval from dual;   --初始化時確實跳過了一些數字。。
   NEXTVAL
----------
         7
BYS@ bys3>select seq3.nextval from dual;
   NEXTVAL
----------
        12
示例修改語句：
ALTER SEQUENCE customers_seq MAXVALUE 1500;
ALTER SEQUENCE customers_seq CYCLE CACHE 5;
刪除序列語句：
DROP SEQUENCE [ schema. ]sequence_name ;
如：BYS@ bys3>drop sequence bys.seq2;
############################

ORACLE sequence使用示例  

詳見官方文件--http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#i1006157
序列常見使用場景：
可以在SELECT 語句，CREATE TABLE ... AS SELECT語句， CREATE MATERIALIZED VIEW ... AS SELECT中使用。
在UPDATE的SET中，在INSERT 的子句或VALUES中。序列可以由多個使用者同時訪問而不產生等待或鎖定。
第一次查詢要用 NEXTVAL，返回序列的初始值。
查詢當前序列號用：CURRVAL，返回的是最後一次引用NEXTVAL返回的值。
查詢下一個序列號用NEXTVAL--用此命令時，sequence會先增加1或increment by指定的值，然後返回sequence值
本實驗中的查詢：
BYS@ bys3>select test_seq.currval from dual;   ---未使用NEXTVAL初始化，故報此錯。
select test_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session
BYS@ bys3>select test_seq.nextval from dual;  第一次使用NEXTVAL，顯示的是建立時start with指定的值
   NEXTVAL
----------
         5
BYS@ bys3>select test_seq.currval from dual; 使用currval查到當前序列號---最後一次引用NEXTVAL返回的值
   CURRVAL
----------
         5
#######
BYS@ bys3>select test_seq.nextval from dual;   --一直執行nextval，觀察序列達到maxvalue指定的值後如何迴圈使用
   NEXTVAL
----------
        17
BYS@ bys3>select test_seq.nextval from dual;  --序列達到maxvalue指定的值後返回的是minvalue指定的值而不是start with了。如未指定minvalue或指定NOMINVALUE，則是返回1.
   NEXTVAL
----------
         4
########
BYS@ bys3>insert into test values(test_seq.nextval,'seqtest');   --使用INSERT語句呼叫序列
1 row created.
BYS@ bys3>select * from test;
OBJECT_NAME  STATUS
------------ -------
10           seqtest
BYS@ bys3>insert into test values(test_seq.currval,'seqtest');
1 row created.
BYS@ bys3>select * from test;
OBJECT_NAME  STATUS
------------ -------
10           seqtest
10           seqtest
BYS@ bys3>insert into test(object_name) select test_seq.nextval from dual;     --使用INSERT子語呼叫序列
1 row created.
BYS@ bys3>select * from test;
OBJECT_NAME  STATUS
------------ -------
10           13
10           16
4
###########
BYS@ bys3>update test set status=test_seq.nextval;    --使用UPDATE語句呼叫序列
2 rows updated.
BYS@ bys3>select * from test;
OBJECT_NAME  STATUS
------------ -------
10           13
10           16
BYS@ bys3>delete test where status=test_seq.currval;  --DELETE中不能使用sequence做條件
delete test where status=test_seq.currval
                                  *
ERROR at line 1:
ORA-02287: sequence number not allowed here
###################

利用解發器自動為表插入遞增序列：---類似自增欄位的作用

建觸發器程式碼為：
create or replace trigger tri_test_id
  before insert on test     --test 是表名
  for each row
declare
  nextid number;
begin
  IF :new.testid IS NULL or :new.testid=0 THEN --DepartId是列名
    select seq1.nextval     --seq1是提前建立好的序列的名字
    into nextid from sys.dual;
    :new.testid:=nextid;
  end if;

end tri_test_id;

---------------case when子句中使用SEQ時，

The statements in a WHEN clause can modify the database and call non-deterministic functions. There is no fall-through mechanism as in the C switch statement

Notice it doesn't say the statements in the "true" WHEN clause. So even if the when statement is false, the nextval will fire:

select
case when 1=0 then 'next ' || seq_id.nextval
     when 1=1 then 'curr ' || seq_id.currval
end col1
from dual;

示例如下：--即WHEN中條件為FALSE時也會觸發執行序列的NEXTVAL，但是CASE WHEN子句不返回值。

SQL> select test1_seq.currval from dual;

   CURRVAL
----------
        14

SQL> select case when 1=2 then test1_seq.nextval
  2     else null end  seqvalue,test1_seq.currval from dual;

  SEQVALUE    CURRVAL
---------- ----------
                   15

SQL> select test1_seq.currval from dual;

   CURRVAL
----------
        15


SQL> select case
  2    when 1=1 then test1_seq.nextval
  3    end  testseq,test1_seq.currval from dual;

   TESTSEQ    CURRVAL
---------- ----------
        20         20

SQL> select case
  2    when 1=2 then test1_seq.nextval
  3    end  testseq,test1_seq.currval from dual;

   TESTSEQ    CURRVAL
---------- ----------
                   21


參考：http://stackoverflow.com/questions/7810108/sequence-and-case-in-a-select-in-oracle

http://www.itpub.net/thread-1878625-1-1.html