oracle创建自增主键
1、创建序列
CREATE SEQUENCE SEQ_XXX
MINVALUE 1 --最小值
NOMAXVALUE --不设置最大值
START WITH 1 --从1开始计数
INCREMENT BY 1 --每次加1个
NOCYCLE --一直累加,不循环
NOCACHE; --不建缓冲区
此时可直接使用 insert into TABLE_XXX values (SEQ_XXX.nextval,‘张三’);
2、创建触发器
CREATE OR REPLACE TRIGGER TG_XXX
BEFORE INSERT ON TABLE_XXX
FOR EACH ROW
WHEN (NEW.PK_RRN IS NULL)
BEGIN
SELECT SEQ_XXX.NEXTVAL INTO :NEW.PK_RRN FROM DUAL;
END;
恢复序列的值为1:
-- 1、查询,假设得到结果 146
select SEQ_XXX.nextval from dual;
-- 2、increment by -145,注意是 -(n-1)
alter sequence SEQ_XXX increment by -145;
-- 3、再查一遍,重置为1
select SEQ_XXX.nextval from dual;
-- 4、递增还原为1
alter sequence SEQ_XXX increment by 1;