Oracle sequence usage

In Oracle, sequence is the sequence number, which is automatically increased each time it is obtained. Sequence has no relationship with the table.

1. Create Sequence

First, you must have the create sequence or create any sequence permission.

The creation statement is as follows:

 

Create Sequence seqtest
Increment By   1   -- Add a few
Start With   1   -- Count from 1
Nomaxvalue -- Do not set the maximum value
Nocycle -- Always accumulate, not loop
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.

 

2. Obtain the sequence value.

After the sequence is defined, you can use currval and nextval to get the value.
Currval: returns the current sequence value.
Nextval: Increase the sequence value, and then return the added sequence value.

The statement for obtaining the value is as follows: Select Sequence name. currval From Dual;

As shown in the preceding statement, the sequence value is:

Select Seqtest. currval From Dual

 

Sequence can be used in SQL statements:
-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

For example, in the insert statement

 

Insert   Into Table Name (ID, name) Values ( Seqtest. nextval, ' Sequence insert Test ' );

 

 

Note:

-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.
-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.

 

 

 

 

3. Alter Sequence
You have the alter any sequence permission to modify the sequence. You can alter all sequence parameters except start. To change the start value, you must drop the sequence and then re-create.

 

Example:

Alter Sequence seqtest maxvalue 9999999 ; Set the sequence_cache_entries parameter to set the number of sequence that can be cached at the same time.

 

4. drop sequence Drop Sequence seqtest;

 

5,Example

 

Create Sequence seq_id
Minvalue 1
Maxvalue 99999999
Start With   1
Increment By   1
Nocache
Order ;

ProducerCodeIs:

Create   Or   Replace   Trigger Tri_test_id
Before Insert   On S_depart -- S_depart is the table name.
For Each row
Declare
Nextid Number ;
Begin
If : New. departid Is Nullor: New. departid = 0   Then   -- Departid is the column name.
Select Seq_id.nextval -- Seq_id is just created
Into Nextid
From SYS. Dual;
: New. departid: = Nextid;
End   If ;
End Tri_test_id;

 

OK. The above code can be used to implement the auto-increment function.

 

 

 

Note: New indicates the new value after the data is changed. The corresponding values are old values.

: = Indicates the value assignment.

:NextidReference variables defined in sqlplus

 

reference Article : http://blog.csdn.net/zhoufoxcn/article/details/1762351