Sequence usage in Oracle Database

InOracleDatabase,SequenceEquivalentSerial numberThe sequence will automatically increase each time it is obtained, and it will usually act on the location where the sequence number needs to be sorted.

　　1. Create Sequence

(Note: you need the create sequence or create any sequence permission)

Create sequence emp_sequence

Increment by 1 -- add several

Start with 1 -- count from 1

Nomaxvalue -- do not set the maximum value

Nocycle -- always accumulate without repeating

Cache 10;

With emp_sequence defined, you can use currval, nextval

Currval = returns the current Sequence Value

Nextval = increase the sequence value, and then return the Sequence Value

For example:

Emp_sequence.currval

Emp_sequence.nextval

Where sequence can be used:

. Select statements that do not contain subqueries, snapshot, and view

. In the subquery of the insert statement

. In the values of the nsert statement

. Updating set

See the following example:

Insert into EMP values (Em1_q. nextval, 'Lewis ', 'cler', 7902, sysdate, 1200, null, 20 ); Select empseq. currval from dual;

　　Note that:

The first value returned by nextval is the initial value. The subsequent nextval will automatically increase the value of your defined increment by and then return the added value. Currval always returns the value of the current sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur. Nextval increases the sequence value once. Therefore, if you use multiple nextval values in the same statement, the values are different.

If the cache value is specified, Oracle can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, Oracle will automatically fetch another set of data to the cache. Using the cache may jump. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used in create sequence to prevent this situation.

　2. Alter Sequence

You must have the owner of the sequence, or have the alter any sequence permission to modify the sequence. You can alter all sequence parameters except start. To change the start value, you can:

(1) drop sequence and re-create.

(2) For example, if we want to change the start of testseq to 1000, we can perform the following operations:

Alter sequence testseq increment 10000;

Select testseq. nxetval from dual;

Alter sequence testseq increment 1;

(3) loop...

　　Alter sequence example:

Alter sequence emp_sequence Increment by 10 Max value 10000 Cycle -- start from scratch after 10000 Nocache;

　It can affect the initialization parameters of sequence:

Sequence_cache_entries = sets the number of sequence that can be simultaneously cached.

Simple drop sequence

Drop sequence order_seq;