Sequence of Oracle

In an Oracle database, sequence is equivalent to the serial number. The sequence is automatically increased each time it is obtained. It usually acts 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. If you want to change the start value, you must drop sequence and re-create.

Alter sequence example:

Alter sequence emp_sequence increment by 10 maxvalue 10000 cycle -- start nocache from the beginning after 10000;

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;