How to use sequence in Oracle

In an Oracle database, sequence is equivalent to a serial number, and the sequence is automatically incremented each time it is taken, and is typically used in places that need to be sorted by serial number.

1. Create Sequence

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

CREATE SEQUENCE emp_sequence

INCREMENT by every time you add a few

Start with the first count starting from 1

nomaxvalue--do not set the maximum value

nocycle--always accumulates, does not circulate

CACHE 10;

As long as you define the emp_sequence, you can use the Currval,nextval

Currval= returns the current value of the sequence

Nextval= increases the value of sequence and then returns the sequence value

For example:

Emp_sequence. Currval

Emp_sequence. Nextval

Places where you can use sequence:

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

。 In the subquery of the INSERT statement

。 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, N, NULL, 20);

SELECT Empseq.currval from DUAL;

It is important to note that:

The first nextval returns the initial value, and the subsequent nextval automatically increases the increment by value that you define, and then returns the incremented value. Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs. A nextval will increment the value of sequence once, so if you use multiple nextval in the same statement, the value will be different.

-If the cache value is specified, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdown abort), the cache sequence will be lost. So you can use NoCache to prevent this when the 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 the sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

Alter Sequence Example:

ALTER SEQUENCE emp_sequence INCREMENT by ten MAXVALUE 10000 CYCLE-from 10000 onwards NOCACHE;

Can affect the initialization parameters of sequence:

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

Simple Drop Sequence

DROP SEQUENCE Order_seq;

How to use sequence in Oracle