oracle序列信息,Oracle创建序列、获取有关序列的信息、使用序列-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;