Oracle:sequence usage ____oracle

In Oracle databases, sequence is equivalent to serial number, sequence automatically increases each time it is taken, and is typically used to sort by sequence number.
1, Create Sequence

(Note: You need to have create sequence or create any sequence permissions)

CREATE SEQUENCE emp_sequence

INCREMENT by 1--each time add a few

Start with 1--counting starting from 1

nomaxvalue--does not set maximum value

nocycle--has been accumulated, does not cycle

CACHE 10;

Once you have defined the emp_sequence, you can use the Currval,nextval

Currval= returns the current value of sequence

nextval= increase the value of sequence, and then return sequence value

For example:

Emp_sequence. Currval

Emp_sequence. Nextval

Where you can use sequence:

。 SELECT statements that do not contain subqueries, snapshot, and view

。 In subqueries of INSERT statements

。 In the values of the Nsert statement

。 In the set of 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;

It is to be noted that:

The first nextval returns an initial value, and subsequent 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 will be different.

-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 need to have SEQUENCE owner, 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 re-create.

Alter Sequence Example:

ALTER SEQUENCE emp_sequence INCREMENT by MAXVALUE 10000 CYCLE-start from scratch after 10000 nocache;

Can affect initialization parameters for sequence:

Sequence_cache_entries = Sets the number of SEQUENCE that can be CACHE simultaneously.

3, Drop Sequence

DROP SEQUENCE Order_seq;