Oracle 序列

CREATE SEQUENCE [ schema. ]sequence

   [ { INCREMENT BY | START WITH } integer

   | { MAXVALUE integer | NOMAXVALUE }

   | { MINVALUE integer | NOMINVALUE }

   | { CYCLE | NOCYCLE }

   | { CACHE integer | NOCACHE }

   | { ORDER | NOORDER }

   ]... ;

不能保证序列的连续性（数据重启，其他事件，rollback。。。）都会导致序列的不连续。只能保证唯一性，

user_sequences/dba_sequences/all_sequences

If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying onlyINCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit. 最大38位

  • To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
  • To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
  • To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE, which is the value 1.
  • INCREMENT BY Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

  • START WITH  Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

    Note:

    This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.

    MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater thanSTART WITH and must be greater than MINVALUE.

    NOMAXVALUE  Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.

    MINVALUE Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

    NOMINVALUE  Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.

    CYCLE  Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

    NOCYCLE  Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

    CACHE Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:

Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.

NOCACHE  Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER  Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

Example

Creating a Sequence: Example The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

ALTER SEQUENCE

ALTER SEQUENCE [ schema. ]sequence

  { INCREMENT BY integer

  | { MAXVALUE integer | NOMAXVALUE }

  | { MINVALUE integer | NOMINVALUE }

  | { CYCLE | NOCYCLE }

  | { CACHE integer | NOCACHE }

  | { ORDER | NOORDER }  sequence numbers are generated in order of request.

}

Cyclye: CYCLE  Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value. 循环是，指定了最大值，当达到最大值是，新得到的值是从1开始，然后加increment by 的值

Semantics

The keywords and parameters in this statement serve the same purposes they serve when you create a sequence.

  • To restart the sequence at a different number, you must drop and re-create it.
  • If you change the INCREMENT BY value before the first invocation of NEXTVAL, some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value.
  • Oracle Database performs some validations. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.

Examples

Modifying a Sequence: Examples This statement sets a new maximum value for the customers_seq sequence, which was created in "Creating a Sequence: Example":

ALTER SEQUENCE customers_seq 
   MAXVALUE 1500;

This statement turns on CYCLE and CACHE for the customers_seq sequence:

ALTER SEQUENCE customers_seq 
   CYCLE
   CACHE 5; 
create sequence  test1_seq 
increment by 2
start with 5
maxvalue 10—循环时最大的值
minvalue 3—循环后的最小值，默认是1,
cycle—设置可以循环
nocache;
select TEST_SEQ.nextval,test_seq.currval  from dual

设置了minvalue，currvale值没变？？5+2

alter sequence test_seq

 cache 4

序列介绍


序列是一个计数器，它并不会与特定的表关联。通过创建Oracle序列和触发器实现表的主键自增。 序列的用途一般用来填充主键和计数。

序列使用


1.创建序列

ORACLE序列的语法格式为：

CREATE SEQUENCE 序列名
[INCREMENT BY n]  If you omit this clause, then the interval defaults to 1.
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];

 

 

1)INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

2)START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

3)MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

4)MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

5)CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;最小值为1。对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

ORACLE OCP考试有道题关于序列，如下所示
Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;


The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following
SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error

答案：a 


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

2.删除序列


语法是DROP SEQUENCE [schema].序列名;

CREATE SEQUENCE SEQ_TEST
INCREMENT BY 1
START WITH 1
MINVALUE 1 NOMAXVALUE
NOCYCLE;

DROP SEQUENCE SEQ_TEST;


3:序列使用

CURRVAL:        返回序列的当前值。
NEXTVAL：        序列递增，返回下一值。

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

4：序列查看

SELECT * FROM USER_SEQUENCES;

SELECT * FROM ALL_SEQUENCES;

SELECT * FROM DBA_SEQUENCES;

5：序列修改

不能修改序列的初始值,否则会报ORA-02283:

SQL> ALTER SEQUENCE SEQ_TEST START WITH 2;
ALTER SEQUENCE SEQ_TEST START WITH 2
ORA-02283: 无法更改启动序列号


SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY 2;
Sequence altered

posted @ 2018-12-04 18:06  春困秋乏夏打盹  阅读(428)  评论(0)  编辑  收藏  举报