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 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 the cache. If you want to set the cache, the format is the sameMaxvalue

With emp_sequence defined, you can use currval and 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

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.

Finally, we will provide you with a small example:

 

 --  Create auto-incrementing Sequence  Create  Sequence seqidnomaxvaluestart  With   1  Increment  By   1  Nocache  Order  ;  -- Create a trigger  Create   Or   Replace   Trigger  Tri_test_id before  Insert   On Mytable --  Mytable is the table to insert the auto-incrementing Sequence    For  Each row  Declare  Nextid  Number  ; Begin    If : New. ID Is   Null   Or : New. ID =  0   Then     --  ID is the name of the column to insert the auto-incrementing sequence.      Select  Seqid. nextval  Into  Nextid  From  SYS. Dual;: New. ID: =  Nextid;  End   If  ;  End Tri_test_id;