Method for creating auto-increment fields in Oracle-Sequence Method

In Oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. Create Sequence
First, you must have the create sequence or create any sequence permission,
Create sequence emp_sequence
Increment by 1-- Add a few
Start with 1-- Count from 1
Nomaxvalue-- Do not set the maximum value (maxvalue 99999999)
Nocycle-- Always accumulate, not loop
Cache 10;-- Set cache SequenceIf the system is down or in other cases, the sequence will be discontinuous,

You can also set it to --------- nocache.
The sequence created for s_test is as follows:

CreateSequence s_test
Minvalue1
Maxvalue99999999
StartWith1
IncrementBy 1
Nocache;

Once s_test is defined, you can use currval, nextval
Currval = returns the current Sequence Value
Nextval = increase the sequence value, and then return the Sequence Value
For example:
S_test.currval
S_test.nextval

Where sequence can be used:
-Select statements that do not contain subqueries, snapshot, and view
-The insert statement is in the subquery.
-In the value of the nsert statement
-Update in Set

See the following example:

Insert into s_test (ID, name, order) values (s_test.nextval, 'wang', 100 );

2. Alter Sequence)
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can modify all sequence parameters except start. if you want to change the start value, you must delete the sequence (drop sequence) and recreate it (re-create ).
Alter sequence example
Alter sequence emp_sequence
Increment by 10
Max value 10000
Cycle-- Start from scratch after 10000
Nocache;

Initialization parameters that affect sequence:
Sequence_cache_entries = sets the number of sequence that can be simultaneously cached.

3. Delete Sequence
Simple drop sequence
Drop sequence s_test;

 

The following is a simple example:
Create SequenceSeq_id
Minvalue 1
Maxvalue 99999999
Start With 1
Increment By 1
Nocache
Order;

Create a producerCodeIs:

Create Or Replace TriggerTri_test_id-- Trigger name
Before Insert OnTest-- Test is the table name.
For Each Row
Declare
NextidNumber;
Begin
If: New. IDIs Null or: New. ID = 0 Then -- ID is the column name.
SelectSeq_id.Nextval -- S_test is the sequence just created.
IntoNextid
FromSYS. Dual;
: New. ID: = nextid;
End If;
EndTri_test_id;
The above code can realize the auto-Increment Function of ID.

 

Summarize and learn others' knowledge

If you have any other good methods, please submit them for your mutual learning and exchange.