Oracle 9i 實現自動遞增欄位 (Identity Column) 的最佳實務

核心機制：Sequence + Trigger

Oracle 無原生 Identity 欄位，標準做法建立 Sequence 與 Before Insert Trigger。

自動化建立預存程序

CREATE OR REPLACE PROCEDURE pr_CreateIdentityColumn
(tablename VARCHAR2, columnname VARCHAR2)
AS
  strsql VARCHAR2(1000);
BEGIN
  strsql := 'CREATE SEQUENCE seq_'||tablename||' MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE';
  EXECUTE IMMEDIATE strsql;
  strsql := 'CREATE OR REPLACE TRIGGER trg_'||tablename||' BEFORE INSERT ON '||tablename||
            ' FOR EACH ROW BEGIN SELECT seq_'||tablename||'.NEXTVAL INTO :new.'||columnname||' FROM dual; END;';
  EXECUTE IMMEDIATE strsql;
END;
/

權限授與 (動態 SQL 需顯式授權)

GRANT CREATE ANY SEQUENCE TO "UserName";
GRANT CREATE ANY TRIGGER TO "UserName";
-- 使用者名稱區分大小寫

重新編譯並呼叫

EXEC pr_CreateIdentityColumn('sdspdept','deptid');
EXEC pr_CreateIdentityColumn('sdspuser','userid');
-- 依此類推

此法簡潔可重複使用，適合批量建立自增欄位。