在 Oracle 資料庫開發中,Sequence(序列)常被用來產生主鍵值。隨著專案推進,有時需要將序列重置為特定數字。傳統做法是 DROP 後 CREATE,但這會導致依賴該序列的函數、存儲過程失效,需重新編譯,風險極高。本文介紹兩種「不刪除重建」的重置技巧,並提供可直接部署的 PL/SQL 存儲過程。
Oracle 序列的 INCREMENT BY 可為正數也可為負數。重置邏輯如下:
NEXTVAL 得到目前值 currINCREMENT BY 設為 -(curr - target)(負值),再取一次 NEXTVAL,序列會「倒退」到目標值INCREMENT BY 改回正常步長(通常為 1)此法不動結構、不失效依賴物件、極低風險。
適用於一次性維護作業,可直接在 SQL*Plus 或開發工具執行:
DECLARE
n NUMBER(10);
v_startnum NUMBER(10) := 10000001; -- 目標起始值
v_step NUMBER(10) := 1; -- 正常步長
tsql VARCHAR2(200);
v_seqname VARCHAR2(200) := 'MIP_JF_SEQUENCE'; -- 你的序列名
BEGIN
-- 1. 取得目前值
EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM DUAL' INTO n;
-- 2. 計算需「倒退」的步長
n := v_startnum - n - v_step;
-- 3. 臨時調整步長並取值(倒退到目標值)
tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY ' || n;
EXECUTE IMMEDIATE tsql;
EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM DUAL' INTO n;
-- 4. 恢復正常步長
tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY ' || v_step;
EXECUTE IMMEDIATE tsql;
END;
/
將邏輯封裝為 SEQ_RESET,未來只需 EXEC SEQ_RESET('序列名') 即可一鍵歸零:
CREATE OR REPLACE PROCEDURE seq_reset (v_seqname VARCHAR2) AS
n NUMBER(10);
tsql VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM DUAL' INTO n;
n := -(n - 1); -- 計算負步長,使下一值變為 1
tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY ' || n;
EXECUTE IMMEDIATE tsql;
EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM DUAL' INTO n; -- 觸發倒退
tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY 1'; -- 恢復步長
EXECUTE IMMEDIATE tsql;
END seq_reset;
/
SQL> CREATE SEQUENCE seq_1 INCREMENT BY 1 START WITH 1 MAXVALUE 999999999;
SQL> SELECT seq_1.NEXTVAL FROM DUAL; -- 2
SQL> SELECT seq_1.NEXTVAL FROM DUAL; -- 3
SQL> EXEC seq_reset('seq_1');
SQL> SELECT seq_1.CURRVAL FROM DUAL; -- 1 ✅ 已重置
ALTER SEQUENCE 權限。v_target_val 參數,支援重置到任意數字,而非固定歸零。透過動態調整 INCREMENT BY 參數,我們能在不刪除序列、不影響依賴物件的前提下,安全、快速地將序列重置為任意指定值。建議將 SEQ_RESET 存儲過程納入資料庫維護工具箱,成為 DBA 與開發人員的標準作業程序。