oraclesequence是資料庫系統按照一定規則自動增加的數字序列。
基本介紹
簡介,操作方法,創建,使用,
簡介
Sequence是資料庫系統的特性，有的資料庫有Sequence，有的沒有。比如Oracle、DB2、PostgreSQL資料庫有Sequence，MySQL、SQL Server、Sybase等資料庫沒有Sequence。
Sequence是數據中一個特殊存放等差數列的表，該表受資料庫系統控制，任何時候資料庫系統都可以根據當前記錄數大小加上步長來獲取到該表下一條記錄應該是多少，這個表沒有實際意義，常常用來做主鍵用，非常不錯，呵呵，不過很鬱悶的各個資料庫廠商各有各的一套對Sequence的定義和操作。在此我對常見三種資料庫的Sequence的定義和操作做一個對比和總結，以便日後查看。
操作方法
創建
Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE許可權，
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 — 每次加幾個
START WITH 1 — 從1開始計數
NOMAXVALUE — 不設定最大值
NOCYCLE — 一直累加，不循環
CACHE 10;
一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的當前值
NEXTVAL=增加sequence的值，然後返回 sequence 值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL
可以使用sequence的地方：
- INSERT語句的子查詢中
- INSERT語句的VALUES中
- UPDATE 的 SET中
可以看如下例子：
INSERT INTO emp VALUES (empseq.nextval, ‘LEWIS’, ‘CLERK’,7902, SYSDATE, 1200, NULL, 20);
SELECT empseq.currval FROM DUAL;
但是要注意的是：
– 如果指定CACHE值，ORACLE就可以預先在記憶體裡面放置一些sequence，這樣存取的快些。cache裡面的取完後，oracle自動再取一組到cache。 使用cache或許會跳號， 比如資料庫突然不正常down掉（shutdown abort)，cache中的sequence就會丟失， 所以可以在create sequence的時候用nocache防止這種情況。
Alter Sequence
你或者是該sequence的owner，或者有ALTER ANY SEQUENCE 許可權才能改動sequence。 可以alter除start至以外的所有sequence參數。如果想要改變start值，必須 drop sequence 再 re-create 。
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE — 到10000後從頭開始
NOCACHE ;
影響Sequence的初始化參數：
SEQUENCE_CACHE_ENTRIES =設定能同時被cache的sequence數目。
可以很簡單的Drop Sequence
DROP SEQUENCE order_seq;
Sequence與indentity的區別與聯繫
Sequence與indentity的基本作用都差不多。都可以生成自增數字序列。
使用
使用 NEXTVAL
第一次訪問一個序列，在引用 sequence.CURRVAL 之前必須先引用 sequence.NEXTVAL。第一次引用 NEXTVAL，返回序列的初始值。後面每次引用 NEXTVAL，用已定義的 step 增加序列值並返回序列新的增加以後的值。
在一個 SQL 語句中只能對給定的序列增加一次。即使在一個語句中多次指定 sequence.NEXTVAL，序列也只增加一次，所以每次 sequence.NEXTVAL 出現在同一 SQL 語句中返回相同的值。除了在同一語句中多次出現這種情況以外，每個sequence.NEXTVAL表達式都會增加序列，無論後來是否提交或回滾當前事務。如果在最終回滾的事務中指定sequence.NEXTVAL，某些序列數可能被跳過。
如在PL/SQL中：
查詢nextval的值等於151
select cheng.nextval from test1234
執行insert語句
insert into test1234 values(cheng.nextval,’bb’,22);
commit或rollback後再查詢nextval的值會增加到153
使用 CURRVAL
如在PL/SQL中：
select cheng.nextval,cheng.currval from test1234
nextval和currval的值都是160
序列的並發訪問
序列總是在資料庫中生成唯一值，即使當多個用戶並發地引用同一序列時也沒有可察覺的等待或鎖定。當多個用戶使用 NEXTVAL 來增長序列時，每個用戶生成一個其他用戶不可見的唯一值。當多個用戶並發地增加同一序列時，每個用戶看到的值是有差異的。例如，一個用戶可能從一個序列生成一組值，如 11、14、16 和 18，而另一個用戶並發地從同一序列生成值 12、13、15 和 17。
sequence使用的限制
NEXTVAL 和 CURRVAL 只在 SQL 語句中有效，並不在 SPL 語句中直接有效。(但是使用NEXTVAL 和CURRVAL的SQL語句可用於SPL例程)以下限制套用於 SQL 語句中的這些運算符：
[1]在 CREATE TABLE 或 ALTER TABLE 語句中，在下列上下文中不能指定 NEXTVAL 或 CURRVAL：
在 DEFAULT 子句中。
在檢查約束中。
[2]在 SELECT 語句中，下列上下文中不能指定 NEXTVAL 或 CURRVAL：
使用 DISTINCT 關鍵字時在投影列表中。
在 WHERE、GROUP BY 或 ORDER BY 子句中。
在子查詢中。
在 UNION 運算符結合 SELECT 語句時。
[3]在下列這些上下文中也不能指定 NEXTVAL 或 CURRVAL：
在分段存儲表達式中
在對另一個資料庫中的遠程式列對象的引用中。
Oracle中實現類似自動增加 ID 的功能
我們經常在設計資料庫的時候用一個系統自動分配的ID來作為我們的主鍵，但是在ORACLE 中沒有這樣的 功能，我們可以通過採取以下的功能實現自動增加ID的功能。
1.首先創建 sequence
create sequence seqmax increment by 1
2.使用方法
select seqmax.nextval id from dual
就得到了一個和ms sql的自動增加ID相同的功能id值
數字序列
定義一個seq_test，最小值為1，最大值為99999999999999999，從1開始，增量的步長為1，快取為20的循環排序Sequence。
Oracle的定義方法：
create sequence seq_test
minvalue 1
maxvalue 99999999999999999
startwith 1
increment by 1
cache 20
cycle
order;
DB2的寫法：
create sequence seq_test
as bigint
startwith 20000
increment by 1
minvalue 10000
maxvalue 99999999999999999
cycle
cache 20
order;
PostgreSQL的寫法：
create sequence seq_test
increment by 1
minvalue 10000
maxvalue 99999999999999999
start20000
cache 20
cycle;
下面分別從三個資料庫的Sequence中獲取nextval的值。
Oracle中：seq_test.nextval
例如：select seq_test.nextval from dual;
DB2中：nextval for SEQ_TOPICMS
例如：values nextval for seq_test;
PostgreSQL中：nextval(seq_test)
例如：select nextval(seq_test);
