[Oracle] 指定或修改 Sequence/Trigger Pair 最新序號值

因專案關係，這一陣子比較有機會長期接觸 Oracle。Oracle 雖然功能強大，但很多功能在操作上不是非常方便，尤其是對初學者來說，若沒有一個讓人親近的工具，真的很難對它保有熱情。不過還好在朋友的推薦下，使用 Dell 開發的 Toad for Oracle，一用之下大驚！原來 Oracle 的世界也有一個媲美 SQL Server Management Studio 這套相對應的工具，而且感覺更加強大。

不過離題了。這次不是要記錄 Toad 多強，而是遇到了序列問題。
新版 Oracle 12 不太清楚，但就我使用 9.2 的經驗中得知 Oracle 並沒有像 SQL Server 一樣提供自動識別編號的功能，雖說如此，但仍可利用 Sequence 及 Trigger 搭配達到相同的效果。當然，這要寫 SQL 的，但透過 Toad，其實非常容易達到：

CREATE OR REPLACE TRIGGER USER.TEST_TRG
BEFORE INSERT
ON USER.TEST
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
  :new.ID := TEST_SEQ.nextval;
END TEST_TRG;

建完 Sequence/Trigger Pair 後資料表執行一段時間，若突然有「直接跳號到指定編號」需求時，這時就得要動些手腳了：

首先，要先知道目前已知的編號 (如: 168) 及目標編號 (如: 210) 是多少

select max(ID) from TEST;  --取得: 168                                   --或
select TEST_SEQ.currval from dual; --使用 currval 取得當前 Sequence 值

換算兩者差距，將其設為 Increment (如: 210-168 + 1 = 41)

alter sequence TEST_SEQ increment by 41;

執行 Sequence，直接跳號到 209

select TEST_SEQ.nextval from dual; --跳號到 209

接著將 Increment 改回 1

alter sequence TEST_SEQ increment by 1;

接下來新增的序號就從 210 開始編囉