频道栏目
首页 > 资讯 > Sybase > 正文

Sequence

15-05-20        来源:[db:作者]  
收藏   我要投稿

CREATE SEQUENCE

Purpose
Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.
Note on Using Sequences with Deferred Segments If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.
See Also:

Chapter 2, "Pseudocolumns" for more information on using the CURRVAL and NEXTVAL

"How to Use Sequence Values" for information on using sequences

ALTER SEQUENCE or DROP SEQUENCE for information on modifying or dropping a sequence

Prerequisites(先决条件)

To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege.

To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege.

Syntax(语法)

create_sequence::=

De.ion of create_sequence.gif follows

序列是一种数据库项,它生成一个整数的序列,生成的整数通常可以用来填充数字类型的主键列。
我们先创建一个序列:
SQL> create sequence text
2 ;
序列已创建。
上面是不带参数的序列,我们创建带参数的序列:
SQL> create sequence text_1 start with 10 increment by 5 minvalue 10 maxvalue 20
2 cycle cache 2 order;

序列已创建。
SQL> select * from dba_sequences where sequence_name like '%TEXT%';

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O
------------------------------ ------------------------------ ---------- ---------- ------------ - -
CACHE_SIZE LAST_NUMBER
---------- -----------
SYS TEXT 1 1.0000E+28 1 N N
20 1


SYS TEXT_1 10 20 5 Y Y
2 10

怎么使用序列呢?下面我们做下小实验:
序列生成一系列数字,一个序列包含两个“伪列”,分别是currval和nextval,可以分别取用该序列的当前值和下一个值。
在检索序列的当前值之前,必须通过检索序列的下一个值对序列进行初始化:
SQL> select text.nextval from dual;
NEXTVAL
----------
1
SQL> select text.currval from dual;

CURRVAL
----------
1
SQL> select text.nextval from dual;
NEXTVAL
----------
2
SQL> select text.currval from dual;
CURRVAL
----------
2
SQL> select text.nextval from dual;

NEXTVAL
----------
3
==========================================================================


SQL> select text_1.currval from dual;
select text_1.currval from dual
*
第 1 行出现错误:
ORA-08002: 序列 TEXT_1.CURRVAL 尚未在此会话中定义
SQL> select text_1.nextval from dual;

NEXTVAL
----------
10
SQL> select text_1.currval from dual;

CURRVAL
----------
10
SQL> select text_1.nextval from dual;


NEXTVAL
----------
15


SQL> select text_1.nextval from dual;


NEXTVAL
----------
20


SQL> select text_1.currval from dual;


CURRVAL
----------
20

======================================================
使用序列填充主键:

当表的主键是整数时,可以用序列来生成主键:
SQL> create table t_text(id integer constraint t_text_pk primary key);

表已创建。


SQL> create sequence text_te nocache;


序列已创建。

在使用序列填充主键时,通常会选择使用nocache,这样可以避免序列不连续的情况,序列不连续的情况,之所以会发生不连续的情况,是因为关闭数据库时,所缓存的
值将全部丢失。但是使用nocache会降低性能。但是如果可以容忍主键不连续,那就用cache
SQL> insert into t_text values (text_te.nextval);


已创建 1 行。


SQL> select * from t_text;


ID
----------
1


SQL> insert into t_text values (text_te.nextval);


已创建 1 行。


SQL> select * from t_text;


ID
----------
1
2


===================================
修改序列,但是有限制:
不能修改序列的初值
序列的最小值不能大于当前值
序列的最大值不能小于当前值


SQL> ALTER SEQUENCE text_te increment by 5;

序列已更改。



SQL> insert into t_text values (text_te.nextval);


已创建 1 行。


SQL> select * from t_text;


ID
----------
1
2
7


=====================================================
删除序列:
drop sequence sequence_name;
相关TAG标签
上一篇:MYSQL中宽度指示器
下一篇:umount不了磁盘或者网络设备
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站