Implementation of auto-increment oracle ID Columns

The following describes how to implement the auto-increment oracle ID column: sequence and trigger for your reference. We hope this will help you learn about the oracle ID column.

We use instances to describe the usage of the auto-increment oracle ID column.

Assume that there is such a table:

Create table S_Depart (

DepartId INT not null,

Parameter Name NVARCHAR2 (40) not null,

DepartOrder INT default 0,

Constraint PK_S_DEPART primary key (DepartId)

);

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; -- set the cache sequence. If the system goes down or otherwise, the sequence will be discontinuous. You can also set it to --------- NOCACHE.

The sequence created for S_Depart is as follows:

Create sequence S_S_DEPART

Minvalue 1

Max value 999999999999999999999999999

Start with 1

Increment by 1

Nocache;

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:

-SELECT statements that do not contain subqueries, snapshot, and VIEW

-The INSERT statement is in the subquery.

-In the value of the NSERT statement

-UPDATE in SET

See the following example:

Insert into S_Depart (departId, partition name, Departorder) values (s_s_0000.nextval, '20140901', 1 );

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 by and then returns 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, 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 shut down and shut down abort), the sequence in the cache will be lost. Therefore, you can use nocache when creating 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

Max value 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.

It's easy to Drop Sequence.

Drop sequence order_seq;

A simple example:

Create sequence SEQ_ID

Minvalue 1

Max value 99999999

Start with 1

Increment by 1

Nocache

Order;

The code of the producer is:

Create or replace trigger tri_test_id

Before insert on S_Depart -- S_Depart is the table name.

For each row

Declare

Nextid number;

Begin

IF: new. DepartId IS NULLor: new. DepartId = 0 THEN -- DepartId IS the column name.

Select SEQ_ID.nextval -- SEQ_ID is just created

Into nextid

From sys. dual;

: New. DepartId: = nextid;

End if;

End tri_test_id;

OK. The above code can implement the auto-increment oracle ID column function.

To a specific instance:

Create sequence: create sequence TB_DRAW_SEQ increment by 1 start with 10 NOMAXvalue nocycle cache 10;

How to Apply: insert into tb_draw (order_id) values (TB_DRAW_SEQ.nextval );

Of course, the specific trigger definition methods can be different, but the big framework looks like this.
 

Oracle command line custom editor vi

Implementation of oracle command line Logon

Oracle queries table space usage

How to view the Oracle tablespace size

Syntax for creating views in Oracle