Implementation of Oracle Auto-increment Data Types

Implementation of Oracle Auto-increment Data Types

 

Author: Zhang guiquan

 

If you often use databases such as INFORMIX and MySQL, you will find that the database system itself provides an "auto-incrementing data class", such as the serial variable type of Informix. You only need to define the field as the serial type in create table to automatically increase the variable value, which is very convenient to use. However, if you are using an Oracle database, you need to implement this mechanism on your own. In fact, it is easy to implement. You only need to create a sequence variable and a trigger based on the target database. Nothing else is special. You may even find that the Oracle mechanism is more flexible than Informix, because you can control the starting point and incremental size of auto-increment variables. For example, you can define an auto-increment mechanism that increases from 1 to 5 at a time (of course, this design generally has no value for use ). The following uses a simple student record table as an example to describe how to implement the Oracle Auto-increment mechanism.

 

(1) create a basic table structure:

----------------------------------------

Create Table student

(

ID number,

Name varchar2 (40 ),

Age number,

Sex varchar2 (1 ),

Address varcharacter 2 (128 ),

Hponenumber varchar2 (16 ),

Primary Key (ID)

);

 

(2) create a sequence variable, which is based on 1 and increases by 1 each time.

Create sequence student_seq start with 1 increment by 1;

 

(3) create a trigger

Create or replace trigger student_trigger

Before insert on student

For each row

Begin

Select student_seq.nextval into: New. ID from dual;

End;

/

 

(4) now the auto-increment mechanism has been established. As long as you insert a record into the student table, the ID value will increase by 1, where the auto-increment base point is 1.