Use of sequences in Oracle

Use of sequences in Oracle

Oracle provides a sequence object that is provided by the system for auto-incrementing serial numbers. It is usually used to generate auto-incrementing primary keys or serial numbers for database data records.

The following describes common operations such as sequence generation, modification, and deletion:

1. Create a sequence

Run the following command to create a sequence ):

Create sequence test_sequence
Increment by 1 -- data added each time
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cache 10;
 
[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 value of sequence.
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:

Alter sequence test_sequence
Increment by 10
Max value 10000
Cycle -- start from scratch after 10000
Nocache;

4. Delete Sequence
Drop sequence order_seq;

 

Referenced fromPrincipal's blog
Http://blog.yesky.com/blog/eric1945/archive/2007/06/29/1685318.aspx? Pending = true # post