How to use ORACLE sequence:
An Oracle sequence is a database item that can generate an integer sequence. It is usually used to fill in a numeric primary key column.
1. Create sequence:
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:
Start_num: sequence start integer
Increment_num: the incremental integer added each time.
Maximum: The maximum integer of a sequence.
Minimum: minimum integer of a sequence
Cycle: Generate cyclically
Nocycle: Non-cyclic generation, default value
Cache_num: Number of integers stored in memory
Nocache: no integer is cached.
For example:
Create sequence seq_test; (default)
Create sequence seq_test start with 1 increment by 2;
2. Use sequence:
Select seq_test.nextval from dual;
Insert into order_status2 (ID) values (seq_test.nextval );
3. Modify the sequence:
Alter sequence statement to modify the sequence. Modifiable content:
Sequence initial values cannot be modified.
The minimum value of the sequence cannot be greater than the current value.
The maximum value of the sequence cannot be smaller than the current value.
Example: Alter sequence seq_test increment by 3;
4. Delete sequence:
Drop sequence seq_test;