Oracle 自增欄位實現方案：Sequence + Trigger 封裝為存儲過程，一鍵生成標準化自增鍵

前言：Oracle 無原生自增，但標準模式早已成熟

不同於 SQL Server/MySQL 的 IDENTITY/AUTO_INCREMENT，Oracle 採用 Sequence (序列) + Trigger (觸發器) 模式實現自增主鍵。為避免重複手工建立、權限遺漏、命名不規範，本文提供通用存儲過程封裝方案，實現「一鍵建表自增欄位、自動授權、命名規範、即插即用」。

核心機制：Sequence + BEFORE INSERT Trigger

-- 手動建立模式 (單表)
CREATE SEQUENCE seq_emp MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE OR REPLACE TRIGGER trg_emp
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
  SELECT seq_emp.NEXTVAL INTO :NEW.emp_id FROM dual;
END;
/

關鍵點：
• NOCACHE 確保無間隙 (RAC 環境建議 CACHE 1000 + ORDER 平衡效能與有序性)
• BEFORE INSERT FOR EACH ROW 行級觸發，攔截插入前賦值
• :NEW.col 引用新行欄位

通用存儲過程：自動化建立 Sequence + Trigger

CREATE OR REPLACE PROCEDURE pro_create_seq_col (
  p_table_name  VARCHAR2,  -- 表名
  p_column_name VARCHAR2   -- 自增欄位名
) AS
  v_sql VARCHAR2(2000);
BEGIN
  -- 1. 建立 Sequence (命名規範: SEQ_<表名>)
  v_sql := 'CREATE SEQUENCE seq_' || p_table_name ||
           ' MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE';
  EXECUTE IMMEDIATE v_sql;

  -- 2. 建立 Trigger (命名規範: TRG_<表名>)
  v_sql := 'CREATE OR REPLACE TRIGGER trg_' || p_table_name ||
           ' BEFORE INSERT ON ' || p_table_name ||
           ' FOR EACH ROW BEGIN ' ||
           'SELECT seq_' || p_table_name || '.NEXTVAL INTO :NEW.' || p_column_name || ' FROM dual; END;';
  EXECUTE IMMEDIATE v_sql;

  DBMS_OUTPUT.PUT_LINE('自增欄位建立完成: 表=' || p_table_name || ', 欄位=' || p_column_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
    RAISE;
END;
/

必要權限授予 (動態 SQL 需顯式授權)

-- 執行者需具備 (或由 DBA 授予)
GRANT CREATE SEQUENCE TO <用戶>;
GRANT CREATE TRIGGER TO <用戶>;
-- 若在其他 Schema 建表，需 CREATE ANY SEQUENCE / CREATE ANY TRIGGER

使用範例：批次建立多表自增欄位

BEGIN
  pro_create_seq_col('sb_zsxx', 'zsxh');   -- 證書信息表
  pro_create_seq_col('sb_sbxx', 'sbxh');   -- 設備信息表
  pro_create_seq_col('sb_jkx', 'pzxh');    -- 監控信息表
  pro_create_seq_col('sdspfp', 'sysfpid'); -- 發票表
END;
/
-- 或單獨調用
EXEC pro_create_seq_col('employees', 'emp_id');

進階增強版：支援緩存、有序、備註、異常處理

CREATE OR REPLACE PROCEDURE pro_create_seq_col_v2 (
  p_table_name   VARCHAR2,
  p_column_name  VARCHAR2,
  p_cache_size   NUMBER   DEFAULT 20,      -- 緩存大小 (RAC 建議 1000+)
  p_ordered      BOOLEAN DEFAULT FALSE,    -- 是否保證有序 (RAC 必須 TRUE)
  p_comments     VARCHAR2 DEFAULT NULL     -- 註釋
) AS
  v_seq_name  VARCHAR2(128) := 'SEQ_' || UPPER(p_table_name);
  v_trg_name  VARCHAR2(128) := 'TRG_' || UPPER(p_table_name);
  v_sql       VARCHAR2(4000);
BEGIN
  -- 檢查表/欄位存在
  IF NOT EXISTS (SELECT 1 FROM user_tables WHERE table_name = UPPER(p_table_name)) THEN
    RAISE_APPLICATION_ERROR(-20001, '表不存在: ' || p_table_name);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM user_tab_columns WHERE table_name = UPPER(p_table_name) AND column_name = UPPER(p_column_name)) THEN
    RAISE_APPLICATION_ERROR(-20002, '欄位不存在: ' || p_column_name);
  END IF;

  -- 建立 Sequence
  v_sql := 'CREATE SEQUENCE ' || v_seq_name ||
           ' MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 ' ||
           CASE WHEN p_cache_size > 0 THEN 'CACHE ' || p_cache_size ELSE 'NOCACHE' END ||
           CASE WHEN p_ordered THEN ' ORDER' ELSE ' NOORDER' END;
  EXECUTE IMMEDIATE v_sql;

  -- 註釋
  IF p_comments IS NOT NULL THEN
    EXECUTE IMMEDIATE 'COMMENT ON SEQUENCE ' || v_seq_name || ' IS ''' || p_comments || '''';
  END IF;

  -- 建立 Trigger
  v_sql := 'CREATE OR REPLACE TRIGGER ' || v_trg_name ||
           ' BEFORE INSERT ON ' || p_table_name ||
           ' FOR EACH ROW BEGIN ' ||
           'SELECT ' || v_seq_name || '.NEXTVAL INTO :NEW.' || p_column_name || ' FROM dual; END;';
  EXECUTE IMMEDIATE v_sql;

  DBMS_OUTPUT.PUT_LINE('完成: ' || v_seq_name || ' + ' || v_trg_name || ' -> ' || p_table_name || '.' || p_column_name);
EXCEPTION
  WHEN OTHERS THEN
    -- 回滾已建立的 Sequence
    BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE ' || v_seq_name; EXCEPTION WHEN OTHERS THEN NULL; END;
    RAISE;
END;
/

12c+ 新特性：IDENTITY 欄位 (原生自增)

-- Oracle 12c 起支援標準 IDENTITY
CREATE TABLE emp (
  emp_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NOCACHE),
  ename  VARCHAR2(50)
);
-- 或 GENERATED BY DEFAULT ON NULL (允許手動指定值)
CREATE TABLE emp (
  emp_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  ename  VARCHAR2(50)
);

建議：12c+ 新建表優先使用 IDENTITY，舊版/遷移項目使用 Sequence+Trigger 方案。

總結

Oracle 自增欄位的標準解法是 Sequence + Trigger。通過存儲過程封裝，可實現命名規範統一、權限前置檢查、批次部署、版本演進 (12c IDENTITY 兼容)。請將 pro_create_seq_col 納入團隊 DBA 工具箱，新建表執行一條 EXEC 即可完成主鍵自增配置，消除手工錯誤，提升交付效率。