| 5. 1. 1. Sequences |
|
|
A sequence is a database item that generates a sequence of integers. |
You typically use the integers generated by a sequence to populate a numeric primary key column. |
You create a sequence using the CREATE SEQUENCE statement, which has the following syntax: |
|
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 } ];
|
|
where |
- The default start_num is 1.
- The default increment number is 1.
- The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
- minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
- NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
- NOMINVALUE is the default.
- maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
- NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
- NOMAXVALUE is the default.
- CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
- When an ascending sequence reaches its maximum value, the next value generated is the minimum.
- When a descending sequence reaches its minimum value, the next value generated is the maximum.
- NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
- NOCYCLE is the default.
- CACHE cache_num specifies the number of integers to keep in memory.
- The default number of integers to cache is 20.
- The minimum number of integers that may be cached is 2.
- The maximum integers that may be cached is determined by the formula CEIL(maximum_num - minimum_num)/ABS(increment_num).
- NOCACHE specifies no integers are to be stored.
- ORDER guarantees the integers are generated in the order of the request.
- You typically use ORDER when using Real Application Clusters.
- NOORDER doesn't guarantee the integers are generated in the order of the request.
- NOORDER is the default.
|
Quote from: |
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback) |
# Paperback: 608 pages |
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004) |
# Language: English |
# ISBN-10: 0072229810 |
# ISBN-13: 978-0072229813 |
|
SQL>
SQL>
SQL> create sequence LOGICAL_ASSIGNMENT_ID_SEQ
2 start with 1;
Sequence created.
SQL>
SQL> drop sequence LOGICAL_ASSIGNMENT_ID_SEQ;
Sequence dropped.
SQL>
SQL>
|
|