序列的使用

SQL> create sequence seq
  2  minvalue 1
  3  nomaxvalue
  4  start with 21
  5  increment by 1
  6  cache 20;
序列已创建。
SQL> select seq.curral from dual;
select seq.curral from dual
      *
第 1 行出现错误:
ORA-00904: "SEQ"."CURRAL": 标识符无效
SQL> select seq.nextval from dual;
   NEXTVAL                                                                     
----------                                                                     
        21                                                                     
SQL> select seq.currval from dual;
   CURRVAL                                                                     
----------                                                                     
        21          

------------注意-----------------

 - 第一次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防止这种情况。                                                            

SQL> select seq.nextval from dual;
   NEXTVAL                                                                     
----------                                                                     
        22                                                                     
SQL> select seq.currval from dual;
   CURRVAL                                                                     
----------                                                                     
        22                                                                     
SQL> select seq.currval from dual;
   CURRVAL                                                                     
----------                                                                     
        22                                                                     
SQL> select seq.currval from dual;
   CURRVAL                                                                     
----------                                                                     
        22                                                                     
SQL> select seq.nextval from dual;
   NEXTVAL                                                                     
----------                                                                     
        23                                                                     
SQL> select seq.currval from dual;
   CURRVAL                                                                     
----------                                                                     
        23                                                                     
--------------------------------

2、 Alter sequence
  你或者是该sequence的owner，或者有ALTER ANY sequence权限才能改动sequence。 可
以alter除start值之外的所有sequence参数。如果想要改变start值，必须drop sequence
再re-create。例子：
  ALTER sequence emp_sequence
  INCREMENT BY 10
  MAXVALUE 10000
  CYCLE -- 到10000后从头开始
  NOCACHE;

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