How to create an auto-increment field in Oracle-A Brief Introduction to Oracle Sequence

Address: http://blog.csdn.net/zhoufoxcn/article/details/1762351

Assume that there is such a table:

Create Table s_depart (
Departid int
Not
Null,
Invalid name nvarchar2 (40)
Not
Null,
Departorder int
Default
0,
Constraint pk_s_depart
Primary
Key (departid)
);

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 several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cache 10; -- set the cache sequence. If the system goes down or otherwise, the sequence will be discontinuous. You can also set it to --------- nocache.

The sequence created for s_depart is as follows:

Create sequence s_s_depart
Minvalue 1
Max value 999999999999999999999999999
Start with 1
Increment
1
Nocache;

Once emp_sequence 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:
Emp_sequence.currval
Emp_sequence.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 values of the insert statement
-Update in Set

See the following example:

Insert into s_depart (departid, partition name, departorder) values (s_s_0000.nextval, '20140901', 1 );

Select empseq. currval from dual;

Note that:
-The first nextval returns the initial value. The subsequent nextval automatically increases the value of your defined increment by and then returns 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. Understand?

-If the cache value is specified, Oracle can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, Oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.

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 alter all sequence parameters except start. if you want to change the start value, you must drop sequence and 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.

It's easy to drop sequence.
Drop sequence order_seq;

A simple example:
Create
SequenceSeq_id
Minvalue1
Maxvalue99999999
Start
1
Increment
By1
Nocache
Order;

The code of the producer is:

Create
Or replace
TriggerTri_test_id
Before
Insert onS_depart -- s_depart is the table name.
For each
Row
Declare
NextidNumber;
Begin
If: New. departidIs
Nullor: New. departid = 0
Then --Departid is the column name.
SelectSeq_id.Nextval --Seq_id is just created
IntoNextid
FromSYS. Dual;
: New. departid: = nextid;
End if;
EndTri_test_id;
OK. The above code can be used to implement the auto-increment function.