序列的语法为
需要注意的是:
1、 未指定MINVALUE,那就使用系统分配的默认值1(官方文档这样描述: Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(10 27 -1) for a descending sequence. This is the default.)
2、START WITH指定的值必须大于等于MINVALUE.
案例一:未指定MINVALUE,序列再次循环时开始值为1
执行:
SELECT seq_test1.nextval FROM dual;
NEXTVAL
----------
100
/
NEXTVAL
----------
150
/
NEXTVAL
----------
200
/
NEXTVAL
----------
1
案例二:若指定MINVALUE,再次循环时从指定的MINVALUE开始
执行:
SELECT seq_test1.nextval FROM dual;
NEXTVAL
----------
100
/
NEXTVAL
----------
150
/
NEXTVAL
----------
200
/
NEXTVAL
----------
0
案例三:START WITH值必须大于MINVALUE
错误信息为:ORA-04006: START WITH 不能小于 MINVALUE
点击(此处)折叠或打开
- CREATE SEQUENCE seq_name
- [INCREMENT BY n]
- [START WITH n]
- [{MAXVALUE n | NOMAXVALUE}]
- [{MINVALUE n | NOMINVALUE}]
- [{CYCLE | NOCYCLE}]
- [{CACHE n | NOCACHE}]
1、 未指定MINVALUE,那就使用系统分配的默认值1(官方文档这样描述: Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(10 27 -1) for a descending sequence. This is the default.)
2、START WITH指定的值必须大于等于MINVALUE.
案例一:未指定MINVALUE,序列再次循环时开始值为1
点击(此处)折叠或打开
- create sequence seq_test1
- start with 100
- increment by 50
- maxvalue 200
- cycle
- nocache;
SELECT seq_test1.nextval FROM dual;
NEXTVAL
----------
100
/
NEXTVAL
----------
150
/
NEXTVAL
----------
200
/
NEXTVAL
----------
1
案例二:若指定MINVALUE,再次循环时从指定的MINVALUE开始
点击(此处)折叠或打开
- DROP SEQUENCE seq_test1;
- create sequence seq_test1
- start with 100
- increment by 50
- maxvalue 200
- MINVALUE 0
- cycle
- nocache;
执行:
SELECT seq_test1.nextval FROM dual;
NEXTVAL
----------
100
/
NEXTVAL
----------
150
/
NEXTVAL
----------
200
/
NEXTVAL
----------
0
案例三:START WITH值必须大于MINVALUE
点击(此处)折叠或打开
- DROP SEQUENCE seq_test1;
- create sequence seq_test1
- start with 0
- increment by 50
- maxvalue 200
- MINVALUE 1
- cycle
- nocache
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29782424/viewspace-2127315/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29782424/viewspace-2127315/