2020年5月7日 星期四

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


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

Database
l   資料庫層級物件,無需依賴任何資料表。
l   可提供多個資料表使用,亦可一個資料表中使用多組SEQUENCE(N:N)
l   資料轉移方便(如不同廠區/分公司),可分別定義同名但不同值域的SEQUENCE
l   SEQUENCE可循環使用(環狀)
l   INSERT INTO資料表時需指定SEQUENCE物件。
IDENTITY

19C
l   資料表層級物件,與資料表相依。
l   一個Table僅支援一組,使用簡便,由於為資料表欄位屬性因此移轉時不用額外處理。
l   資料移轉時可能發生重覆問題(如複寫、匯入匯出、資料合併備份)需額外處置。

ORACLEMSSQL2012以上)對於SEQUENCECREATEALTER上語法上雷同(幾乎通用)以下分別CREATEALTER語法本文僅列出探討常用參數及語法如需深入探討研究則請參考官網
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.  MAXVALUEMINVALUE參數對應設定NOMAXVALUENOMINVALUE兩種資料庫略有不同。
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預設數目為20MSSQL未說明

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


資料庫
語法
說明
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   不允變更起始序號
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)而造成效能問題,甚至發生重複編碼問題應避免此種設計模式,如欲解決取得寫入流水序號問題,可參考取得目前自動編號值》文章中所述。

沒有留言:

張貼留言