Oracle 12c 前無 IDENTITY 欄位,主鍵自增需自行建立 Sequence 與 Before Insert Trigger。12c 起可直接使用 GENERATED AS IDENTITY。
CREATE SEQUENCE seq_table_name INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE OR REPLACE TRIGGER trg_table_name BEFORE INSERT ON table_name FOR EACH ROW WHEN (NEW.pk_column IS NULL) BEGIN SELECT seq_table_name.NEXTVAL INTO :NEW.pk_column FROM dual; END; /
COMMIT; (DDL 隱性提交,此行可省略)。CREATE TABLE t ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(100) );
GENERATED ALWAYS:不可手動指定 ID。GENERATED BY DEFAULT:允許手動指定,未給值才自動取序號。USER_TAB_IDENTITY_COLS 查詢。CACHE 20 (或更大) 減少 Row Cache Lock 爭用。ORDER 保證序號單調遞增(RAC 環境需求)。INSERT /*+ APPEND */ ... SELECT seq.NEXTVAL ... 直寫。