Oracle implements automatic number in table

This is a simple function, that is, using auto-increment columns and triggers.

 

1. First create a simple table users

-- Create Table userscreate table system. users (userid varchar2 (20) primary key, username varchar (20) not null, userpassword varchar2 (20) not null, userage number (3) default 0, usersex char (2) default 'male ')

Primary Key set primary key

Not null cannot be blank

Default setting default value

 

2. Create an auto-increment Column

--create sequences useridcreate sequence system.users_userid_sequencestart with 1increment by 1minvalue 1nomaxvaluenocache

Starting from 1

1 increase each time

The minimum value is 1.

No maximum value

Do not set Cache

 

3. Create a trigger

--create triggers useridcreate or replace trigger system.users_userid_biferbefore insert on system.usersfor each rowwhen(new.userid is null)beginselect system.users_userid_sequence.nextval into:new.userid from dual;end;

 

Create or replace means to create or replace. If no replace exists, it is created. If yes, It is replaced.

When

Before inserting data in each row

Check whether the userid in the current table (New indicates the current table, that is, users) is null.

Start execution

The next number is obtained from the query in the auto-increment column and assigned to the current userid.

Execution ended

 

As for the from dual, this is completely for the unification of syntax. Dual is meaningless.

 

4. Test

Insert into system. users (username, userpassword, userage, usersex) values ('zhang san', '000000', '20', 'mal'); insert into system. users (username, userpassword, userage, usersex) values ('Lee 4', '123456', '21', 'mal'); select * from system. users

 

Result