--用户表
create table userInfo(
customerID number primary key,
customerName varchar2(20) not null,
PID varchar2(18) not null unique check(length(PID) in(15,18)),
telephone varchar2(11) not null check(length(telephone) = 11),
Address varchar2(40)
)
--自增序列
create sequence seq_userInfo
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20
--触发器
create or replace trigger tgr_userInfo
before insert on userInfo
for each row
declare
begin
if inserting and :new.customerID is null then
:new.customerID := seq_userInfo.nextval;
end if;
end tgr_userInfo;如上,当给userInfo表插入错误数据时,序列自增了,下次插入就跳号了
create table userInfo(
customerID number primary key,
customerName varchar2(20) not null,
PID varchar2(18) not null unique check(length(PID) in(15,18)),
telephone varchar2(11) not null check(length(telephone) = 11),
Address varchar2(40)
)
--自增序列
create sequence seq_userInfo
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20
--触发器
create or replace trigger tgr_userInfo
before insert on userInfo
for each row
declare
begin
if inserting and :new.customerID is null then
:new.customerID := seq_userInfo.nextval;
end if;
end tgr_userInfo;如上,当给userInfo表插入错误数据时,序列自增了,下次插入就跳号了
2. flush shared pool
等情况,数据库都会出现seq跳号如果把cache设置为0,是可以从一定程度上解决这个问题,但是这个会导致另外的问题:性能下降明显,每一次使用seq 都会更新seq$表,如果数量多,性能非常差如果一定要求连续,建议应用上控制,不要依赖数据库的seq