ORACLE序列的使用
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE權(quán)限，
CREATE SEQUENCE emp_sequence
??? INCREMENT BY 1 -- 每次加幾個
??? START WITH 1??? -- 從1開始計(jì)數(shù)
??? NOMAXVALUE????? -- 不設(shè)置最大值
??? NOCYCLE???????? -- 一直累加，不循環(huán)
??? CACHE 10
??? NOORDER;???? --并行時取得序列的順序

一旦定義了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的當(dāng)前值
NEXTVAL=增加sequence的值，然后返回 sequence 值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL

可以使用sequence的地方：
- 不包含子查詢、snapshot、VIEW的 SELECT 語句
- INSERT語句的子查詢中
- INSERT語句的VALUES中
- UPDATE 的 SET中???

可以看如下例子：
INSERT INTO emp VALUES??
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SELECT empseq.currval???? FROM DUAL;

但是要注意的是：
- 第一次NEXTVAL返回的是初始值；隨后的NEXTVAL會自動增加你定義的INCREMENT BY值，然后返回增加后的值。CURRVAL 總是返回當(dāng)前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否則會出錯。一次NEXTVAL會增加一次 SEQUENCE的值，所以如果你在同一個語句里面使用多個NEXTVAL，其值就是不一樣的。明白？

- 如果指定CACHE值，ORACLE就可以預(yù)先在內(nèi)存里面放置一些sequence，這樣存取的快些。cache里面的取完后，oracle自動再取一組 到cache。 使用cache或許會跳號， 比如數(shù)據(jù)庫突然不正常down掉（shutdown abort),cache中的sequence就會丟失. 所以可以在create sequence的時候用nocache防止這種情況。

2、Alter Sequence
你或者是該sequence的owner，或者有ALTER ANY SEQUENCE 權(quán)限才能改動sequence. 可以alter除start至以外的所有sequence參數(shù).如果想要改變start值，必須 drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
??? INCREMENT BY 10
??? MAXVALUE 10000
??? CYCLE??? -- 到10000后從頭開始
??? NOCACHE ;

影響Sequence的初始化參數(shù)：
SEQUENCE_CACHE_ENTRIES =設(shè)置能同時被cache的sequence數(shù)目。??

可以很簡單的Drop Sequence
DROP SEQUENCE order_seq;

關(guān)于Oracle的序列（Sequence）使用

序列是一數(shù)據(jù)庫對象，利用它可生成唯一的整數(shù)。一般使用序列自動地生成主鍵值。

1） 建立序列命令

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue];
[NOCYCLE]　 --
INCREMENT BY ： 指定序列號之間的間隔，該值可為正的或負(fù)的整數(shù)，但不可為0。序列為升序。忽略該子句時，缺省值為1。
START WITH ：指定生成的第一個序列號。在升序時，序列可從比最小值大的值開始，缺省值為序列的最小值。對于降序，序列可由比最大值小的值開始，缺省值為序列的最大值。
MAXVALUE ：指定序列可生成的最大值。
NOMAXVALUE ：為升序指定最大值為1027，為降序指定最大值為-1。
MINVALUE ：指定序列的最小值。
NOMINVALUE ：為升序指定最小值為1。為降序指定最小值為-1026。
NOCYCLE ：一直累加，不循環(huán)

2） 更改序列命令

ALTERSEQUENCE [user.]sequence_name
[INCREMENT BY n]
[MAXVALUE n| NOMAXVALUE ]
[MINVALUE n | NOMINVALUE]；
修改序列可以：
?? 修改未來序列值的增量。
設(shè)置或撤消最小值或最大值。
改變緩沖序列的數(shù)目。
?? 指定序列號是否是有序。

注意：
1，第一次NEXTVAL返回的是初始值
2，可以alter除start至以外的所有sequence參數(shù).如果想要改變start值，必須 drop sequence 再 re-create .

3） 刪除序列命令

DROP SEQUENCE [user.]sequence_name；
用于從數(shù)據(jù)庫中刪除一序列。

4）牛刀小試

???? 4.1）創(chuàng)建一個序列號的語句：

-- Create sequence
create sequence NCME_QUESTION_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
nocache;

4.2）SQL中取序列號的用法：

SELECT NCME_QUESTION_SEQ.nextval FROM dual
SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual

序：
CREATE SEQUENCE command

PURPOSE:
??? To create a sequence. A sequence is a database object from which
??? multiple users may generate unique integers. You can use sequences
??? to automatically generate primary key values.

SYNTAX:

CREATE SEQUENCE [schema.]sequence
??? [INCREMENT BY integer]
??? [START WITH integer]
??? [MAXVALUE integer | NOMAXVALUE]
??? [MINVALUE integer | NOMINVALUE]
??? [CYCLE | NOCYCLE]
??? [CACHE integer | NOCACHE]
??? [ORDER | NOORDER]

where:

schema
??? is the schema to contain the sequence. If you omit schema, Oracle
??? creates the sequence in your own schema.

sequence
??? is the name of the sequence to be created.

INCREMENT BY
??? specifies the interval between sequence numbers. This value can be
??? any positive or negative Oracle integer, but it cannot be 0. If
??? this value is negative, then the sequence descends. If the
??? increment is positive, then the sequence ascends. If you omit this
??? clause, the interval defaults to 1.

MINVALUE
??? specifies the sequence's minimum value.

NOMINVALUE
??? specifies a minimum value of 1 for an ascending sequence or -10
??? for a descending sequence.

??? The default is NOMINVALUE.

MAXVALUE
??? specifies the maximum value the sequence can generate.

NOMAXVALUE
??? specifies a maximum value of 10
??? for a descending sequence.

The default is NOMAXVALUE.

START WITH
??? specifies the first sequence number to be generated. You can use
??? this option 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
??? sequence's minimum value. For descending sequences, the default
??? value is the sequence's maximum value.

CYCLE
??? specifies 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.

NOCYCLE
??? specifies that the sequence cannot generate more values after
??? reaching its maximum or minimum value.

??? The default is NOCYCLE.

CACHE
??? specifies how many values of the sequence Oracle preallocates and
??? keeps in memory for faster access. 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.

NOCACHE
??? specifies that values of the sequence are not preallocated.

??? If you omit both the CACHE parameter and the NOCACHE option, Oracle
??? caches 20 sequence numbers by default. However, if you are using
??? Oracle with the Parallel Server option in parallel mode and you
??? specify the ORDER option, sequence values are never cached,
??? regardless of whether you specify the CACHE parameter or the NOCACHE
??? option.

ORDER
??? guarantees that sequence numbers are generated in order of request.
??? You may want to use this option if you are using the sequence
??? numbers as timestamps. Guaranteeing order is usually not important
??? for sequences used to generate primary keys.

NOORDER
??? does not guarantee sequence numbers are generated in order of
??? request.

??? If you omit both the ORDER and NOORDER options, Oracle chooses
??? NOORDER by default. Note that the ORDER option is only necessary to
??? guarantee ordered generation if you are using Oracle with the
??? Parallel Server option in parallel mode. If you are using exclusive
??? mode, sequence numbers are always generated in order.

PREREQUISITES:
??? To create a sequence in your own schema, you must have CREATE
??? SEQUENCE privilege.

??? To create a sequence in another user's schema, you must have CREATE
??? ANY SEQUENCE privilege. If you are using Trusted Oracle in DBMS MAC
??? mode, your DBMS label must dominate the creation label of the owner
??? of the schema to contain the sequence.

example

create sequence seqTest
increment by 1
start with 0
maxvalue 10000
minvalue 0
nocache
cycle
noorder;

select seqTest.nextval from dual;
select seqTest.currval from dual;

alter sequence seqTest
increment by 5;

drop sequence seqTest;

oracle sequence的使用