Auto-increment of fields in Sql_oracle table

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the following sources, otherwise, the legal responsibility to pursue copyright.

Deep Blue Blog: http://blog.csdn.net/huangyanlong/article/details/43937701

Goal: TheID field is automatically incremented when data is inserted into a table.

Start the experiment:

(1), create the experiment table

Create Table Test_id_add

(

ID Number (ten),

USERNAME VARCHAR2 (+),

TEL VARCHAR2 (one),

create_date Date default sysdate

);

(2), create sequence

Use PL/SQL Developer Create a sequence

Add: For sequence creation, we can also be implemented by command, as follows: Create sequence seq_testincrement by 1 start with 1nomaxvaluenominvaluenocache--parameter description--seq_ Test, which represents the name of the creation sequence--increment by 1 means each time an increase of 1--start with 1 means that starting at 1--nomaxvalue means that there is no maximum value--nominvalue indicates no minimum value--nocache indicates that the serial number reaches the upper limit and does not continue to expand supplemental end

(3), create trigger

-- each time to test_id_add table insert an ID value before inserting a new piece of data

Create or Replace Trigger Tr_idadd

before Insert on Test_id_add

for each Row

Begin

Select Seq_idadd.nextval into : New.id from dual;

End ;

(4), test

Sql>insert into Test_id_add (Username,tel) Values (' Huangyanlong ',' 12345678900 ');

-- Insert the first piece of data

Sql>commit ;

Sql>select * from Test_id_add;

SQL > Insert into Test_id_add (Username,tel) Values (' Lilei ',' 13713777777 ');

-- Insert a second piece of data

Sql>commit ;

Sql>select * from Test_id_add;

, the ID field grows automatically after each addition.

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the following sources, otherwise, the legal responsibility to pursue copyright.

Deep Blue Blog: http://blog.csdn.net/huangyanlong/article/details/43937701

Auto-increment of fields in Sql_oracle table