数据库序列

创建序列

SQL> create sequence dept_seq increment by 10 start with 10 maxvalue 20 cycle nocache;
Sequence created

查询序列

SQL> select sequence_name from user_sequences;
SEQUENCE_NAME
------------------------------
DEPARTMENTS_SEQ
DEPT_SEQ
EMPLOYEES_SEQ
LOCATIONS_SEQ

使用序列

使用序列下一个值 序列名.nextval

SQL> select dept_seq.nextval from dual;

使用序列当前值 序列名.currval

SQL> select dept_seq.currval from dual;
   CURRVAL
----------
        11

使用序列作为主键插入到表中

SQL> select * from fkey;
        ID NAME
---------- ----------

SQL> insert into fkey values(dept_seq.nextval, 'tommy');
1 row inserted

SQL> select * from fkey;
        ID NAME
---------- ----------
        10 tommy

修改序列

SQL> alter sequence dept_seq increment by 20 maxvalue 999999 nocycle cache 20;
Sequence altered

无法修改 start with 数字，除非重新创建序列
SQL> alter sequence dept_seq increment by 20 start with 100 maxvalue 9999 nocycly cache 20;
alter sequence dept_seq increment by 20 start with 100 maxvalue 9999 nocycly cache 20
ORA-02283: 无法变更启动序列号

删除序列

SQL> drop sequence dept_seq;
Sequence dropped