In Oracle, sequence is the so-called serial number, each time it takes its own initiative to add, usually in order to be ordered by serial number place.
1. Create Sequence
You first have to have create sequence or create any sequence permissions,
CREATE SEQUENCE emp_sequence
INCREMENT by 1--add a few each time
Start with 1-Start counting from 1
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
CACHE 10;
Once you have defined emp_sequence, you can use Currval,nextval
Currval= returns the current value of the sequence
Nextval= adds a sequence value, and then returns the sequence value
Example:
Emp_sequence. Currval
Emp_sequence. Nextval
Where to use the sequence:
-Do not include sub-query, snapshot, view's SELECT statement
-INSERT statement in subqueries
-In the values of the Nsert statement
-In the set of UPDATE
Can look for example the following examples:
INSERT into EMP VALUES
(Empseq.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, N, NULL, 20);
SELECT Empseq.currval from DUAL;
However, it is important to note that:
-The first nextval returns the initial value, and the subsequent nextval will voluntarily add the increment by value that you defined, and then return the added value. Currval always returns the value of the current sequence, but if Currval is used after the first nextval initialization, an error occurs. Once Nextval will add the value of sequence once, so suppose you use multiple nextval in the same statement, the value is different. Clear?
-Assuming that the cache value is specified, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache was taken, Oracle took the initiative to take a group to the cache. Using the cache may jump, for example the database suddenly shutdown down (abort), the cache sequence will be lost. So it is possible to use NoCache to prevent such a situation when create sequence.
2. Alter Sequence
You are either the owner of the SEQUENCE, or have alter any SEQUENCE permission to modify SEQUENCE. Ability to alter all sequence parameters except start to. False assumption to change the start value, you must drop sequence and then re-create.
Examples of Alter sequence
ALTER SEQUENCE emp_sequence
INCREMENT by 10
MAXVALUE 10000
CYCLE-Start from the beginning in 10000
NOCACHE;
Initialization parameters that affect sequence:
Sequence_cache_entries = Sets the number of SEQUENCE that can be CACHE at the same time.
Can be very easy to drop Sequence
drop sequence order_seq;