Oracle 19C学习 - 17. 序列

序列是什么

序列就是一组数字的队列。

1. 自动生成唯一编号
2. 可共享的对象
3. 通常用于创建主键。
4. 如果将序列高速缓存放到内存中，可以提高序列值访问效率，也增大危险，一旦内存失效，序列的缓存值就丢失了。

MYSQL序列在建表的时候在定义列的时候一起定义，Oracle不可以，必须要以单独的数据对象出现。

序列操作

创建序列

CREATE SEQUENCE 序列名称
INCREMENT BY # -- #代表自增的值
START WITH # -- 从某个值开始
MAXVALUE # -- 定义最大值
NOCACHE -- 无缓存
NOCYCLE; -- 无循环

复制一个新的departments表
CREATE TABLE my_departments
AS 
SELECT * FROM DEPARTMENTS WHERE 1 = 1;

创建一个针对于my_departments表的ID列的序列对象
CREATE SEQUENCE MY_DEPARTMENTS_ID_SEQ
  INCREMENT BY 10
  START WITH 280
  MAXVALUE 350
  NOCACHE;

确认序列

序列记录在表user_sequences中。

Name          Null     Type          
------------- -------- ------------- 
SEQUENCE_NAME NOT NULL VARCHAR2(128) 
MIN_VALUE              NUMBER        
MAX_VALUE              NUMBER        
INCREMENT_BY  NOT NULL NUMBER        
CYCLE_FLAG             VARCHAR2(1)   
ORDER_FLAG             VARCHAR2(1)   
CACHE_SIZE    NOT NULL NUMBER        
LAST_NUMBER   NOT NULL NUMBER        
SCALE_FLAG             VARCHAR2(1)   
EXTEND_FLAG            VARCHAR2(1)   
SESSION_FLAG           VARCHAR2(1)   
KEEP_VALUE             VARCHAR2(1)  

通过对user_sequences的查询，得到值。

SELECT SEQUENCE_NAME, MIN_VALUE,MAX_VALUE,LAST_NUMBER,INCREMENT_BY 
FROM USER_SEQUENCES;

                       MIN_VALUE MAX_VALUE      LAST    INCREMENT_BY
DEPARTMENTS_SEQ    	1	    9990	280	10
EMPLOYEES_SEQ	        1	    9999	207	1
LOCATIONS_SEQ	        1	    9900	3300	100
MY_DEPARTMENTS_ID_SEQ	1	     350	280	10

NEXTVAL和CURRVAL

NEXTVAL 返回下一个可用的序列值，每次引用时他都返回一个位置的值。
CURRVAL 返回当前的序列值。
必须对序列发出NEXTVAL和CURRVAL才能包含我们要的序列值。

可以在以下SQL语句使用NEXTVAL和CURRVAL:

1. 不属于子查询一部分的SELECT语句
2. INSERT语句中子查询的SELECT列表
3. INSERT语句的VALUES子句。
4. UPDATE语句的SET语句。

不可以在SQL中使用NEXTVAL和CURRVAL

1. 视图的select列表
2. 带有DISTINCT的SQL语句
3. 带有GROUP BY, HAVING或ORDER BY的SELECT语句
4. SELECT, DELETE或UPDATE语句的子查询
5. CREATE TABLE或ALTER TABLE语句中的子查询。

SELECT MY_DEPARTMENTS_ID_SEQ.CURRVAL FROM DUAL;
=> output: 280

下面语句会造成下个序列值被使用，不用使用下面语句查看序列值。
SELECT MY_DEPARTMENTS_ID_SEQ.NEXTVAL FROM DUAL;
=> output: 290

SQL中使用序列

使用"序列名.NEXTVAL" 可以将序列的下一个值用于SQL语句。

INSERT INTO my_departments
VALUES (MY_DEPARTMENTS_ID_SEQ.NEXTVAL, 'TEST', 100,100);

修改序列

修改序列的准则

1. 必须是序列的所有者才能修改序列。
2. 修改只会影响序列之后的序号。
3. 如果需要从不同的序号开始，则必须删除原有的序列后再创建序列。

ALTER SEQUENCE 序列名
一条序列相关选项(多了可能改不了)

ALTER SEQUENCE MY_DEPARTMENTS_ID_SEQ
  INCREMENT BY 20;

ALTER SEQUENCE MY_DEPARTMENTS_ID_SEQ
  MAXVALUE 1000;
  
ALTER SEQUENCE MY_DEPARTMENTS_ID_SEQ
  CYCLE;