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;
Once the emp_sequence is defined, you can use 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:
INSERT into EMP VALUES
(Empseq.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, 1200, NULL, 20);
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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.