Oracle 设置ID自增

郭顺发

发布于 2023-07-07 11:34:49

5240

发布于 2023-07-07 11:34:49

1. 先有个表

# 首先得有个表
create table t_user(
user_id number not null primary key,
user_name varchar2(30),
credits number,
user_password varchar2(32),
last_visit date,
last_ip varchar2(23)
)

2. 创建一个序列

转载自：https://blog.csdn.net/jiejie5945/article/details/44198283

# 为这个表创建序列
create sequence T_USER_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 21
increment by 1
cache 20;

参数描述：

create sequence seq_name：创建序列，seq_name为序列名称
minvalue：自增最小值，缺省值为nominvalue，即不设置最小值。
maxvalue：自增最大值，缺省值为nomaxvalue，即不设置最大值；系统能产生的最大值为10的27次方。
start with：自增开始值，设置成21则从21开始自增。
increment by：自增数值，设置成1则每次递增1，负数表示递减，缺省值为1。
cache：定义缓存序列的个数，缺省值为20，nocache表示不设置缓存；使用缓存可以提高序列的性能，但数据库出错时会造成数据丢失使序列不连续。
NOCYCLE：一直累加，不循环
cycle：定义当序列达到最大/小值后是否循环，缺省值为不循环；nocycle：不循环；**cycle：循环；如果不使用循环达到限制值后继续产生新值就会出错；使用循环达到最大值后的下一个值为1，和start** **with**设置的值无关，递增还是increment **by**设置的值；

2.1. 序列详细描述

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL CURRVAL = 返回sequence的当前值 NEXTVAL = 增加sequence的值，然后返回sequence值比如： emp_sequence.CURRVAL emp_sequence.NEXTVAL

可以使用sequence的地方：

不包含子查询、snapshot、VIEW的 SELECT 语句
INSERT语句的子查询中
NSERT语句的VALUES中
UPDATE 的 SET中

可以看如下例子：

INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); 

SELECT empseq.currval FROM DUAL;

但是要注意的是：

第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前sequence的值，但是在第一次NEXTVAL 初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次sequence的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？
如果指定CACHE值，oracle就可以预先在内存里面放置一些sequence，这样存取的快些。
cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号，比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。

2.2. 调整序列

你或者是该sequence的owner，或者有ALTER ANY sequence权限才能改动sequence。可以alter除start值之外的所有sequence参数。如果想要改变start值，必须drop sequence 再re-create。例子：

ALTER sequence emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE -- 到10000后从头开始
NOCACHE;

影响sequence的初始化参数： sequence_CACHE_ENTRIES = 设置能同时被cache的sequence数目。

可以很简单的Drop sequence DROP sequence order_seq;

3. 创建一个触发器

create or replace trigger t_user_tr
before insert on t_user
for each row
begin
select t_user_seq.nextval into :new.user_id from dual;
end t_user_tr;

参数描述：

t_user_tr: 随意的名字，不要重复就行
t_user: 表名
user_id ：自增的id

删除触发器：

DROP TRIGGER trigger_name;

参数描述

trigger_name：要删除的触发器的名称。

4. 测试

insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming1',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming1',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming1',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming2',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');

select * from t_user