Application of sequence-sequence in Oracle

Oracle does not have a field type similar to the field type Automatically numbered in Acces. Therefore, it is quite troublesome to use a field to automatically add value in oracle. Here we provide you with a method to use the sequence, the following describes how to create, use, and delete a sequence.

1. create sequence ------------ create sequence

Create sequence Seq_AutoID // The sequence name Seq_AutoID. You must remember this sequence name before calling it.

Minvalue 1 // the minimum value can be customized from 1.

Maxvalue 9999999999999999999 // The maximum sequence value is 999999999999999999999.

Start with 1 // can be customized from 1

Increment by 1 // The increment range is 1 and can be customized

Cache 20; // cache 20

2. Call the sequence to automatically add 1 ------------ Seq_AutoID.nextval

You can call this operation when you insert or update data to a table. Because you only use this method in your application

EG: insert into table1 (f1, f2, f3, f4) select f1, f2, f3, Seq_AutoID.nextval from table2 where Condition

Of course, the above sentence refers to batch data insertion. You can also use the values value to insert data one by one. Note that the table1.f4 field must be of the number type. In this way, the f4 field in your table 1 is a numerical value that increases progressively from 1, and the auto-increment function is realized. Of course, the range can be defined by yourself.

Another point is that the sequence is unique. As long as you keep increasing with nextval, the sequence number will not be repeated in oracle. you can use CurrVal to query the current sequence value. After nextval, currval will also change.

3. sequence deletion ------------ drop sequence

In order to reset the sequence or not, you can delete the sequence first.

EG: drop sequence Seq_AutoID;

4. Use a sequence to modify the value of a field

Eg: update cse_depot set jobid = Seq_AutoID.nextval

In the preceding example, the current sequence value is used to assign a value to the jobid field of cse_depot, enabling the jobid field to automatically add 1.

The above points are summarized by myself and according to my own use, and I hope you will join us in the discussion.