oracle主键自增=序列+触发器
首先创建一张表:
create table employee(id number(4) primary key,
name varchar2(20) not null,
age number(4) check(age between 1 and 100),
sal number(8,2));
其次为id创建序列:
create or replace sequence EMPLOYEE_INCminvalue 1 --最小值
maxvalue 20000 --最大值
start with 1 --开始值
increment by 1; --自增值
此时可以使用EMPLOYEE_INC.NEXTVAL来为id做插入值:
insert into employee(id,name, age, sal) values(EMPLOYEE.NEXTVAL,'占三',19,200.3);
但每次都要写EMPLOYEE_INC.NEXTVAL实在很麻烦,所以我们可以用触发器简化一下:
编写触发器
create or replace tigger EMPINSERT
before insert on EMPLOYEE --触发在插入employee表之前
for each row
when (new.id is null)
begin
select EMPLOYEE.NEXTVAL into :new.id from dual; --将 EMPLOYEE.NEXTVAL的值直接赋给id
end EMPINSERT;
最后的成果:
insert into employee(name, age, sal) values('李四',19,200.3);