在Oracle中实现自增列的两种方式。

测试表：

CREATE TABLE UserInfo(
id NUMBER(10) NOT NULL,
username VARCHAR2(15) NOT NULL,
CONSTRAINTS PF_UserInfo PRIMARY KEY(Id)
);

显示调用序列的下一个值插入：

SQL语句：

-- 创建自增序列
CREATE SEQUENCE Tab_UserInfo_Sequence
START WITH 1
MINVALUE 1
MAXVALUE 999999999
INCREMENT BY 1
CACHE 20;

-- 插入值
INSERT INTO USERINFO values(Tab_UserInfo_Sequence.nextval,'aaa');
INSERT INTO USERINFO values(Tab_UserInfo_Sequence.nextval,'bbb');
SELECT * FROM UserInfo;

结果：

序列+触发器：

SQL语句：

-- 创建自增序列
CREATE SEQUENCE Tab_UserInfo_Sequence
START WITH 1
MINVALUE 1
MAXVALUE 999999999
INCREMENT BY 1
CACHE 20;

-- 创建触发器
DROP TRIGGER Tig_UserInfo_Insert;
CREATE TRIGGER Tig_UserInfo_Insert
BEFORE INSERT 
ON UserInfo
FOR EACH ROW
BEGIN 
	 SELECT Tab_UserInfo_Sequence.nextval into :new.id FROM dual;
END;

INSERT INTO UserInfo(username) values('zs');
INSERT INTO UserInfo(username) values('ls');
SELECT * FROM UserInfo;

结果：