ORACLE SEQUENCE 詳解，oraclesequence

ORACLE SEQUENCE 詳解，oraclesequence

1.    About Sequences（關於序列）

序列是資料庫物件一種。多個使用者可以通過序列產生連續的數字以此來實現主鍵欄位的自動、唯一增長，並且一個序列可為多列、多表同時使用。

序列消除了序列化並且提高了應用程式一致性。（想象一下沒有序列的日子怎麼辦？）

2.   Creating Sequences（建立序列）

前提：Prerequisites

To create a sequence inyour own schema, you must have the CREATE SEQUENCE system privilege. 在自己模式下建立序列需要create sequence許可權

To create a sequence inanother user's schema, you must have the CREATE ANY SEQUENCE system privilege. 在其他使用者模式下建立序列需要create any sequence許可權。

文法：Syntax

如果不加條件陳述式，預設建立的序列格式如下：

-- Create sequence

create sequence SEQ_T

minvalue  1

maxvalue  999999999999999999999999999

start  with  1

increment  by  1

cache  20;

 

語義Semantics：

INCREMENT BY：指定序列增長步長。可以為正（升序）、負整數（降序），但不能為0。最高精度28。

START WITH： 指定序列起始數。預設為序列最小值。

MAXVALUE ：指定序列最大值。最大28位。必須大於等於起始值且大於等於序列最小值。

NOMAXVALUE：  無最大值（實際為10^27或-1）。default

MINVALUE ：指定序列最小值。

NOMINVALUE  ：無最小值（實際為1或-10^26）。Default

CYCLE  ：指定序列達到最大值或最小值後繼續從頭開始產生。

NOCYCLE ：不迴圈產生。Default.

CACHE ：指定資料庫記憶體中預分配的序列值個數，以便快速擷取。最小cache值為2。

Cache參數最大值為：

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

注意1：如果系統發生故障，所有緩衝的沒有被DML語句使用並提交的序列值將丟失。潛在丟失值數量等於cache的數量。

NOCACHE  ：不指定緩衝數，預設緩衝20

ORDER ：指定order條件保證序列按請求順序產生。此條件適用於RAC環境。

NOORDER ：不保證序列按請求順序產生。

 

例子：

CREATE SEQUENCE customers_seq

 START WITH     1000

 INCREMENT BY   1

 NOCACHE

 NOCYCLE;

 

注意2：帶有cycle條件序列當達到最大值後，下一個值從最小值minvalue開始迴圈！

CREATE  SEQUENCE seq1

START  WITH  200

INCREMENT  BY  10

MAXVALUE  200

CYCLE

NOCACHE;

 

SELECT  seq1.nextval   FROM dual;

結果：1

3.   ALTER SEQUENCE（修改序列）

 

前提：

The sequence must be in your own schema, or youmust have the ALTER object privilege on

the sequence, or you must have the ALTER ANY SEQUENCE systemprivilege.

修改自己模式序列需要alter object許可權，修改其他模式序列需要alter any sequence許可權。

 

文法：

 

語義：

 

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

 

 

例子：

ALTER SEQUENCE customers_seq 

   MAXVALUE 1500;

 

ALTER SEQUENCE customers_seq 

   CYCLE

   CACHE 5; 

 

4.   DROP SEQUENCE（刪除序列）

前提：

       Thesequence must be in your own schema or you must have the DROP ANY SEQUENCE system privilege.

 

刪除序列必須要有drop  any  sequence許可權

 

文法：

 

例子：

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 幹嘛用的

Oracle提供了sequence對象,由系統提供自增長的序號,通常用於產生資料庫資料記錄的自增長主鍵或序號的地方.

下面介紹一下關於sequence 的產生,修改,刪除等常用的操作:

1. 建立 Sequence

使用如下命令建立sequence(使用者需要有CREATE SEQUENCE 或者CREATE ANY SEQUENCE許可權):

CREATE SEQUENCE test_sequence INCREMENT BY 1 -- 每次加的個資料 START WITH 1 -- 從1開始計數 NOMAXVALUE -- 不設定最大值 NOCYCLE -- 一直累加，不迴圈 CACHE 10 ; [注意] 如果設定了CACHE值，ORACLE將在記憶體裡預先放置一些sequence，以使存取速度更快。cache裡面的取完後，oracle自動再取一組到cache。 但是，使用cache可能會跳號， 當遇到資料庫突然異常down掉（shutdown abort),cache中的sequence就會丟失. 因此，推薦在create sequence的時候使用 nocache 選項。

2. 使用 sequence:

sequence.CURRVAL -- 返回 sequence的當前值 sequence.NEXTVAL -- 增加sequence的值，然後返回 sequence 值

[注意] 第一次NEXTVAL返回的是初始值； 隨後的NEXTVAL會自動增加你定義的INCREMENT BY值，然後返回增加後的值。

CURRVAL 總是返回當前SEQUENCE的值，但是在第一次NEXTVAL初始化之後才能使用CURRVAL，否則會出錯。 一次NEXTVAL會增加一次 SEQUENCE的值，所以如果你在同一個語句裡面使用多個NEXTVAL，其值就是不一樣的。

sequence 儲存在資料字典中,儲存於user_sequences表 LAST_NUMBER 為最終序號，也就是sequence遊標當前所在的位置。

//get sequence last_number

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME=TEST_SEQNAME

// NEXTVAL 使遊標指向下一位（增一或減一）

SELECT SEQNAME.NEXTVAL FROM USER_SEQUENCES 得到下一位遊標的值

3. 修改 Sequence

使用者必須擁有ALTER ANY SEQUENCE 許可權才能修改sequence. 可以alter除start至以外的所有sequence參數. 如果想要改變start值，必須 drop sequence 再 re-create.

命令格式如下：

ALTER SEQUENCE test_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE -- 到10000後從頭開始 NOCACHE ;

4. 刪除 Sequence DROP SEQUENCE order_seq;
參考資料：......餘下全文>>
 
oracle資料庫的這個sequence怎寫

在Oracle中要想設定欄位自增，序列和觸發器組合使用才行。例如：
序列：
create sequence SEQ_XLH
minvalue 1
start with 1
increment by 1
cache 20
order;
首先在emp表增加一列ID類型字元20位：alter table emp add ID char(20);
觸發器：
create or replace trigger trig_emp_id
before insert on emp
for each row
begin
select to_char(sysdate,'yyyymmdd')||
substr(to_char(1000000000000+seq_xlh.nextval),-12,12)
into :new.ID from dual;
end;