Oracle 数据库表中创建ID自增列

1  首先创建一个递增序列

-- Create sequence
create sequence SEQ_TABLE
minvalue 100001
maxvalue 9999999999999999999999999999
start with 100001
increment by 1
cache 20;

 

2 在要加递增列的表上创建触发器

CREATE OR REPLACE TRIGGER TRIG_TABLE
BEFORE INSERT ON TABLE
FOR EACH ROW
DECLARE
  tmpVar NUMBER;
BEGIN
  tmpVar := 0;
  Select SEQ_TABLE.NextVal into tmpVar from dual;
  :new.ID := tmpVar;

EXCEPTION
  WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
END;