Oracle中实现自增列

Oracle中添加自增列（触犯器和序列实现）

 

CREATE OR REPLACE TRIGGER "触发器名"

BEFORE INSERT ON "HIS_MZ_SFMX" FOR EACH ROW

 

DECLARE last_Sequence NUMBER;

         last_InsertID NUMBER;

BEGIN

   IF (:NEW."ID" IS NULL) THEN --new代表新行!如果是insert的话,就是刚或者将要insert的那一行!用:new.字段名可以得到每一列的值

      SELECT "序列名".NEXTVAL INTO :NEW."ID" FROM DUAL;

   ELSE

      SELECT Last_Number-1 INTO last_Sequence FROM User_Sequences WHERE UPPER(Sequence_Name) = UPPER('序列名');

      SELECT :NEW."ID" INTO last_InsertID FROM DUAL;

      WHILE (last_InsertID > last_Sequence)

      LOOP

         SELECT "序列名".NEXTVAL INTO last_Sequence FROM DUAL;

         END LOOP;

   END IF;

END;

 

 

注释：序列的声明

-- Create sequence

create sequence序列名

minvalue 1

maxvalue 9999999999999999999999999999

start with 500137

increment by 1

nocache

order;