Shulou(Shulou.com)06/01 Report--

In some business tables, the ID field is not needed as a unique identity, but in order to facilitate and need to add the ID column as the primary key or foreign key in the data table later! So here are two ways:

Solution one:

Sequence + UPDATE. If there is data in the original table, you want to add a column of ID to automatically add value.

1. Create a sequence

Format:

Create sequence test_seq

Start with 1 Murray-start bit 1

Increment by 1Mel-basic demand, how much does it increase each time

Minvalue 1 Murray-minimum

Maxvalue 999muri-maximum according to demand

Nocache-- has no cache

For details, please see the introduction of my blog sequence.

2. Insert

Format:

Update table set ID=test_seq.nextval;-- will insert as many rows as there are in the original data table

Note that this is a change! That is to say, if there is any ID in your table, it will be overwritten.

This method is suitable for new ID columns without data, does not involve other column action instructions, ensures data integrity, and most sequences are used for automatic growth.

Solution 2:

Another scenario is that I want to do something high-end (special scenario). When I insert or change a column, I implement the data at the pace of sequential growth.

Sequence + trigger + cursor

1. Create a sequence

Format:

Create sequence test_seq

Start with 0mura-start bit

Increment by 3muri-every time I want to add 3

Minvalue 1 Murray-minimum

Maxvalue 999muri-maximum according to demand

Nocache-- has no cache

2. Create a trigger

Format:

Create or replace trigger trig_test

Before update on test_table

For each row

Declare ing_age number

Begin

If: new.ID = 0 or: new.ID is null then

Select test_seq.nextval into ing_age from sys.dual

: new.ID: = ing_age

End if

End trig_test

If there is a need for the entire cursor, it is no longer hidden here, because if you store problems such as sequences, this will complicate the database and increase the cost of later maintenance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.