SEQUENCE序列(流水號)物件使用

現行資料庫系統均提供流水序號（Sequential  Number）以供編製代理鍵(Surrogate Key)使用，MSSQL 2008(含)以下版本僅提供資料表的整數欄位中設定IDENTITY屬性，當新增一筆資料列（ROW）時，則該欄位將自動產生序號，ORACLE與MSSQL（2012以上）則提供資料庫層級之「SEQUENCE(序列物件) 」，可由此物件取得流水序號，以下基本列出優劣比較，如下表。本文將探討SEQUENCE(序列物件)的使用。

Object	MSSQL	ORACLE	LEVEL	說明
SEQUENCE	2016		Database	l   資料庫層級物件，無需依賴任何資料表。 l   可提供多個資料表使用，亦可一個資料表中使用多組SEQUENCE(N:N) 。 l   資料轉移方便(如不同廠區/分公司)，可分別定義同名但不同值域的SEQUENCE。 l   SEQUENCE可循環使用(環狀)。 l   INSERT INTO資料表時需指定SEQUENCE物件。
IDENTITY		19C	Table	l   資料表層級物件，與資料表相依。 l   一個Table僅支援一組，使用簡便，由於為資料表欄位屬性，因此移轉時不用額外處理。 l   資料移轉時可能發生重覆問題(如複寫、匯入匯出、資料合併備份)需額外處置。

ORACLE與MSSQL（2012以上）對於SEQUENCE在CREATE或ALTER上語法上雷同(幾乎通用)，以下分別CREATE及ALTER語法，本文僅列出探討常用參數及語法，如需深入探討研究則請參考官網。

CREATE語法

ORACLE /MSSQL2012	說明
CREATE SEQUENCE schema.sequence_name [INCREMENT BY interval] [START WITH first_num] [MAXVALUE max_val] [MINVALUE min_val] [CYCLE | NOCYCLE] [CACHE cache_size | NOCACHE] [ORDER | NOORDER] --註: 僅ORACLE支援 註: 1.  僅ORACLE支援[ NOORDER | ORDER ]。設為ORDER時，將保證序號依請求順序產生，但無論使用任何選項，均不影響唯一性。 2.  MAXVALUE及MINVALUE參數對應設定NOMAXVALUE、NOMINVALUE，兩種資料庫略有不同。	l   INCREMENT BY interval 設定序號遞增或遞減間隔，interval為整數，預設為1。 l   START WITH first_num   設定第一個序號起始數值，預設為1 l   MAXVALUE max_val     設定自動產生序號的最大值 l   MINVALUE min_val      設定自動產生序號的最小值 l   CYCLE|NOCYCLE  設定序號到達最大值或最小值設定時，循環或不產生序號，預設為NOCYCLE l   CACHE n|NOCACHE 設定一組數值將事先配置並保留於記憶體中使用，以加速執行效能，資料庫非預期結束時可能導致留存於快取中序號遺失。ORACLE預設數目為20，MSSQL未說明。

DROP語法

ORACLE /MSSQL2012	說明
DROP SEQUENCE schema.sequence_name	兩者語法相同

資料庫	語法	說明
ORACLE	ALTER SEQUENCE [schema.]sequence_name [ INCREMENT BY interval ] --[START WITH first_num] (不允許變更) [ MAXVALUE max_val | NOMAXVALUE ] [ MINVALUE min_val | NOMINVALUE ] [ NOCYCLE | CYCLE ] [ CACHE cache | NOCACHE ] [ NOORDER | ORDER ];	l   不允變更起始序號
MSSQL	ALTER SEQUENCE [schema_name. ] sequence_name  [ RESTART [ WITH first_num ] ] [ INCREMENT BY interval ]  [ MAXVALUE max_val | NO MAXVALUE ] [ MINVALUE min_val | NO MINVALUE ]  [ CYCLE |  NO CYCLE ]  [ { CACHE [ ] } | { NO CACHE } ]  [ ; ]	l   MSSQL允許變更起始序號，但改成RESTART。

以下進行測試

	ORACLE	MSSQL 2012
CREATE	--FAB 12A: 1000000001~1999999999 --FAB 12B: 2000000001~2999999999  CREATE SEQUENCE SEQ_LOT INCREMENT BY 1 START WITH   1000000001 MAXVALUE     1999999999 MINVALUE     1000000001 CYCLE	--FAB 12A: 1000000001~1999999999 --FAB 12B: 2000000001~2999999999  CREATE SEQUENCE SEQ_LOT INCREMENT BY 1 START WITH   1000000001 MAXVALUE     1999999999 MINVALUE     1000000001 CYCLE
OBJECT	SELECT * FROM ALL_SEQUENCES	SELECT * FROM sys.sequences
NEXT_VAL	--1.搭配INSERT INTO使用 INSERT INTO LOT     (LOT_ID, LOT_NO, PROD)       VALUES (SEQ_LOT.NEXTVAL, 'S4001', '4G')  --2.使用SELECT (僅是demo用) --實際應用時，勿用程式先抓取序號再寫入資料庫， --如欲取得序號可搭配RETURNING直接回傳 SELECT SEQ_LOT.NEXTVAL FROM DUAL	--1. 搭配INSERT INTO 使用 INSERT INTO LOT    (LOT_ID, LOT_NO, PROD) VALUES (NEXT VALUE FOR SEQ_LOT, 'S4001', '4G')  -- 2.使用SELECT(僅demo用) -- 實際應用時，勿用程式先抓取序號再寫入資料庫 -- 如欲取得序號，可搭配OUTPUT回傳結果 SELECT NEXT VALUE FOR SEQ_LOT
CURR_VAL	SELECT SEQ_LOT.CURRVAL FROM DUAL	SELECT current_value FROM sys.sequences WHERE name ='SEQ_LOT'

另外，許多舊有設計採搜索資料表序號欄位中最大值，再將此值加1，以做為下一個流水編號，此作法需進行額外鎖定(Lock)而造成效能問題，甚至發生重複編碼問題，應避免此種設計模式，如欲解決取得寫入流水序號問題，可參考《取得目前自動編號值》文章中所述。