db2序列

2019/02/13 09:36
阅读数 96

DB2创建序列

一、创建序列

序列是按照一定的规则生产的数值,序列的作用非常的大,比如银行交易中的流水号,就是记录每笔交易的关键字段。

通过create sequence语句创建序列,具体语法如下:

>>-CREATE--+------------+--SEQUENCE--sequence-name--·----------->

           '-OR REPLACE-'                               

 

 

   .-AS INTEGER--------.                                        

>--+-------------------+--·--+------------------------------+--->

   '-AS--| data-type |-'     '-START WITH--numeric-constant-'   

 

 

      .-INCREMENT BY 1-----------------.      

>--·--+--------------------------------+--·--------------------->

      '-INCREMENT BY--numeric-constant-'      

 

 

   .-NO MINVALUE----------------.      

>--+----------------------------+--·---------------------------->

   '-MINVALUE--numeric-constant-'      

 

 

   .-NO MAXVALUE----------------.     .-NO CYCLE-.      

>--+----------------------------+--·--+----------+--·----------->

   '-MAXVALUE--numeric-constant-'     '-CYCLE----'      

 

 

   .-CACHE 20----------------.     .-NO ORDER-.      

>--+-------------------------+--·--+----------+--·-------------><

   +-CACHE--integer-constant-+     '-ORDER----'      

   '-NO CACHE----------------' 

简化写如下:

create sequence seq_name

start with a

increment by b

minvalue c

maxvalue d

no cycle

cache e

order

参数简介如下:

or replace:若有创建序列重名的序列,那么将会被替代(先删除,再创建)

as datatype:指定数据类型,这里说明下,序列的数据类型只能为数值型,

            如,smallint,integer,bigint,不带小数点的decimal类型。

start with:指定序列的起始值,默认情况下,对于升序的序列是当前指定数据类型的最小值,

            对于降序序列的是当前指定类型的最大值。

increment by:指定增长的值,默认值为1,正数表示此序列为增长升序的,负数表示此序列为降序的。

minvalue:最小值,若降序序列,no cycle的话,到此值的时候就不再生成序列值了;

no minvalue:对于升序序列来说,此值为start with的值,如果start with值未指定的话就是1.

             对于降序序列来说,此值就是指定数据类型的最小值。

maxvalue:指定生产序列的最大值,

no maxvalue:对于升序序列来说,此值为指定数据类型的最大值;

             对于降序序列来说,此值为start with值,若未指定start with值的话就是-1.

cycle:循环使用数据值,对于升序序列来说,当达到了最大值之后,下一个值将会是其最小值;

       对于降序序列来说,达到最小之后,下一个序列值为其最大值。

no cycle:当达到序列的边界值之后,就不再产生序列值,默认选项。

cache:缓存序列值,表示每次应用此序列的时候,预先生产并存放在内存中的序列值。

       其作用是有效的降低了写日志的I/O操作。

       若在使用的过程中,出现系统错误的话,那么所有这些缓存值将会丢失。

       最小值为2,默认为20

no cache:当指定此选项的时候,内存中不会存储任何序列值,无论出现什么异常现象都不会影响到此序列,

          每次生存新的序列值,都会导致写日志的I/O操作。

order:按照请求的顺序生成值。

no order:不会按照请求的顺序生成值,默认情况。

 

下面创建一个序列seq_001:

create sequence seq_001 

start with 1 

increment by 1 

no maxvalue 

no cycle 

cache 21

DB20000I  SQL 命令成功完成。

 

 

查看编目表中存放的序列seq_001的信息:

SELECT SEQNAME,START,INCREMENT,MINVALUE,MAXVALUE,CYCLE,CACHE,ORDER 

FROM SYSCAT.SEQUENCES WHERE SEQNAME='SEQ_001'

SEQNAME     START      INCREMENT     MINVALUE     MAXVALUE     CYCLE   CACHE     ORDER

--------- ----------   -----------   ----------   ------------ ------   ------- --------

SEQ_001      1.        1.              1.          2147483647.  N         21         N

 

 

  1 条记录已选择。

 

 

注意事项:

1、常量序列,也就是一个不会改变其生成值的序列。

   创建的时候,指定增长值为0,即increment by 0,不过start with值不能超过其数据类型的最大最小值;

   另一种方法,指定start with值,minvalue值,和maxvalue值相等。

2、当序列定义为no cycle,可以通过alter sequence改变其属性,让其达到边界值之后还能继续产生序列值,

   即从no cycle修改为cycle

3、当定义序列时指定cycle,除了increment by 1或者-1之外,序列生成的最大值将不是指定数据类型的最大值;

   如,一个序列定义为start with=1,increment=2,maxvalue=10,则能够生成的最大值将会是9,而不是10.

4、序列的定义者拥有序列的alter和usage特权(with grant option,可将其授予其他用户),

   序列的拥有者可以删除序列。

5、下面2点使用与所有DB2数据库版本,非标准信息:

   1).A comma can be used to separate multiple sequence options

   2).novinvalue,nomaxvalue,nocycle,nocache,noorder可以替代

       no minvalue,no maxvalue,no cycle,no cache,no order。

 

二、修改序列

语法如下:

>>-ALTER SEQUENCE--sequence-name-------------------------------->

 

 

   .-----------------------------------------------.   

   V  (1)                                          |   

>----------+-RESTART--+------------------------+-+-+-----------><

           |          '-WITH--numeric-constant-' |     

           +-INCREMENT BY--numeric-constant------+     

           +-+-MINVALUE--numeric-constant-+------+     

           | '-NO MINVALUE----------------'      |     

           +-+-MAXVALUE--numeric-constant-+------+     

           | '-NO MAXVALUE----------------'      |     

           +-+-CYCLE----+------------------------+     

           | '-NO CYCLE-'                        |     

           +-+-CACHE--integer-constant-+---------+     

           | '-NO CACHE----------------'         |     

           '-+-ORDER----+------------------------'     

             '-NO ORDER-' 

参数简介:

restart:重置序列,如果没有指定with n时,序列将按照create sequence时指定参数去产生值。

with:重置序列,并按照重新指定的值去生成序列值,可以使任意值。

其他参数与create sequence时一样的。

从语法中可以看出,可以修改的属性如下:

1、序列其实值(或重置)

2、increment值

3、最大最小值

4、cache值

5、当达到边界值的时候,是否循环产生序列值

6、是否按照请求顺序产生序列值

 

 

注意:

1、序列的数据类型不能修改,若要修改,只能删除当前序列,重建时指定想要的数据类型。

2、当修改的时候,所有的缓存值将会丢失。

3、当将序列修改为cycle之后,序列将会产生重复的值。

 

 

下面是一个实例:

alter sequence seq_001 increment by 2 maxvalue 20 cycle no cache

DB20000I  SQL 命令成功完成。

SELECT SEQNAME,START,INCREMENT,MINVALUE,MAXVALUE,CYCLE,CACHE,ORDER 

FROM SYSCAT.SEQUENCES WHERE SEQNAME='SEQ_001'

SEQNAME     START      INCREMENT     MINVALUE     MAXVALUE     CYCLE   CACHE     ORDER

--------- ----------   -----------   ----------   ------------ ------   ------- --------

SEQ_001      1.        2.              1.          20.          Y          1         N

 

 

  1 条记录已选择。

我们可以看出no cache其实是cache 1。

 

 

三、序列的使用

可以通过两个表达式来获取序列的值,next value获取下一个值,previous value获取当前值;

为了保持DB2向后的兼容,也可以使用nextval和prevval获取下一个序列值和当前序列值。

当首次引用序列的时候,只能使用next value获取其第一个值;否则将会得到下面的错误信息:

db2 => insert into test1(cid,cname,sex,age) values(previous value for seq_001,'scott','M',30)

DB21034E  该命令被当作 SQL 语句来处理,因为它是无效的“命令行处理器”命令。在

SQL 处理期间,它返回:

SQL0845N  在 NEXT VALUE 表达式在序列 "SEQID = 7"

的当前会话中生成值之前,不能使用 PREVIOUS VALUE 表达式。  SQLSTATE=51035

 

 

db2 => insert into test1(cid,cname,sex,age) values(next value for seq_001,'scott','M',30)

DB20000I  SQL 命令成功完成。

db2 => select * from test1

 

 

CID        CNAME                SEX AGE

---------- -------------------- --- -----------

1          scott                M            30

 

 

  1 条记录已选择。

 

next value和previous value可以使用在select,values,insert,和update语句中,不能使用在where语句中。

 

--the end--

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部