Oracle数据库表自增列

–创建示例表
create table Student(
stuId number(9) not null,
stuName varchar2(20) not null,
stuMsg varchar2(50) null
)

– 创建序列 Student_stuId_Seq
create sequence Student_stuId_Seq
increment by 1
start with 1
minvalue 1
maxvalue 999999999;

– 更改序列 Student_stuId_Seq
alter sequence Student_stuId_Seq
increment by 2
minvalue 1
maxvalue 999999999;

–获取序列自增ID
select Student_stuId_Seq.Nextval 自增序列ID from dual;

– 删除序列
drop sequence Student_stuId_Seq;

–调用序列，插入Student数据
insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,‘张三’);
insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,‘李四’);
–查询插入的数据
select * from Student

–创建自增序列
CREATE SEQUENCE S_LOG_INFO_PRIMARYKEY_SEQUENCE
MINVALUE 1 --最小值
NOMAXVALUE --不设置最大值
START WITH 1 --从1开始计数
INCREMENT BY 1 --每次加1
NOCYCLE --一直累加，不循环
NOCACHE; --不建缓冲区

–创建触发器，插入数据时自动增加
CREATE OR REPLACE TRIGGER S_LOG_INFO_TRIGGER BEFORE INSERT ON S_LOG_INFO FOR EACH ROW WHEN(NEW.PRIMARYKEY IS NULL)
BEGIN
SELECT S_LOG_INFO_PRIMARYKEY_SEQUENCE.NEXTVAL INTO:NEW.PRIMARYKEY FROM dual;
END;

–添加数据测试
INSERT
INTO
S_LOG_INFO
(LOGSTATUS, USERACCOUNT, LOGTYPE, SYSTEMTYPE, CONTENT, FUN, USERNAME, MODUAL)
VALUES
(‘成功’,‘123’,‘系统日志’, ‘XXXXXXX’, ‘用户成功注销’, ‘doLogout’, ‘admin’,‘系统注销’)