Auto increment of ID fields in the SQL _Oracle table

Auto increment of ID fields in the SQL _Oracle table

Objective: To automatically increment the ID field when inserting data into a table.

Start the experiment:

(1) Create an experiment table

Createtable test_ID_add

(

Id number (10 ),

USERNAME VARCHAR2 (32 ),

TEL VARCHAR2 (11 ),

CREATE_DATE datedefasyssysdate

);

 

(2) create a sequence

Use PL/SQL Developer to create a sequence

 

Supplement: For sequence creation, we can also use the command, as shown below: create sequence seq_testincrement by 1 start with 1nomaxvaluenominvaluenocache -- parameter description -- seq_test, indicates the name of the sequence to be created -- increment by 1 indicates that 1 is added each time -- start with 1 indicates that the value starts at 1 -- nomaxvalue indicates that no maximum value -- nominvalue indicates that no minimum value -- nocache indicates that the serial number does not continue after the maximum value is reached. extension supplement ended

(3) create a trigger

-- Forward each timeBefore a new data entry is inserted to the test_id_add table, an ID value is inserted.

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', '123 ');

-- Insert the first data entry

SQL> commit;

SQL> SELECT * from test_id_add;

SQL> insert into test_id_add (Username, TEL) values ('lilei', '123 ');

-- Insert the second data entry

SQL> commit;

SQL> SELECT * from test_id_add;

, The ID field automatically increases after each plus one.