你的分享就是我们的动力 ---﹥

DB2?sequence

时间:2015-04-06 21:18来源:www.chengxuyuans.com 点击:

1、创建sequence

CREATE sequence my_seq AS INTEGER START WITH 628 increment BY

2、获取sequence的下一个

values nextval for my_seq(my_seq是sequence名称)

3、在sql中使用sequence的下一个值

insert into table_name(id) values(nextval for my_seq)

创建SEQUENCE:

Sql代码
  • CREATESEQUENCENAME_XXX_SEQASINTSTARTWITH1000 INCREMENTBY1 MINVALUE 1
  • NOMAXVALUENOCYCLENOCACHEORDER;
  • 查询SEQUENCE:

    Sql代码
  • SELECTNEXTVALFORNAME_XXX_SEQFROMSYSIBM.SYSDUMMY1;
  • 1、create

    CREATE SEQUENCE

    AS data-type 默认 As Integer

    START WITH

    INCREMENT BY 默认 INCREMENT BY 1

    MINVALUE | NO MINVALUE 默认 NO MINVALUE

    MAXVALUE | NO MAXVALUE 默认 NO MAXVALUE

    NO CYCLE | CYCLE 默认 NO CYCLE

    CACHE | NO CACHE 默认 CACHE 20

    NO ORDER | ORDER 默认 NO ORDER

    2、序列取值

    取下一个值:

    DB2: NEXTVAL FOR 序列 或 NEXT VALUE FOR 序列

    取当前值:

    DB2: PREVIOUS VALUE FOR 序列 或 PREVVAL FOR 序列

    3、序列设置开始值

    DB2:ALTER SEQUENCE 序列名 RESTART WITH 下一值

    4、修改

    修改最大值: ALTER SEQUENCE MAX VALUE | NO MAXVALUE

    修改最小值: ALTER SEQUENCE MIN VALUE | NO MINVALUE (此值需要比当前值小)

    修改步长: ALTER SEQUENCE INCREMENT BY ;

    修改CACHE值: ALTER SEQUENCE CACHE | NO CACHE

    修改循环属性: ALTER SEQUENCE

    修改排序属性:ALTER SEQUENCE

    从新计数: ALTER SEQUENCE RESTART | RESTART WITH

    5、DROP

    DB2: DROP SEQUENCE ;

    6、例子

    CREATE SEQUENCE MY_SEQ

    AS INTEGER

    START WITH 10001000

    INCREMENT BY 1

    MINVALUE 10000000

    MAXVALUE 69999999

    CYCLE

    NO CACHE

    ORDER;

    select nextval for MY_SEQ from sysibm.sysdummy1

    转载注明地址:http://www.chengxuyuans.com/DB2/92296.html