Let's assume that there is a table:
Copy Code code as follows:
CREATE TABLE S_depart (
Departid INT NOT NULL,
Departname NVARCHAR2 () NOT NULL,
Departorder INT Default 0,
Constraint Pk_s_depart primary KEY (Departid)
);
In Oracle sequence is called the serial number, each time it will automatically increase, generally used in the need to order by serial number of places.
1, Create Sequence
You must first have create sequence or create any sequence permissions,
CREATE SEQUENCE emp_sequence
INCREMENT by 1--add a few at a time
Start with 1-counting from 1
Nomaxvalue--Do not set maximum value
Nocycle--Cumulative, not cyclic
CACHE 10; --Sets the cached cache sequence, which can also be set to---------if the system is down or other conditions will cause the sequence to be discontinuous nocache
The sequence created for S_depart are as follows:
Copy Code code as follows:
Create sequence S_s_depart
MinValue 1
MaxValue 999999999999999999999999999
Start with 1
Increment by 1
NoCache;
Once the emp_sequence is defined, you can use the Currval,nextval
Currval= returns the current value of sequence
nextval= increase the value of sequence, and then return sequence value
Like what:
Emp_sequence. Currval
Emp_sequence. Nextval
Where you can use sequence:
-SELECT statements that do not contain subqueries, snapshot, view
-Subquery in INSERT statement
-The values of the Nsert statement
-In the set of the UPDATE
You can see the following example:
Copy Code code as follows:
Insert into S_depart (Departid,departname,departorder) VALUES (s_s_depart.nextval, ' 12345 ', 1);
SELECT empseq.currval from DUAL;
But be aware of:
-The first time the Nextval returns the initial value, then the nextval automatically increases the increment by value you define and then returns the added value. Currval always returns the value of the current sequence, but cannot use currval after the first nextval initialization, otherwise there will be an error. Once nextval will add a sequence value, so if you use multiple nextval within the same statement, the value is different. Got it?
-If the cache value is specified, Oracle can place some sequence in memory in advance so that the access is faster. Cache inside of the finished, Oracle automatically take a group to cache. Use cache may jump number, such as the database suddenly abnormal down (shutdown abort), the cache in the sequence will be lost. So you can use NoCache to prevent this when you create sequence.
2. Alter Sequence
You are either owner of the SEQUENCE or have alter any SEQUENCE permission to change the SEQUENCE. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.
An example of Alter sequence
ALTER SEQUENCE emp_sequence
INCREMENT by 10
MAXvalue 10000
CYCLE--Start from scratch after 10000.
NoCache;
The initialization parameters that affect sequence:
Sequence_cache_entries = Sets the number of SEQUENCE that can be CACHE simultaneously.
Can be very simple drop Sequence
DROP SEQUENCE Order_seq;
a simple example：
Copy Code code as follows:
Create sequence seq_id
MinValue 1
MaxValue 99999999
Start with 1
Increment by 1
NoCache
Order
the builder code is：
Copy Code code as follows:
Create or Replace Trigger tri_test_id
Before insert on S_depart--s_depart is table name
For each row
Declare
NextID number;
Begin
If:new. Departid is nullor:new. Departid=0 THEN--departid is a column name
Select Seq_id.nextval--seq_id just created
Into NextID
From Sys.dual;
: New. Departid:=nextid;
End If;
End tri_test_id;
OK, the above code can realize the function of automatic increment.
