Oracle 序列

一、介绍

序列(Sequence)，又叫序列生成器，用于提供一系列的数字，并不会与特定的表关联，开发人员一般用它填充主键和计数。
每次访问序列，序列按照一定的规律增加或者减少。
序列的定义存储在SYSTEM表空间中，序列不像表，它不会占用磁盘空间。
序列独立于事务，每次事务的提交和回滚都不会影响序列。

二、使用

1、创建

CREATE SEQUENCE [ schema. ] sequence
   [ { INCREMENT BY | START WITH } integer        --INCREMENT BY设置步长值(为整数则递增，负数递减),START WITH设置初始值
   | { MAXVALUE integer | NOMAXVALUE }            --最大值 | 不设置最大值
   | { MINVALUE integer | NOMINVALUE }            --最小值 | 不设置最小值
   | { CYCLE | NOCYCLE }                        --到达最值后是否循环，若不循环，达到限值后继续产生新值就会发生错误
   | { CACHE integer | NOCACHE }                --缓存|不缓存
   | { ORDER | NOORDER }                        --设置是否按照请求的顺序产生序列
   | { KEEP | NOKEEP }                            --
   | { SESSION | GLOBAL }                        --
   ]...
;

CACHE(缓冲)定义存放序列的内存块的大小，默认为20。
NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。 当实例异常关闭时，缓存选项会造成数据丢失。

2、修改

ALTER SEQUENCE [ schema. ] sequence
  { INCREMENT BY integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { CACHE integer | NOCACHE }
  | { ORDER | NOORDER }
  | { KEEP | NOKEEP }
  | { SESSION | GLOBAL }
  } ...
;

注意：
(1)必须是序列的拥有者或对序列有ALTER 权限;
(2)只有将来的序列值会被改变;
(3)不能直接修改序列初始值，只能通过删除序列之后重建的方法实现;

3、删除

DROP SEQUENCE [ schema. ] sequence_name ;

 

4、使用

CURRVAL:    返回序列的当前值：select 序列名.currval from dual;
NEXTVAL：    序列递增，返回下一值:select 序列名.nextval from dual;--每次递增

你不能使用序列的CURRVAL和NEXTVAL，在下面情况下(具体参见官方文档)：
(1)在DELETE、SELECT、UPDATE的子查询中
(2)在视图或物化事物的查询中。
(3)SELECT查询中使用了DISTINCT操作符。
(4)SELECT查询中有GROUP BY或ORDER BY

5、查看字典

SELECT * FROM USER_SEQUENCES;

SELECT * FROM ALL_SEQUENCES;

SELECT * FROM DBA_SEQUENCES;

 三、应用场景

1、Oracle自增字段

Oracle12C之前的版本是不能设置某个字段为自增字段的，MySQL可以。在某些情况下，需要设置某张表的ID为自增字段时，一种解决方法是使用序列，当insert的时候，获取序列号实现自增。

若数据库版本为12C，则可以使用identity关键字。语法参考如下：

CREATE TABLE tmp(
ID NUMBER GENERATED ALWAYS|BY DEFAULT AS IDENTITY ,
NAME VARCHAR2(100)
);

说明：当时用always时，上面的ID字段将总是自动从内置的序列中获取序列号，insert和update不需要对该字段进行指定。否则，操作该ID字段会返回错误（ORA-32795）;

　　    当使用by default时，该ID字段将默认是从内置序列中获取值，当手动指定值时，将使用指定的值。需要注意的是，手动指定时，手动指定的值不是从内置序列中取的，再次使用默认值，不会从手动指定的ID值开始，

而是使用上次默认序列的nextvalue，参考如下：

CREATE TABLE tmp(
ID NUMBER GENERATED BY DEFAULT AS IDENTITY ,
NAME VARCHAR2(100)
);

SELECT * FROM tmp ;
INSERT INTO tmp (NAME) VALUES('hehe');
INSERT INTO tmp (id,NAME) VALUES(3,'hehe');
INSERT INTO tmp (NAME) VALUES('hehe');
INSERT INTO tmp (id,NAME) VALUES(1,'hehe');
INSERT INTO tmp (NAME) VALUES('hehe');
INSERT INTO tmp (id,NAME) VALUES(1,'hehe');

查询结果：

可以看到手动指定的ID值和内置序列产生的ID值是没有相关关系且可以重复的。具体相关信息请参考官方文档12c的sql language reference。

 

2、流水号

 配合时间或其他参考因素组成交易流水等。

 

3、重置序列

 因项目需要，写重置序列过程，代码如下：

  PROCEDURE P_RESET_SEQ(V_SEQNAME VARCHAR2) IS
    /********************************************************************
    ** 过程功能：重置序列的值（因部分过程中需重置序列的值，故创建此过程）
    ** 创建时间：2018-02-27
    ** 创建人：vnx
    ** 修改时间： 修改内容： 修改人：
    ********************************************************************/
    V_NUM NUMBER(10);
    V_SQL  VARCHAR2(100);
  BEGIN
    --1.获取序列的当前值
    EXECUTE IMMEDIATE 'SELECT ' || V_SEQNAME || '.NEXTVAL FROM  DUAL'
      INTO V_NUM;
    --修改序列的步长和方向（通过负值改变方向）
    V_NUM := -V_NUM;
    V_SQL  := 'ALTER SEQUENCE ' || V_SEQNAME || ' INCREMENT BY ' || V_NUM;
    EXECUTE IMMEDIATE V_SQL;
    --3.通过获取序列的下一个值来实现反向取值1次
    EXECUTE IMMEDIATE 'SELECT ' || V_SEQNAME || '.NEXTVAL FROM  DUAL'
      INTO V_NUM;
    --4.恢复序列的步长和方向
    V_SQL := 'ALTER SEQUENCE ' || V_SEQNAME || ' INCREMENT BY 1';
    EXECUTE IMMEDIATE V_SQL;
  END P_RESET_SEQ;

重置序列时，直接调用即可。

 

参考材料：

Syntax for SQL Statements https://docs.oracle.com/database/121/SQLQR/sqlqr01001.htm#SQLQR110
ORACLE序列总结 - 潇湘隐者 - 博客园 https://www.cnblogs.com/kerrycode/archive/2013/03/18/2965747.html
oracle中序列的使用 - warrior1234 - 博客园 https://www.cnblogs.com/warrior4236/p/5866214.html