Oracle 10G中关于序列使用详解.

http://www.cnblogs.com/chenkai/archive/2010/02/23/1672039.html

数据库设计的三大范式第一条就是独立的表结构中必须有唯一主键来标识表中数据.在以往微软的SQL Server(duo版本)平台上.手动编码实现表中主键.并设定为自增列是极其简单.编码如下:

 --简单主键定义如下并设置为自增(只取出主键定义) 
 typeid int not null primary key identity(1,1),

在Oracle中如果要设定一个独立表结构的主键为自增.其中就涉及到了 Oracle 10G中关于序列(Sequence)的使用.

(A)Sequence-序列的定义语法

SEquence-序 列是一个数据库项.它生成一个整数序列..Oracle中序列所生成的整数通常可以用来填充独立表结构中数字类型的主键列.来实现同SQL Server自增效果. 但是序列的定义同SQL Server下定义区别很大.在Oracle 10G官方开发手册中显得有些复杂,先来看看定义序列的(精简后,如有详细请参考官方文档.)语法:

 

--定义序列完整语法
Create Sequence Sequence_Name
[Start With start_num]--自增开始初始值
[Increment by increment_num]--每次增量 
[{MaxValue maximum_num | NoMaxValue}]
[{MinValue minimum_num | NoMinValue}]
[{Cycle | NoCycle}]
[{Cache cache_num | NoCache}]
[{Order | NoOrder}];

 语法定义说明:

Sequence_Name:定义序列的名称[有意义的命名].

Start With (start_num):用来定义序列的初始值.可选. 系统默认值为1.

Increment By(increment_num):指定序列每次自增的增量. 可选. 系统默认值同样为1.

MaxValue(maximum_num):设定序列自增最大上限整数值.maximum_num必须大于或等于start_num即初始值.同时maximum_num必须大于序列最小下限值minimum_num.[保证有意以].

NoMaxValue:是系统对序列设置的默认值. 即指定升序序列的最大值为10的27次方.降序序列的最大值为-1. NoMaxValue为系统默认值. 同理MinValue.

MinValue(minimum_num):设定序列自增最小下限整数值. ,minmum_num必须小于或等于start_num即初始值. 而且minimum_num必须小于maximum_num.同理.

NoMinvalue:即使序列自增下限的默认值. 升序序列的最小值为1, 降序序列最小为负的10的26次方. NoMinValue为默认值.

Cycle:指定当序列即使已经达到序列自增的最大值或最小值时也继续生成整数. 当升序序列达到最大值时. 下一个生成的值为最小值即初始值. 当降序序列达到最小值时. 下一个生成的值最大值. 以这种规则来循环.

NoCycle:正好反之:指定到序列自增到最大值或最小值时就不能再生成整数了. NoCycle是默认值.

Cache(cache_num):指定要保留在内存中整数的个数.默认缓存的格式为20个. 可以缓存的整数最少为2个. 可以缓存的整数个数最多为:Cell(maximum_num—minimum_num)/ABS(increment_num). 算法即:Cell(序列的最大上限值—最小下限值,)/ABS(每次自增的增量).

NoCache:指定不适用缓存整数数据.【个人建议不适用缓存来存储数据. 当数据库连接断开后.自动清空内存数据, 导致后面插入数据序列值不连续.效果  建议在创建不适用Cache存储 后有详解】.

Order:确保按照请求次序生成整数 【不常用的设置】.只有在使用Real Application Cluster(RAC)时才可以使用Order选项设置.

Noorder:就是(以上)反之.(不在赘述) NoOrder为系统默认值.

详细了解sequence序列的详细语法定义. 定义一个简单的Sequence序列.并使用在表中主键列中实现自增.代码如下:

1 --创建序列
2 create sequence product_sequence
3 start with 1 --初始值默认为1
4 increment by 1--每次增量默认为1
5 maxvalue 10 --增量的最大上限
6 minvalue 1--增量的最小底限
7 cycle
8 nocache ;

 (B)Use Sequence-使用序列

 序列生成的是一系列整数数字.一个序列中包含两个"伪列" ，分别为"Currval"和"Nextval",可以分别用来获取该序列的当前值和下一个值.

虽然我们在定义时指定序列product_sequence初始值为1但并没有真正初始化该值. 当在检索序列的当前值前,必须通过检索序列的下一个值即Nextval来对序列进行初始化操作.在选择了product_sequence.Nextval时,该序列就被初始化为1.如下为实例.

 1 --初始化序列
 2 select product_sequence.nextval from ProductType
 3 
 4 --ProductType为表明.在初始化前 必须先创建表ProductType 如下:
 5 create table Producttype
 6 (
 7   typeid integer constraint producttype_pk primary key,
 8   typename varchar(120) default'CastChen Test Date!' not null,
 9   createdate date  default sysdate not null
10 ); 

执行查询结果如下:

上述为第一次执行后. 序列中初始化的值为定义时1. 如果在定义时没有指定初始值. 默认值为1. 成功初始化后我们来获得当前序列的值.

1 --获取序列当前值
2 select product_sequence.currval from ProductType

查询结果如下:

当查询Currval时,Nextval保持不变.而nextval只有在再次查询nextval以获得下一个值是才会改变. 下面做一个实例. 注意nextval和Currval先后顺序.

1 --同时查看两个伪列的值. 注意Nextval 在前 Currval在后.
2 select product_sequence.nextval, product_sequence.currval from ProductType

 查询结果如下:

当查询两个值时Nextval在前. 前面提到再次查询Nextval可以获得序列的下一个值. 所以此处序列的当前值Currval为2.其实可以理解这样这个过程: Nextval其实通过查询来向序列赋值的,而赋值对象就是Currval. Currval同时又是对外访问序列值的唯一窗口.product_sequence.currval 就直接取到了序列的当前值.同理我们可以把product_sequence.currval放在主键赋值, 而不用手动每次定义.  是否又再一次接近我们目的.

注意我们在定义使用了Cycle选项:即当序列自增到最大值或最小值时产生循环效果. 我们来看下实例. 

--一直执行赋值语句 直到达到序列上限的最大值10
select product_sequence.nextval from ProductType

 执行最后结果:

 当我们再次执行赋值语句时 结果:

 序列的值有最大上限10 又再次恢复到初始值1,实现一次循环. 如果再次执行其实同第一次执行时一样的,Cycle实现序列在一个特定范围内循环定义使用.

(C)使用序列填充主键-(核心内容)

上面做的都是准备工作, 我们要的最后结果即是在主键列中通过序列来自动赋值.当然前提表的主键定义时数据类型必须为整数. 在这里我还要再次提一下Cache这个选项.在使用序列填充主键时,常用设置为NoCache即采取默认方式. 当关闭数据库连接时所缓存的值会全部丢失. 导致主键产生的数值不连续的现象. 使用序列来填充主键标识:

1 --插入数据 时 序列值代替主键值定义
2 insert into ProductType(typeid,Typename,Createdate) 
3 values(product_sequence.nextval,'使用了序列定义主键',default)

这种方式不必每次添加数据时查看主键的数值. 害怕插入重复数据. 而主键列的赋值工作完全交给序列来做. 我只需把精力投放在其他地方.

上面操作基本实现我们当初预想. 但我也想提出一个问题:

在微软的SQL Server平台上定义独立表结构的主键时. 可以同时指定多个列共同标识为该表的主键. 即如果多个合并在一起比对实现主键唯一标识. 而Oracle 10G中序列其实就分离这种关系. 两者之间相互独立. 也就是说主键的定义和主键赋值 是没有关-系的. 完全分开的. 序列只是负责独立的对主键进行赋值. 而至于主键约束的定义 没有关系.

(D)修改和删除序列

可以通过Alert  Sequence子句来修改序列, 但是我在修改序列中常常会报错 而且很频繁.修改序列内容有如下限制:

(1)不能修改序列的初始值

(2)序列的最小值不能大于当前值

(3)序列的最大值不能小于当前值

修改序列的增量:

1 --修改序列的增量为2
2 alert sequence product_sequence increment by 2;

再次查询数据结果将以每次增量为2 增加. 如果在不使用情况下删除序列:

1 --删除序列 极其简单.
2 drop  sequence product_sequence;

当我们添加多张表表中序列时, 我们可以同查询系统中User_sequences表来获取相关表的关于序列的详细信息. User_sequences是字典表的一部分.

至此以上是在Oracle 10G关于序列的全部用法.