Oracle Self-Growth ID

In Oracle, you can create a separate sequence for the primary key of each table, and then get an automatically incremented identifier from the sequence and assign it to the primary key. For example, the statement creates a sequence named Customer_id_seq, which has a starting value of 1 and an increment of 2.

Create sequence Customer_id_seq increment by 2 start with 1

Once you have defined the CUSTOMER_ID_SEQ sequence, you can access the Curval and Nextval properties of the sequence.

• Curval: Returns the current value of the sequence
• Nextval: Increase the value of the sequence first, and then return the sequence value

The following SQL statement creates the Customers table first, then inserts two records, sets the value of the ID and Name field at Insert, where the value of the ID field comes from the customer_id_seq sequence. Finally, the ID field in the Customers table is queried.

CREATE TABLE Customers (ID int primary key NOT NULL, name varchar); INSERT into customers values (customer_id_seq.nextval , ' name1 '); INSERT into customers values (Customer_id_seq.nextval, ' name2 '); select ID from Customers;

If you execute the above statement in Oracle, the query results are:

Auto-Add ID field via trigger

As you can see from the INSERT statement above, it is cumbersome and cumbersome to insert the value of customer_id_seq.nextval every time, so consider using a trigger to complete this step.

Create a trigger Trg_customers

Create or Replacetrigger trg_customers before insert on customers for each row begin select Customer_id_seq.nextval into: New.id from dual; End

Insert a record

This is what we will find this record is inserted into the database, and the ID is still self-growing.

Oracle Self-Growth ID