Oracle、DB2、PostgreSQL之Sequence总结
Sequence是数据库系统按照一定规则自动增加的数字序列。这个序列一般作为代理主键(因为不会重复),没有其他任何意义。
Sequence是数据库系统的特性,有的数据库有Sequence,有的没有。比如Oracle、DB2、PostgreSQL数据库有Sequence,MySQL、SQL Server、Sybase等数据库没有Sequence。
根据我个人理解,Sequence是数据中一个特殊存放等差数列的表,该表受数据库系统控制,任何时候数据库系统都可以根据当前记录数大小加上步长来获取到该表下一条记录应该是多少,这个表没有实际意义,常常用来做主键用,非常不错,呵呵,不过很郁闷的各个数据库厂商尿不到一个壶里--各有各的一套对Sequence的定义和操作。在此我对常见三种数据库的Sequence的定义和操作做一个对比和总结,以便日后查看。
一、定义Sequence
定义一个seq_test,最小值为10000,最大值为99999999999999999,从20000开始,增量的步长为1,缓存为20的循环排序Sequence。
Oracle的定义方法:
create sequence seq_test
minvalue 10000
maxvalue 99999999999999999
start with 20000
increment by 1
cache 20
cycle
order;
DB2的写法:
create sequence seq_test
as bigint
start with 20000
increment by 1
minvalue 10000
maxvalue 99999999999999999
cycle
cache 20
order;
PostgreSQL的写法:
create sequence seq_test
increment by 1
minvalue 10000
maxvalue 99999999999999999
start 20000
cache 20
cycle;
二、Oracle、DB2、PostgreSQL数据库Sequence值的引用参数为:currval、nextval,分别表示当前值和下一个值。下面分别从三个数据库的Sequence中获取nextval的值。
Oracle中:seq_test.nextval
例如:select seq_test.nextval from dual;
DB2中:nextval for seq_test
例如:values nextval for seq_test;
PostgreSQL中:nextval(seq_test)
例如:select nextval(seq_test);
三、Sequence与indentity的区别与联系
Sequence与indentity的基本作用都差不多。都可以生成自增数字序列。
Sequence是数据库系统中的一个对象,可以在整个数据库中使用,和表没有任何关系;indentity仅仅是指定在表中某一列上,作用范围就是这个表。
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/dingmao6790229/archive/2009/05/07/4156840.aspx
在首次宣布 Sequence 对象时,我认为它们只是为了将应用程序从其他数据库移植到 DB2 而向 DB2 添加的另一功能。但是我越是深入地研究它们,就越是开始喜欢这些新的对象了,因为它们消除了我们对于 Identity 列的许多限制,而且它们还具有许多独特且有用的功能。它还向我们提供了数字生成的另一备选方法。
- 无需等待值的增加;
- 独立的连续数字生成对象(不与表关联);
- 递增或递减地生成数字;
- 用于从其他数据库移植应用程序;
- 可以生成键,用以跨多个表(RI 或相关的应用程序)协调各键。
下面这个例子展示了一个 Sequence 对象的创建及其简单用法。
CREATE SEQUENCE ACCOUNT_SEQ
AS INTEGER
START WITH 1
INCREMENT BY 10
CYCLE
CACHE 20
- SELECT 和 SELECT INTO 语句;
- INSERT 语句里面的 fullselect 的 SELECT 从句;
- UPDATE 语句里面的 SET 从句(搜索或定位);
- SET 主变量;
- VALUES 或 VALUES INTO;
- CREATE PROCEDURE、FUNCTION、TRIGGER。
假定 ACCT_SEQ 为 START WITH 10 INCREMENT BY 10
正如您所看到的,使用 Sequence 对象代替 Identity 列有许多好处。下面是两者的一个简单比较。
Sequence 对象与 Identity 列 | |
Sequence 对象 | Identity 列(带有 V8 功能) |
单独的 Sequence 对象是在用户请求时创建的 | 由 DB2 生成/维护和填充的内部 Sequence 对象 |
可用于它们所选择的任何目的,并且存在有多个 | 与特定的表相关联,并且只能有一个 |
循环(CYCLE)将回绕和重复,并且无需进行惟一性考虑 | 如果惟一索引在 Identity 列上并且创建了复制,那么循环(CYCLE)可能会产生问题 |
当用于填充表时,后来可被更新 | 如果是 GENERATED ALWAYS,就无法被更新 |
可以更改(ALTER)Sequence 对象的属性。 还可以进行注释(COMMENT)以及授予/撤销(GRANT/REVOKE)权限 | 只能更改表(ALTER TABLE)(如果向已填充的表添加 Identity 列,将被置于 REORG 暂挂状态) |
可被删除 | 不能从表中删除* |
支持 NEXT VALUE FOR EXPRESSION 和 PREVIOUS VALUE FOR EXPRESSION | 必须使用 ID_VAL_LOCAL 并且只返回用户提交(commit)范围内的最后值 |
* 如果今后设计中使用 Sequence 对象比使用 Identity 列更好,那么在选择 Identity 列时就要考虑仔细。如果它们是在已填充的表上定义的并且需要被删除,那么必须删除表然后重新创建。这对于高效环境中的大型表可能是一个大问题。 |