Oracle 序列详解（sequence）

1 概述

2 语法

2.1 授权

-- 通用授权语句的查询方式
select distinct t.privilege
  from dba_sys_privs t
 where t.privilege like '%SEQUENCE%'
 order by t.privilege;

grant create sequence to <user_name>;
revoke create sequence from <user_name>;

2.2 创建序列

create sequence <sequence_name>
-- 以下皆为 "可选项"
[minvalue n | nominvalue] -- 最小值，默认不限制最小值 nominvalue
[maxvalue n | nomaxvalue] -- 最大值，默认不限制最大值 nomaxvalue
[start with n]            -- 初始值，默认 1
[increment by n]          -- 增量，正数递增，负数递减，默认 1
[cache n | nocache]       -- 是否缓存，缓存序列的个数，默认 20
[cycle | nocycle]         -- 是否循环，默认 nocycle
[order | noorder]         -- 是否排序，默认 noorder

示例：创建一个名为 seq_name 的序列

create sequence scott.seq_name
minvalue 1
maxvalue 99999999   -- 按业务量，适当调整
start with 1 
increment by 1 
cycle
cache 20
noorder;

-- 若多用户使用，则需要授权
grant select on scott.seq_name to <user_name>;

2.3 查询、修改、删除

-- 查询。可用视图：dba_sequences | all_sequences | user_sequences
select * 
  from all_sequences t
 where t.sequence_owner = 'SCOTT'
   and t.sequence_name = 'SEQ_NAME';

-- 修改。
alter sequence 序列名 参数 参数值;
alter sequence seq_name cache 30;

-- 删除
drop sequence seq_name;

2.4 使用序列

使用方式：先 '.nextval' 后 '.currval' -- 反之会报错

select seq_name.nextval from dual;
select seq_name.currval from dual;

示例：创建 “学生信息表”，并实现主键 “学号” 自增

-- 1.创建 '学生信息表'
create table scott.stu_info (
  sno   number(10) constraint pk_stu_info_sno primary key,
  sname varchar2(30)
);

-- 2.创建序列（上述已创建 seq_name）

-- 3.实现序列自增
insert into scott.stu_info(sno, sname) values(scott.seq_name.nextval, '小游子');
insert into scott.stu_info(sno, sname) values(scott.seq_name.nextval, '瑶瑶');
insert into scott.stu_info(sno, sname) values(scott.seq_name.nextval, '优优');

select scott.seq_name.currval 当前序列值 from dual; 
select * from scott.stu_info;

查询结果：

sno		sname
1		小游子
2		瑶瑶
3		优优

3 扩展

3.1 cache 详解

1. 好处：'提升性能'
   (1) 当大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。
       Oracle 序列允许将序列提前生成 'cache n' 个先存入内存
       
   (2) 申请序列语句，可直接到运行最快的内存中去得到序列。
       但 cache 个数也不能设置太大，
       因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失
       
2. 坏处："断层现象":'序列丢失'
   (1) 比如：cache 20，当 nextval = 1 时，此时清空缓存（或数据库服务器重启）
       下次 nextval 会 '从上次获取的最大值开始（含缓存）'，而不是 '当前值' 开始计算！ ，
       也就是说 nextval = 21, 
       若 此时继续 清空缓存，那么下次 nextval = 41

"断层现象" 测试 - 验证sql：

create sequence seq_name_test 
minvalue 1
maxvalue 100
start with 1
increment by 1
cache 20
nocycle;

select seq_name_test.nextval from dual; -- 1

alter system flush shared_pool; -- 慎用！清空缓存语句，仅供个人测试使用！
select seq_name_test.nextval from dual; -- 21

alter system flush shared_pool; 
select seq_name_test.nextval from dual; -- 41

3.2 cycle 详解

-- 必须满足如下公式: "缓存" <= "最大循环的次数"
cache <= ceil((maxvalue - minvalue) / abs(incrementby))

create sequence seq_name_cache_error
minvalue 10 -- 可修改此处为 1-9 均可
maxvalue 200
start with 10
increment by 10
cache 20
cycle
noorder;

若不满足上述公式，报错如下：

解释：
cache = 20 > cycle 的值 = ceil((MaxValue - MinValue) / INCREMENT)，也就是 ceil((200 - 10) / 10 = 19

3.3 常用获取序列的工具包

Oracle 获取序列的pkg【工具包】

3.4 面试题

-- 在 Oracle 中，用以下 sql 命令创建一个序列：
create sequence my_seq
start with 100
increment by 10
nominvalue
nomaxvalue
nocache
nocycle;
-- 用户执行 my_seq.nextval 三次
-- 然后执行 my_seq.currval 两次，
-- 请问序列 my_seq 的当前值是（）
-- A.100	B.110	C.120	D.130

考点：
(1) 第一次执行 my_seq.nextval 值其实还是 100，从第二次开始增加
(2) my_seq.currval 并不增加序列的值

测试结果：

SQL> create sequence my_seq
  2  start with 100
  3  increment by 10
  4  nominvalue
  5  nomaxvalue
  6  nocache
  7  nocycle;
Sequence created

SQL> select my_seq.nextval from dual;
   NEXTVAL
----------
       100

SQL> select my_seq.nextval from dual;
   NEXTVAL
----------
       110

SQL> select my_seq.nextval from dual;
   NEXTVAL
----------
       120

SQL> select my_seq.currval from dual;
   CURRVAL
----------
       120

SQL> select my_seq.currval from dual;
   CURRVAL
----------
       120