关于Oracle的序列（Sequence）使用序列是一数据库对象，利用它可生成唯一的整数。一般使用序列自动地生成主键值。对我们程序员来讲，精力时间有限，我们只学最有用的知识。大家请看：
　　1） 建立序列命令
　　CREATE SEQUENCE [user.]sequence_name
　　[increment by n]
　　[start with n]
　　[maxvalue n | nomaxvalue]
　　[minvalue n | nominvalue];
　　[NOCYCLE]　 --
　　INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0.序列为升序。忽略该子句时，缺省值为1.
　　START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。
　　MAXVALUE：指定序列可生成的最大值。
　　NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1.
　　MINVALUE：指定序列的最小值。
　　NOMINVALUE：为升序指定最小值为1.为降序指定最小值为-1026.
　　NOCYCLE：一直累加，不循环
　　2） 更改序列命令
　　ALTERSEQUENCE [user.]sequence_name
　　[INCREMENT BY n]
　　[MAXVALUE n| NOMAXVALUE ]
　　[MINVALUE n | NOMINVALUE]；
　　修改序列可以：修改未来序列值的增量。
　　设置或撤消最小值或最大值。
　　改变缓冲序列的数目。
　　指定序列号是否是有序。
　　注意：
　　1，第一次NEXTVAL返回的是初始值
　　2，可以alter除start至以外的所有sequence参数。如果想要改变start值，必须 drop sequence 再 re-create .
　　3） 删除序列命令
　　DROP SEQUENCE [user.]sequence_name；
　　用于从数据库中删除一序列。
　　4）牛刀小试
　　4.1）创建一个序列号的语句：
　　-- 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
　　SELECT NCME_QUESTION_SEQ.nextval FROM dual SELECTNCME_QUESTION_SEQ.CURRVAL FROM dual
　　注意：在使用序列的时候，有时需要有用户名，就像这样：
　　insert intosystem.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values('JG','123456','000',0,system.CONID.nextval);
-------------------------------------------------------------------------end---------------------

ORACLE序列的使用

1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATEANY SEQUENCE权限，
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10
NOORDER; --并行时取得序列的顺序
一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的当前值
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 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？
- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在createsequence的时候用nocache防止这种情况。
2、Alter Sequence
你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE -- 到10000后从头开始
NOCACHE ;

影响Sequence的初始化参数：
SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。
可以很简单的Drop Sequence
DROP SEQUENCE order_seq;

序：
CREATE SEQUENCE command
PURPOSE:
To create a sequence. A sequence is a database object fromwhich
multiple users may generate unique integers. You can usesequences
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 valuecan 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 youomit 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 canuse
this option to start an ascending sequence at a valuegreater than
its minimum or to start a descending sequence at a valueless than
its maximum. For ascending sequences, the default value isthe
sequence's minimum value. For descending sequences, thedefault
value is the sequence's maximum value.
CYCLE
specifies that the sequence continues to generate valuesafter
reaching either its maximum or minimum value. After anascending
sequence reaches its maximum value, it generates its minimumvalue.
After a descending sequence reaches its minimum, itgenerates its
maximum.
NOCYCLE
specifies that the sequence cannot generate more valuesafter
reaching its maximum or minimum value.
The default is NOCYCLE.
CACHE
specifies how many values of the sequence Oraclepreallocates and
keeps in memory for faster access. The minimum value forthis
parameter is 2. For sequences that cycle, this value must beless
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 andthe NOCACHE option, Oracle
caches 20 sequence numbers by default. However, if you areusing
Oracle with the Parallel Server option in parallel mode andyou
specify the ORDER option, sequence values are never cached,
regardless of whether you specify the CACHE parameter or theNOCACHE
option.
ORDER
guarantees that sequence numbers are generated in order ofrequest.
You may want to use this option if you are using thesequence
numbers as timestamps. Guaranteeing order is usually notimportant
for sequences used to generate primary keys.
NOORDER
does not guarantee sequence numbers are generated in orderof
request.
If you omit both the ORDER and NOORDERoptions, Oracle chooses
NOORDER by default. Note that the ORDER option is onlynecessary to
guarantee ordered generation if you are using Oracle withthe
Parallel Server option in parallel mode. If you are usingexclusive
mode, sequence numbers are always generated in order.
PREREQUISITES:
To create a sequence in your own schema, you must haveCREATE
SEQUENCE privilege.
To create a sequence in another user'sschema, you must have CREATE
ANY SEQUENCE privilege. If you are using Trusted Oracle inDBMS MAC
mode, your DBMS label must dominate the creation label ofthe 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;