Oracle 9i 實現自增欄位的最佳實踐：Sequence + Trigger 封裝存儲過程

設計思路

Oracle 無原生自增欄位，通過 Sequence 產生遞增值，配合 Before Insert Trigger 自動賦值給目標欄位，並封裝為可重用存儲過程。

核心代碼

-- 1. 建立自增欄位建立存儲過程
CREATE OR REPLACE PROCEDURE pr_CreateIdentityColumn (
  p_table_name  VARCHAR2,
  p_column_name VARCHAR2
) AS
  v_sql VARCHAR2(1000);
BEGIN
  -- 建立 Sequence
  v_sql := 'CREATE SEQUENCE seq_' || p_table_name ||
           ' MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE';
  EXECUTE IMMEDIATE v_sql;

  -- 建立 Trigger
  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;
END;
/

-- 2. 授權動態 SQL 所需權限（即使用戶已有權限也需顯式授權）
GRANT CREATE ANY SEQUENCE TO "UserName";
GRANT CREATE ANY TRIGGER TO "UserName";

-- 3. 重新編譯存儲過程
ALTER PROCEDURE pr_CreateIdentityColumn COMPILE;

-- 4. 調用範例
EXEC pr_CreateIdentityColumn('sdspdept', 'deptid');
EXEC pr_CreateIdentityColumn('sdspuser', 'userid');
-- ... 更多表

關鍵注意點

• 動態 DDL 需 CREATE ANY SEQUENCE/TRIGGER 系統權限，GRANT 時用戶名區分大小寫。
• Sequence 設 NOCACHE 避免實例重啟導致間隙；若允許間隙可改用 CACHE 20 提升性能。
• Trigger 使用 :NEW.column 直接賦值，無需額外 SELECT ... INTO 變數，寫法更簡潔。