oracle中的sequence

在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。  

1、Create Sequence  

你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限

CREATE SEQUENCE emp_seq  
      INCREMENT BY 1              -- 每次加几个  
      START WITH 1                -- 从1开始计数  
      NOMAXVALUE              -- 不设置最大值  
      NOCYCLE                -- 一直累加，不循环  
      CACHE 10;  

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL  
CURRVAL=返回 sequence的当前值  
NEXTVAL=增加sequence的值，然后返回 sequence 值  

比如：  
emp_sequence.CURRVAL  
emp_sequence.NEXTVAL  

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

可以看如下例子：  

SQL>INSERT INTO emp VALUES   
(emp_seq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);  
已创建 1 行。

SQL> select emp_seq.currval from dual;
    CURRVAL
----------
                1

SQL> INSERT INTO emp VALUES
      (emp_seq.nextval, 'TOM', 'CLERK',8920, SYSDATE, 1420, NULL, 30);
已创建 1 行。

SQL> select emp_seq.currval from dual;
    CURRVAL
----------
                2

但是要注意的是：  
- 第一次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数目。   

Oracle在创建序列（sequence）时有个参数你可以选择cache或者nocache，下面来讲一下两者的区别：

先来看下创建sequence的语句：

create sequence SEQ_ID  
minvalue 1  
maxvalue 99999999  
start with 1  
increment by 1  
cache n  / nocache  --其中n代表一个整数，默认值为20
order;

如果指定CACHE值，Oracle就可以预先在内存里面放置一些Sequence，这样存取的快些。cache里面的取完后，Oracle自动再取一组到cache。使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的Sequence就会丢失。
举个例子：
比如你的sequence中cache 100，那当你sequence取到90时突然断电，那么在你重启数据库后，sequence的值将从101开始。

如果指定NOCACHE值，Oracle就不会预先在内存里面存放Sequence，当然这也就可以避免数据库不正常down掉的sequence丢失。
不过会产生一些问题：
创建nocache sequence在高并发访问时，容易导致row cache lock等待事件，主要原因是每次获取nextval时都需要修改rowcache中的字典信息。
使用nocache sequence，还会导致如下问题:
由于每次修改字典信息都需要commit,可能导致log file sync等待，nocache sequence在RAC环境下，会对基于sequence生成的列创建的索引造成实例间大量索引块争用
基于以上问题,避免创建nocache sequence。

再来看看sequence相关保护机制：
row cache lock：在调用sequence.nextval情况下需要修改数据字典时发生，对应row cache lock事件
SQ lock：在内存缓存(并非rowcache)上获取sequence.nextval时发生，对应enq:SQ-contention事件
SV lock：RAC环境下获取cache order属性的sequence.nextval时发生，对应DFS lock handle事件

什么情况下使用cache？什么时间上使用nocache？

我个人感觉应该尽量使用cache，因为现在的数据库很多都是在高并发的情况下运行的，首先这样可以提高性能，并且也不会产生row cache lock等待事件。
可能有些人会担心数据库不正常的down掉会产生序列号间断，但这也是很少的情况。当然如果你的业务要求是绝不能产生间断的序列号，那就要使用nochache了。


可以很简单的Drop Sequence  :
DROP SEQUENCE order_seq;

oracle没有WINDOWS自增字段这样的功能，但是通过触发器(trigger)和序列(sequence)可以实现。


3、常见错误

1：指定最大值创建sequence----注意：此处实验超过最大值会提示错误

harvey@SALES>create sequence seq4 maxvalue 10;
Sequence created.

harvey@SALES>select seq4.nextval from dual;
    NEXTVAL
----------
                1
.....................
......................

harvey@SALES>select seq4.nextval from dual;
    NEXTVAL
----------
              10
           
harvey@SALES>select seq4.nextval from dual;
select seq4.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQ4.NEXTVAL exceeds MAXVALUE and cannot be instantiated


2：指定最大值并指定循环----------也提示错误：cache缓存中存储的seq数量必须小于一个循环

harvey@SALES>create sequence seq5 maxvalue 10 cycle;
create sequence seq5 maxvalue 10 cycle
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

harvey@SALES>create sequence seq5 maxvalue 5 cycle nocache;
Sequence created.

harvey@SALES>select seq5.nextval from dual;
    NEXTVAL
----------
                1
           
harvey@SALES>select seq5.nextval from dual;
    NEXTVAL
----------
                5
           
harvey@SALES>select seq5.nextval from dual;
    NEXTVAL
----------
                1
           
4、小结

至此整理了sequence的一些参数的默认值。同时查询了一下oracle 10g 的concept文档进行了验证。得到结果如下：
1：start参数。默认从1 开始
2：increment 参数默认为1
3：对于参数nomaxvalue/maxvalue abc    默认取值为：nomaxvalue
注：如果序列的数值达到最大值。则继续调用seq.nextval 则oracle会提示错误：
ORA-08004: sequence SEQ4.NEXTVAL exceeds MAXVALUE and cannot be instantiated

4：对于参数nocycle/cycle    默认取值为：nocycle，既：序列的取值不循环，
注：如果指定为循环。则必须指定sequence的最大值maxvalue。否则提示错误：
ORA-04015: ascending sequences that CYCLE must specify MAXVALUE

5：对于参数cache abc /nocache    默认取值为：cache 20,即默认一次缓存20个序列号到SGA中。如果20个值全用完，则会产生下一组20个号码到SGA中供用户使用
注：如果指定seq需要循环，并且seq具有缓存特性时，那么seq的一次缓存的序列号必须小于一个循环内包含的个数否则会提示错误
ORA-04013: number to CACHE must be less than one cycle


序列的并发访问 ：
序列总是在数据库中生成唯一值，即使当多个用户并发地引用同一序列时也没有可察觉的等待或锁定。
当多个用户使用 NEXTVAL 来增长序列时，每个用户生成一个其他用户不可见的唯一值。
当多个用户并发地增加同一序列时，每个用户看到的值是有差异的。
例如，一个用户可能从一个序列生成一组值，如 1、4、6 和 8，而另一个用户并发地从同一序列生成值 2、3、5 和 7。


假如在创建sequence时，有意不选用cache选项，有2点需要注意：
1． 访问效率降低，没有cache功能的sequence取值将无法直接访问内存
2． 不论是nocache还是cache , 每次访问nextval的过程都是不可逆的，
在同一session中，在执行一系列dml和sequence的操作后，用户执行rollback,希望将操作回滚，但是sequence此时就显得异常顽固，用掉的nextval将无法被重现。当下一次试图读取nextval时，sequence的指针又移动到下一位了。


还有sequence不属于某个表，也不属于某个字段，sequence仅仅属于某个用户。
其实在创建了sequence后，每个表都可以使用这个sequence，但是这样会引起应用的很多麻烦，因此，建议每个表都使用一个sequence。


5、限制 ：
NEXTVAL 和 CURRVAL 只在 SQL 语句中有效，并不在 PLS 语句中直接有效。（但是使用 NEXTVAL 和 CURRVAL 的 SQL 语句可用于 PLS 例程。）

在 CREATE TABLE 或 ALTER TABLE 语句中，在下列上下文中不能指定 NEXTVAL 或 CURRVAL：  
在 DEFAULT 子句中  
在检查约束中。

在 SELECT 语句中，下列上下文中不能指定 NEXTVAL 或 CURRVAL：  
使用 DISTINCT 关键字时在投影列表中。  
在 WHERE、GROUP BY 或 ORDER BY 子句中  
在子查询中  
在 UNION 运算符结合 SELECT 语句时。

在下列这些上下文中也不能指定 NEXTVAL 或 CURRVAL：  
在分段存储表达式中  
在对另一个数据库中的远程序列对象的引用中。


示例
在下面的例子中，假设没有其它用户并发地访问序列并且用户连续执行语句。
示例基于下列序列和表：  

CREATE SEQUENCE seq_2
    INCREMENT BY 1 START WITH 1
    MAXVALUE 30 MINVALUE 0
    NOCYCLE CACHE 10 ORDER;

CREATE TABLE tab1 (col1 int, col2 int);
INSERT INTO tab1 VALUES (0, 0);

可以在 INSERT 语句的 values 子句中使用 NEXTVAL（或 CURRVAL），如以下示例中所示：
INSERT INTO tab1 (col1, col2)
    VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)
   
在前面的例子中，数据库服务器把一个增加后的值（或序列的初始值，即 1）插入到表的 col1 和 col2 列。

可以在 UPDATE 语句的 SET 子句中使用 NEXTVAL（或 CURRVAL），如以下示例中所示：  
UPDATE tab1 SET col2 = seq_2.NEXTVAL WHERE col1 = 1;

在前面的例子中，seq_2 序列增长以后的值，即 2，替换了 col2 中 col1 等于 1 的值。

以下示例显示了如何在 SELECT 语句的 Projection 子句中使用 NEXTVAL 和 CURRVAL：

SELECT seq_2.CURRVAL, seq_2.NEXTVAL FROM tab1;

在前面的示例中，数据库服务器从 CURRVAL 和 NEXTVAL 表达式返回3 和 4。

转自：http://blog.csdn.net/cymm_liu/article/details/7451009