Oracle 设置ID自增

2023-07-07 11:34:49 浏览数 (2)

1. 先有个表

代码语言：javascript复制
# 首先得有个表
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

代码语言：javascript复制
# 为这个表创建序列
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中

可以看如下例子：

代码语言：javascript复制
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。例子：

代码语言：javascript复制
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. 创建一个触发器

代码语言：javascript复制
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

删除触发器：

代码语言：javascript复制
DROP TRIGGER trigger_name;

参数描述

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

4. 测试

代码语言：javascript复制
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

参考文章

  • https://blog.csdn.net/qq_42055933/article/details/117401672
  • oracle如何实现ID自增长
数据库 oracle 缓存 insert sequence

0 人点赞