How to Create a sequence in Oracle

Welcome to the Oracle community forum, and interact with 2 million technical staff about how to create a sequence in Oracle. sequence is the so-called serial number. It will automatically increase each time it is obtained, it is generally used to sort data by serial number. 1. CreateSequence you must first have CREATESEQUENCE or CREATEANYSEQUENCE

Welcome to the Oracle community forum and interact with 2 million technical staff> in Oracle, how to create a sequence is the so-called serial number in oracle. It will automatically increase each time it is retrieved, it is generally used to sort data by serial number. 1. Create Sequence you must first have create sequence or CREATE ANY SEQUENCE

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

How to Create a sequence in Oracle

In oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.

1. Create Sequence

First, you must have 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;

Once emp_sequence is 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:

-The SELECT statement that does not contain subqueries, snapshot statements, and views-in the INSERT statement subqueries

-In the value of the NSERT statement

-UPDATE in 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 NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment,

Then return the added value. CURRVAL always returns the value of the current SEQUENCE,

However, you can use CURRVAL only after the first NEXTVAL initialization. Otherwise, an error occurs.

NEXTVAL increases the SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different. Understand?

-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 automatically retrieves another group to the cache. The cache may be skipped,

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 are either the owner of the sequence, or you 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 from scratch after 10000

NOCACHE;

Initialization parameters that affect Sequence:

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

You can easily Drop Sequence drop sequence order_seq;