Create auto-increment Field sequence in Oracle

Oracle does not have a convenient auto-incrementing SEQUENCE in sqlserver. If you want to achieve that effect in sqlserver, it is not very troublesome. You need to create an auto-incrementing SEQUENCE,

Oracle does not have a convenient auto-incrementing SEQUENCE in sqlserver. If you want to achieve that effect in sqlserver, it is not very troublesome. You need to create an auto-incrementing SEQUENCE,

Oracle does not have a convenient auto-incrementing SEQUENCE in sqlserver. If you want to achieve that effect in sqlserver, it is not very troublesome. You need to create an auto-incrementing SEQUENCE and then create a trigger.

Create sequence emp_sequence

Increment by 1 -- Auto INCREMENT step, where step size is 1

Start with 1 -- count from 1

NOMAXvalue -- do not set the maximum value. If you want to set the maximum value, it is the value to be set after MAXvalue

NOCYCLE -- always accumulate without repeating

NOCACHE; -- do not set cache. If you want to set it, the format is the same as that of MAXvalue.

With emp_sequence defined, you can use CURRVAL and NEXTVAL. CURRVAL = returns the current sequence value, NEXTVAL = increases the sequence value, and then returns 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

Note:
The first value returned by NEXTVAL is the initial value. The subsequent NEXTVAL will automatically increase the step size you defined 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, so if you use multiple NEXTVAL values in the same statement, their 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 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 to prevent this situation when creating sequence.

Alter and drop are used to modify or delete a Sequence.