Sequence，sequence是什麼意思

Sequence，sequence是什麼意思
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production下面是摘自11gR2部分官方文檔：CREATE SEQUENCE

Purpose

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

Note on Using Sequences with Deferred Segments If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.

See Also:

• Chapter 2, "Pseudocolumns" for more information on using the CURRVAL and NEXTVAL

• "How to Use Sequence Values" for information on using sequences

• ALTER SEQUENCE or DROP SEQUENCE for information on modifying or dropping a sequence

Prerequisites（先決條件）

To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege.

To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege.

Syntax（文法）

create_sequence::=

序列是一種資料庫項，它產生一個整數的序列，產生的整數通常可以用來填充數字類型的主鍵列。
我們先建立一個序列:
SQL> create sequence text 
  2  ;
序列已建立。
上面是不帶參數的序列，我們建立帶參數的序列：
SQL> create sequence text_1 start with 10 increment by 5 minvalue 10 maxvalue 20
  2  cycle cache 2 order;

序列已建立。
SQL> select * from dba_sequences where sequence_name like '%TEXT%';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O
------------------------------ ------------------------------ ---------- ---------- ------------ - -
CACHE_SIZE LAST_NUMBER
---------- -----------
SYS                            TEXT                                    1 1.0000E+28            1 N N
        20           1


SYS                            TEXT_1                                 10         20            5 Y Y
         2          10

怎麼使用序列呢？下面我們做下小實驗：
序列產生一系列數字，一個序列包含兩個“偽列”，分別是currval和nextval,可以分別取用該序列的當前值和下一個值。
在檢索序列的當前值之前，必須通過檢索序列的下一個值對序列進行初始化：
SQL> select text.nextval from dual;
NEXTVAL
----------
         1
SQL> select text.currval from dual;

 CURRVAL
----------
         1
SQL> select text.nextval from dual;
NEXTVAL
----------
         2
SQL>  select text.currval from dual;
  CURRVAL
----------
         2
SQL>  select text.nextval from dual;

  NEXTVAL
----------
         3
==========================================================================


SQL> select text_1.currval from dual;
select text_1.currval from dual
       *
第 1 行出現錯誤:
ORA-08002: 序列 TEXT_1.CURRVAL 尚未在此會話中定義
SQL> select text_1.nextval from dual;

  NEXTVAL
----------
        10
SQL> select text_1.currval from dual;

 CURRVAL
----------
        10
SQL> select text_1.nextval from dual;


   NEXTVAL
----------
        15


SQL> select text_1.nextval from dual;


   NEXTVAL
----------
        20


SQL> select text_1.currval from dual;


   CURRVAL
----------
        20

======================================================
使用序列填充主鍵：

當表的主鍵是整數時，可以用序列來產生主鍵：
SQL> create table t_text(id integer constraint  t_text_pk primary key);

表已建立。


SQL> create sequence text_te nocache;


序列已建立。

在使用序列填充主鍵時，通常會選擇使用nocache,這樣可以避免序列不連續的情況，序列不連續的情況，之所以會發生不連續的情況，是因為關閉資料庫時，所緩衝的
值將全部丟失。但是使用nocache會降低效能。但是如果可以容忍主鍵不連續，那就用cache
SQL>  insert into t_text values (text_te.nextval);


已建立 1 行。


SQL> select * from t_text;


        ID
----------
         1


SQL> insert into t_text values (text_te.nextval);


已建立 1 行。


SQL> select * from t_text;


        ID
----------
         1
         2


===================================
修改序列，但是有限制：
不能修改序列的初值
序列的最小值不能大於當前值
序列的最大值不能小於當前值




SQL> ALTER SEQUENCE text_te increment by 5;

序列已更改。



SQL> insert into t_text values (text_te.nextval);


已建立 1 行。


SQL>  select * from t_text;


        ID
----------
         1
         2
         7


=====================================================
刪除序列：
drop sequence  sequence_name;