oracle 序列详解

转]Oracle sequence 序列详解
2010-09-17 17:09
ORACLE序列的使用
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY 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就会丢失. 所以可以在create sequence的时候用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;


关于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


序：
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;