Oracle创建序列、获取有关序列的信息、使用序列

序列

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

1.创建序列

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

2.获取有关序列的信息

select
sequence_name, min_value, max_value, increment_by, cycle_flag,order_flag, cache_size, last_number
from user_sequences
order by sequence_name;

3.使用序列

select s_test.nextval
from dual;

NEXTVAL
--------
1


select s_test.currval
from 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;