Oracle -------- Sequence

1. Think about the problem

In a table, an id column (integer) exists. When a user wants to add a record, this column will automatically grow from 1. How can this problem be solved?

2. Introduction

Oracle uses sequence processing to automatically increase columns.

(1) values can be automatically generated for columns in the table.

(2) The user creates a database object and can share it with multiple users.

(3) It is generally used for primary keys or unique columns.

(4) sequence is also a Data Object

3. Define the sequence

2 format:

Create sequence name

[Start with n1]

[Increment by n2]

[Minvalue n3 | nominvalue]

[Maxvalue n4 | nomaxvalue]

[Cache n5 | Nocache]

[Cycle | nocycle]

[Order | noorder]

2 parameter description:

L Increment: This clause is optional, indicating the Increment of the sequence. A positive number generates an ascending sequence, and a negative number generates a descending sequence. The default value is 1.

L Minvalue: an optional clause that determines the minimum value generated by the sequence.

L Maxvalue: an optional clause that determines the maximum value generated by the sequence.

L Start: an optional clause that specifies the Start position of a sequence. By default, the Start value of an incremental sequence is Minvalue and the Start value of a descending sequence is Maxvalue.

L Cache: an optional clause. This option determines whether serial numbers are pre-allocated and stored in memory.

L Cycle: an optional clause. When the serial number reaches the maximum or minimum value, it can be reset and continue. If the limit is reached, the next data generated will be the minimum or maximum values.

L Nocycle: an optional clause. If a sequence reaches the maximum or minimum value, an error is returned if the next value is obtained in the view.

L Order: This option ensures that the generated sequence values are generated in sequence.

L Noorder: only the uniqueness of the sequence value is guaranteed, and the sequence of the generated sequence value is not guaranteed.

4. Delete Sequence

2 format:

Drop sequence name

5. Management Sequence

2 format:

Alter sequence name

[Increment by n2]

[Minvalue n3 | nominvalue]

[Maxvalue n4 | nomaxvalue]

[Cache n5 | Nocache]

[Cycle | nocycle]

[Order | noorder]

Note: Except for the starting value of a sequence, other sequence clauses and parameters that can be set can be modified. After the sequence is modified, all the sequence values in the cache will be lost. You can obtain the sequence information by querying the data dictionary USER_SEQUENCES.

6. Case studies

1) ascending sequence

Create sequence myseq

Start with 1 -- Start from 1

Increment by 1-increase by 1 each time

Minvalue 1-Minimum value 1

Maxvalue 30000-maximum to 30000

Cycle-after the sequence reaches 30000, start from 1 again

Cache 10-It indicates that 10 numbers are generated at a time for users to use. This can improve efficiency, but may generate a jump number.

2) descending sequence:

Create sequence myseq

Start with-4 -- Start from 1

Increment by-1-increase by 1 each time

Minvalue-5-Minimum value 1

Maxvalue-1-maximum to 30000; Use nomaxvalue if no maximum value is set

Cycle-sequence to 30000, and then start from 1 again; non-Cycle is nocycle

Nocache

7. Sequence usage

Create table test1 (id number primarykey, name varchar (32 ));

Insert into test1values (myseq. nextval, 'abc ');

Insert into test1values (myseq. nextval, 'ddd );

Note: "myseq" indicates the sequence name; "nextval" indicates that the sequence value is added and the value is returned.

Currval returns the current value of the sequence.

Select myseq. currval from dual;

Note:

(1) sequence usage:

? In the values of the Insert statement

? Insert into test (id, name) select myseq. nextval, enamefrom emp;

? Updating set

? Select statements that do not contain subqueries, snapshots, and views

(2) Currval always returns the value of the current sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur. A nextval (select, update, and insert) increases the sequence value. If multiple nextval values are used in a statement, the values are different.

(3) The first nextval returns the initial value. The subsequent nextval automatically adds the defined incrementby value and then returns the new value.

(4) If the cache value is specified, oracle can place some sequence in the memory in advance, so that the access is faster. After the Cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown/abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.

(5) Maxvalue: that is, the maximum value of the ascending sequence is10 to the power of 27; The maximum value of the descending sequence is-1. NoMaxValue is the default value.

(6) Minvalue: the minimum value of the ascending sequence is 1, and the minimum value of the descending sequence is the 26 power of the negative 10. NoMinValue is the default value.

(7) Cycle: Specify to continue generating integers even if the sequence has reached the maximum or minimum auto-increment value. When the ascending sequence reaches the maximum value, the next generated value is the minimum value. When the descending sequence reaches the minimum value, the next generated value is the maximum value. This rule is used for loop.