Learn how to use sequences in Oracle

1. Create a sequence

Run the following command to create a sequence ):

CreateSequence test_sequence
IncrementBy 1 --Data added each time
StartWith 1 --Count from 1
Nomaxvalue  --Do not set the maximum value
Nocycle  --Always accumulate, not loop
Cache10;
[Note]
If the cache value is set, Oracle will pre-place some sequence in the memory to make access faster. After the cache is obtained, Oracle automatically retrieves another group to the cache. However, the cache may be skipped. When the database suddenly goes down (shutdown abort), the sequence in the cache will be lost.
Therefore, we recommend that you use the nocache option when creating sequence.

2. Use sequence:

Sequence. currval  -- Returns the current sequence value.
Sequence. nextval  -- Increase the sequence value and return the sequence value.

[Note]
The first nextval returns the initial value;
The subsequent nextval will automatically increase the value of your defined increment by and then return the added value.

Currval always returns the value of the current sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur.
Nextval increases the sequence value once. Therefore, if you use multiple nextval values in the same statement, their values are different.

Sequence is stored in the data dictionary and in the user_sequences table
Last_number is the final serial number, that is, the current position of the sequence cursor.

// Get sequence last_number

Select last_number from user_sequences where sequence_name = test_seqname

// Nextval points the cursor to the next position (add or subtract one)

Select seqname. nextval from user_sequences to get the value of the next cursor.

3. Modify Sequence

You must have the alter any sequence permission to modify the sequence. You can alter all sequence parameters except start.
If you want to change the start value, you must drop sequence and re-create.

The command format is as follows:

AlterSequence test_sequence
IncrementBy 10
Maxvalue10000
Cycle   --Start from scratch after 10000
Nocache;

4.Delete Sequence
DropSequence order_seq;