ORACLE SEQUENCE 详解
时间：2022-03-10 17:56
1. About Sequences（关于序列）
序列是数据库对象一种。多个用户可以通过序列生成连续的数字以此来实现主键字段的自动、唯一增长，并且一个序列可为多列、多表同时使用。
序列消除了串行化并且提高了应用程序一致性。（想象一下没有序列的日子怎么办？）
如果不加条件语句，默认创建的序列格式如下：
-- Create sequence
create sequence SEQ_T
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
语义Semantics：
语义：
1）如果想以不同的数字重新开始序列，必须删除重建。
SQL> alter sequence seq_t start with 2;
alter sequence seq_t start with 2
*
ERROR at line 1:
ORA-02283: cannot alter starting sequencenumber
2）修改的maxvalue必须大于序列当前值。
SQL> alter sequence seq_t maxvalue 1;
alter sequence seq_t maxvalue 1
*
ERROR at line 1:
ORA-04004: MINVALUE must be less than MAXVALUE
例子：
例子：
DROP SEQUENCE oe.customers_seq;
5. NEXTVAL and CURRVAL的使用限制
CURRVAL and NEXTVAL can
be used in the following places:
· VALUES clause of INSERT statements
· The SELECT list of a SELECT statement
· The SET clause of an UPDATE statement
CURRVAL and NEXTVAL cannot
be used in these places: 不能用于以下场景
· A subquery 子查询
· A view query or materialized view query 视图或物化视图查询
· A SELECT statement with the DISTINCT operator 含distinct关键字查询
· A SELECT statement with a GROUP BY or ORDER BY clause带order
by 查询语句
· A SELECT statement that is combined with
another SELECT statement with the UNION, INTERSECT,
or MINUS set operator含union, interest,minus操作符
· The WHERE clause of a SELECT statement用在where条件中
· DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement 列的默认值
· The condition of a CHECK constraint check约束
--------------------------------------
Dylan Presents.
ORACLE SEQUENCE 详解,布布扣,bubuko.com
