| 1 |
------------------- |
| 2 |
Sequence generators |
| 3 |
------------------- |
| 4 |
|
| 5 |
Function: |
| 6 |
A sequence generator is a mechanism for generating successive exact |
| 7 |
numeric values, one at a time. A sequence generator is a named schema |
| 8 |
object. |
| 9 |
|
| 10 |
Syntax rules: |
| 11 |
CREATE { SEQUENCE | GENERATOR } <name> |
| 12 |
DROP { SEQUENCE | GENERATOR } <name> |
| 13 |
SET GENERATOR <name> TO <start_value> |
| 14 |
ALTER SEQUENCE <name> RESTART WITH <start_value> |
| 15 |
GEN_ID (<name>, <increment_value>) |
| 16 |
NEXT VALUE FOR <name> |
| 17 |
|
| 18 |
Type: |
| 19 |
INTEGER in dialect 1, BIGINT in dialect 3 |
| 20 |
|
| 21 |
Example(s): |
| 22 |
1. CREATE SEQUENCE S_EMPLOYEE; |
| 23 |
2. ALTER SEQUENCE S_EMPLOYEE RESTART WITH 0; |
| 24 |
3. SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE; |
| 25 |
4. INSERT INTO EMPLOYEE (ID, NAME) VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith'); |
| 26 |
|
| 27 |
Note(s): |
| 28 |
1. SEQUENCE is a syntax term declared in the SQL specification, while |
| 29 |
GENERATOR is a legacy InterBase syntax term. It's recommended to use |
| 30 |
the standard SEQUENCE syntax in your applications. |
| 31 |
2. Currently, increment values not equal to 1 (one) could be used only via |
| 32 |
the GEN_ID function. The future versions are expected to provide full |
| 33 |
support for SQL-99 sequence generators (which allows the required |
| 34 |
increment values to be specified at the DDL level), so it's recommended |
| 35 |
to get new sequential values via NEXT VALUE FOR value expression instead |
| 36 |
of the GEN_ID function, unless it's vitally important to use other |
| 37 |
increment values. |
| 38 |
3. GEN_ID(<name>, 0) allows you to retrieve the current sequence value, |
| 39 |
but it should be never used in insert/update statements, as it produces a |
| 40 |
high risk of uniqueness violations in a concurrent environment. |