ORACLE SEQUENCE Introduction

In Oracle, sequence is the so-called serial number, each time it takes its own initiative to add it, 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= Add the value of the? Sequence and return 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 return is the initial value, and then the Nextval will add it himself? You define the increment by value, and then return the value after the add?. Currval always returns the value of the current sequence, but if Currval is used after the first nextval initialization, an error occurs. Once a nextval is added, the value of a sequence is assumed, so if 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;

ORACLE SEQUENCE Introduction