Oracle:序列

序列

是一种数据库项，可以生成整数序列。

1.创建序列

create sequence s_teststart with 10 increment by 5minvalue 10 maxvalue 20cycle cache 2 order;

2.获取有关序列的信息

selectsequence_name, min_value, max_value, increment_by, cycle_flag,order_flag, cache_size, last_numberfrom user_sequencesorder by sequence_name;

3.使用序列

select s_test.nextvalfrom dual;NEXTVAL--------1select s_test.currvalfrom dual;CURRVAL--------1

4.使用序列填充主键

create table order (id integer constraint order_pk primary key,status varchar2(10);last_modified DATE default sysdate);create sequence s_order nocache;insert into order(id, status, last_modified)values(s_order.nextval, 'placed', '2017-11-09');

5.使用序列指定默认列值

create sequence s_default_value_for_column;create table test_with_sequence(id integer constraint test_with_pk primary key,sequence_value integer default s_default_value_for_column.nextval)

6.使用标识列

oracle database 12c的新功能

create table test_with_identity(    id integer constraint test_with__identity_pk primary key,   identity_value integer generated by default as identity(    start with 5 increment by 2 ) )

7.修改序列

alter sequence s_test increment by 2;

8.删除序列

drop sequence s_test;