序列

序列
序列不占用实际的存储空间,在数据字典中存储序列的定义描述

dba_sequences
all_sequences
user_sequences

create sequence sequence_name
[start with integer]
[increment by integer]
[Maxvalue integer|NoMaxValue]  递增序列最大值为(10^28)-1
[Minvalue integer|NoMinValue]  递减序列的最大值为-1
[Cache|NoCache]  预先分配并保留在内存中的值的个数为20
[Cache integer|NoCache];

SQL> create sequence departments_seq
     start with 10
     increment by 10
     maxvalue 999
     nocache;

序列的伪列属性:
    1. NextVal: 产生一个序列值
    2. CurrVal: 返回序列的当前值

序列值不能重复生成,一个序列只能被使用一次,一个序列通常只被一个数据库对象占用
序列的start with参数不能被修改

SQL> alter sequence departments_seq 
     increment by 100 
     maxvalue 15000 
     cycle cache 30;

 

SQL> create sequence seq_jack2 start with 100 increment by 1 maxvalue 1000 cache 20 cycle;

Sequence created.

SQL> select seq_jack2.currval from dual;
select seq_jack2.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence SEQ_JACK2.CURRVAL is not yet defined in this session


SQL> select seq_jack2.nextval from dual;

   NEXTVAL
----------
       100

SQL> select seq_jack2.currval from dual;

   CURRVAL
----------
       100

SQL>