A Brief Introduction to Oracle sequence (auto-increment field)

 

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;

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 value of the nsert statement
-Update in Set

See the following example:
Insert into EMP values
(Em1_q. nextval, 'Lewis ', 'cler', 7902, sysdate, 1200, null, 20 );

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

The current sequence value is always returned, but currval can be used only after the first nextval initialization; otherwise, an error will occur. One nextval increase

The value of sequence, so if you use multiple nextval In the same statement, the value is 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

Automatically fetch 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

It will be lost. Therefore, nocache can be used in create sequence to prevent this situation.

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 parameter. If you want to change the start value, you must drop the sequence before 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;

Okay, that's it.

-------------------------------------------------------------
Auto-growth and triggers:

In Oracle, how does one implement the function of auto-adding IDs?
Edit: China ASP

We often use an ID automatically assigned by the system as our primary key when designing a database, but this is not true in Oracle.

Feature, we can automatically add the ID by taking the following features
1. First create Sequence
Create sequence seqmax increment by 1
2. Usage
Select seqmax. nextval ID from dual
You get an ID.
If you put this statement in the trigger, you can automatically add the same ID function as ms SQL!

-------------------------------------------------------------------------

Released by macro:

By macro Zeng

 

### Create a table ###

Create Table "Sports". "lineup" ("ID" number not null, "type"
Number (3) not null, "body" varchar2 (100) not null, "hits" number (
10) default 0 not null, primary key ("ID "))
Tablespace "ts_sports"

### Sequence creation ###

Create sequence "Sports". "sports_lineup_id_seq" increment by 1
Start with 1 maxvalue 1.0e28 minvalue 1 nocycle
Cache 50 noorder

### Create an automatic update trigger ###

Create or replace trigger "Sports". "sports_lineup_id_trigger"
Before insert
On "Sports". "lineup"
For each row
Declare
Next_id number;
Begin
-- Get the next ID number from the sequence
Select sports_lineup_id_seq.nextval
Into next_id
From dual;

-- Use the sequence number as the primary key
-- For the record being inserted.
: New. ID: = next_id;
End;

### Create a trigger to protect the primary key ###

Create or replace trigger "Sports". "lineup_id_update_trigger"
Before update of "ID" on "Sports". "lineup"
For each row
Begin
Raise_application_error (-20000,
'Sports _ lineup_id_update_trigger: updates of the ID field'
| 'Are not allowed .');
End this article is from 51cto. com technical blog

Http://860510.blog.51cto.com/192661/143045