Common Oracle database objects-Sequence

Common Oracle database objects-Sequence

1. sequence: a database object provided by oacle for generating a series of unique numbers.

A) sequence features

I. The unique value is automatically provided.

Ii. shared object

Iii. Mainly used to provide the primary key value

Iv. Loading Sequence values into memory improves access efficiency

B) sequence definition

Create sequence Sequence

[Increment by N]

[Start with N]

[{Maxvalue n | nomaxvalue}]

[{Minvalue n | nominvalue}]

[{Cycle | nocycle}]

[{Cache n | cocache}];

Ii. Sequence Creation

A) You must have the create sequence or create any sequence permission to create a sequence.

B) sequence creation syntax

Create SequenceSequence// Create a sequence name

[IncrementN] // The incremental sequence value is n. If n is positive, it increases. If n is negative, it decreases by 1 by default.

[StartN] // Start value. The default value of increment is minvalue and the value of increment is maxvalue.

[{MaxvalueN|Nomaxvalue}] // Maximum value

[{MinvalueN|Nominvalue}] // Minimum value

[{Cycle |Nocycle}] // Loop/non-Loop

[{CacheN| Nocache}]; // It is allocated to the memory.

C) Notes

I. nextval returns the next valid value in the sequence, which can be referenced by any user.

Ii. Store the current value of the sequence in currval

Iii. nextval should be specified before currval, and both should be valid at the same time

Iii. Sequence usage

A) Loading Sequence values into memory improves access efficiency

B) The sequence has cracks in the following circumstances:

I. rollback

Ii. system exceptions

Iii. Multiple tables use the same sequence at the same time

C) if you do not load the sequence value into the memory (nocache), you can use the user_sequences table to view the current valid values of the sequence.

4. Modify the sequence

A) modify the sequence increment, maximum value, minimum value, loop option, or whether to load the memory.

Example: Alter sequence dept_deptid_seq

Increment by 20

Max value 999999

Nocache

Nocycle;

Sequence altered.

 

B) Considerations

I. It must be the sequence owner or have the alter permission on the sequence.

Ii. Only future sequence values will be changed

Iii. Changing the initial values of a sequence can only be achieved by deleting the sequence and recreating the sequence.

Iv. Use the drop sequence statement to delete a sequence

V. After deletion, the sequence cannot be referenced again