SQL基础--序列
序列是一种数据库对象，用来自动产生一组唯一的序号；序列是一种共享式的对象，多个用户可以共同使用序列中的序号。
序列的创建语法
CREATE SEQUENCE sequencename
[INCREMENT BY n] 定义序列增长步长，省略为1
[START WITH m] 序列起始值，省略为1
[{MAXVALUE n | NOMAXVALUE}] 序列最大值，
[{MINVALUE n | NOMINVALUE}] 序列最小值
[{CYCLE | NOCYCLE}] 到达最大值或最小值后，继续产生序列（默认NOCYCLE）
[{CACHE n | NOCACHE}]; CACHE默认是20
--创建一个简单序列
SQL> create sequence myseq;
使用序列
NEXTVAL 和CURRVAL 伪列
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
第一次使用时CURRVAL不能用
使用时需要指定序列的对象名
将序列值装入内存可提高访问效率
序列在下列情况下出现裂缝:
–回滚
–系统异常
–多个表同时使用同一序列
如果不将序列的值装入内存(NOCACHE), 可使用表USER_SEQUENCES 查看序列当前的有效值
SQL> select myseq.currval from dual; select myseq.currval from dual * 第 1 行出现错误: ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义 SQL> select myseq.nextval from dual; NEXTVAL ---------- 1 SQL> select myseq.currval from dual; CURRVAL ---------- 1
--实际应用
SQL> create table member( 2 mid number, 3 name varchar2(50) not null, 4 constraint pk_mid primary key(mid) 5 ); 表已创建。 SQL> insert into member(mid,name) values (myseq.nextval,'Scott'); 已创建 1 行。 SQL> insert into member(mid,name) values (myseq.nextval,'Tom'); 已创建 1 行。 SQL> select * from member; MID NAME ---------- -------------------------------------------------- 4 Scott 5 Tom
查询和删除序列
SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 1.0000E+28 1 N N 20 21 SQL> drop sequence myseq; 序列已删除。
创建特殊功能的序列
--设置序列的初始值和增长步长
SQL> create sequence myseq 2 increment by 5 3 start with 30; 序列已创建。 SQL> select myseq.currval from dual; select myseq.currval from dual * 第 1 行出现错误: ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义 SQL> select myseq.nextval from dual; NEXTVAL ---------- 30
--cache和nocache
默认情况是打开缓存的，默认的大小是20，这里有一个重要的参数是LAST_NUMBER
SQL> create sequence myseq cache 100; 序列已创建。 SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 1.0000E+28 1 N N 100 1 SQL> select myseq.nextval from dual; NEXTVAL ---------- 1 SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 1.0000E+28 1 N N 100 101
使用nocache
SQL> create sequence myseq nocache; 序列已创建。 SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 1.0000E+28 1 N N 0 1 SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 1.0000E+28 1 N N 0 1 SQL> select myseq.nextval from dual; NEXTVAL ---------- 1 SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 1.0000E+28 1 N N 0 2
--使用循环序列
循环序列是指每次调用nextval可以产生指定范围的数据，比如在1、3、5、7、9这5个数字中产生
SQL> create sequence myseq 2 start with 1 3 increment by 2 4 maxvalue 10 5 minvalue 1 6 cycle 7 cache 3; 序列已创建。 SQL> select myseq.nextval from dual; NEXTVAL ---------- 1 SQL> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- MYSEQ 1 10 2 Y N 3 7
修改序列
修改序列的注意事项
必须是序列的拥有者或对序列有ALTER 权限
只有将来的序列值会被改变
改变序列的初始值只能通过删除序列之后重建序列的方法实现
其它的一些限制
SQL> ALTER SEQUENCE myseq 2 INCREMENT BY 20 3 MAXVALUE 160 4 CYCLE;
Oracle12C新特性
从oracle12c开始提供了像mysql那样的自动增长列
SQL> create table tab_test2( 2 id number generated as identity ( 3 start with 50 increment by 2), 4 name varchar2(30)); Table created.
