Oracle 中序列使用详解

scb0814

create table test 
(pid integer primary key, 
  name varchar(20) 
  ); 
   
  select * from test; 
  insert into test values(1,'Jack'); 

create sequence test_seq;--创建序列  
select test_seq.nextval from dual;--每次使用后序列加一  
select test_seq.currval from dual;--查询当前序列的值  
insert into test values(test_seq.nextval,'Jack Or Duck?');--序列的使用  
drop sequence test_seq;--删除序列  
create sequence test_seq start with 8;--指定序列的初始值  
alter sequence test_seq minvalue 9;--设置序列的最小值  
alter sequence test_seq maxvalue 9999;--设置序列的最大值  
alter sequence test_seq increment by 1;--设置序列的步长  
drop sequence test_seq_1; 
create sequence test_seq_1 start with 3 minvalue 1 maxvalue 30 increment by 1; 
select test_seq_1.nextval from dual; 
alter sequence test_seq_1 cycle; 
alter sequence test_seq_1 nocycle;--关闭循环取值功能  
alter sequence test_seq_1 cache 10; 
 
--设置序列缓存大小的作用，注:序列缓存默认为20  
create sequence test_seq_2 start with 1 minvalue 1 maxvalue 20 increment by 3; 
alter sequence test_seq_2 cycle; 
 
--上述语句会报错：Cache值必须小于cycle值  
 
alter sequence test_seq_2 increment by 2; 
alter sequence test_seq_2 cycle; 
--还是报错  
alter sequence test_seq_2 increment by 1; 
alter sequence test_seq_2 cycle; 
--还是报错  
alter sequence test_seq_2 maxvalue 21; 
alter sequence test_seq_2 cycle; 
--不报错了  
 
--或者修改  
alter sequence test_seq_1 cache 5; 
--start with 1 and maxvalue 10 step 1 then 10 times cycle and bigger than 5