ORACLE SEQUENCE Detailed

1. About sequences (about sequence)

A sequence is a database object. Multiple users can generate sequential numbers through a sequence to achieve automatic, unique growth of primary key fields, and a sequence can be used concurrently for multiple columns and multiple tables.

The sequence eliminates serialization and improves application consistency. Imagine a day without a sequence.   ) 2. Creating sequences (Create Sequence)

Prerequisite:Prerequisites

To create a sequence inyour own schema, you must have the Create sequence system privilege. Creating a sequence in your own mode requires the Create SEQUENCE permission

To create a sequence Inanother user's schema, you are must have the Create any sequence system privilege. Creating a sequence in other user mode requires the Create any sequence permission.

Syntax:Syntax

If you do not add conditional statements, the sequence format created by default is as follows:

--Create sequence

Create sequence seq_t

MinValue 1

MaxValue 999999999999999999999999999

Start with 1

Increment by 1

Cache 20;

Semantic semantics:

INCREMENT by : Specifies the sequence growth step. can be positive (ascending), negative integer (descending), but not 0. Maximum accuracy 28.

START with : Specifies the number of start sequences. The default is the sequence minimum value.

MAXVALUE : Specifies the maximum value for the sequence. Maximum 28 digits. must be greater than or equal to the starting value and is greater than or equal to the sequence minimum value.

Nomaxvalue: No maximum value (actual 10^27 or-1). Default

MinValue : Specifies the minimum value for the sequence.

Nominvalue : No minimum value (actual 1 or -10^26). Default

CYCLE : Specifies that the sequence reaches the maximum or minimum value and continues to build from the beginning.

nocycle : Do not cycle generation. Default.

CACHE : Specifies the number of pre-allocated sequence values in database memory for quick access. The minimum cache value is 2.

The cache parameter maximum value is:

(Ceil (Maxvalue-minvalue))/ABS (INCREMENT)

Note 1: If the system fails, all cached sequence values that are not used by the DML statements and committed will be lost. The number of potential missing values equals the number of cache.

NoCache : Do not specify cache count, default cache 20

Order: Specifies that the ordering condition guarantees that the sequence is generated in the sequence requested. This condition applies to the RAC environment.

Noorder : The sequence is not guaranteed to be generated in the order requested.

 

Example:

CREATE SEQUENCE Customers_seq

START with 1000

INCREMENT by 1

NoCache

Nocycle;

 

NOTE 2: With the cycle condition sequence when the maximum value is reached, the next value begins cycling from the minimum value minvalue.

CREATE SEQUENCE seq1

START with 200

INCREMENT by 10

MAXVALUE 200

CYCLE

NoCache;

SELECT Seq1.nextval from dual;

Results: 1 3. Alter SEQUENCE (Modify sequence)

 

Prerequisite:

The sequence must is in your own schema, or youmust have the ALTER object privilege on

The sequence, or you must have the ALTER any sequence systemprivilege.

Modifying your own pattern sequence requires alter object permission, and modifying other pattern sequences requires alter any sequence permissions.

Grammar:

 

semantics:

1 If you want to restart the sequence with a different number, you must delete the rebuild.

sql> alter sequence seq_t start with 2;

Alter sequence seq_t start with 2

*

ERROR at line 1:

Ora-02283:cannot Alter starting SequenceNumber

2 The modified MaxValue must be greater than the current value of the sequence.

sql> alter sequence seq_t MaxValue 1;

Alter sequence seq_t MaxValue 1

*

ERROR at line 1:

Ora-04004:minvalue must be less than MAXVALUE

Example:

MAXVALUE 1500;

 

CYCLE

4. Drop SEQUENCE (delete sequence)

Prerequisite:

Thesequence must be in your own schema or your must have the DROP any SEQUENCE system privilege.

Delete sequence must have drop any sequence permission

 

Syntax:

 

Example:

5. Restrictions on the use of Nextval and Currval

Currval and Nextval can be used in the following places:

· VALUES clause of INSERT statements

· The select list of a SELECT statement

· The SET clause of an UPDATE statement

Currval and Nextval cannot are used in these places: cannot be used in the following scenarios

· A subquery subquery

· A view query or materialized view queries or materialized views

· A SELECT statement with the DISTINCT operator contains DISTINCT keyword query

· A SELECT statement with a GROUP by or ORDER BY clause take an ORDER BY query statement

· A SELECT statement that are combined with another SELECT statement with the Union, INTERSECT, or minus set operator containing union, Interest,minus operator

· The WHERE clause of a SELECT statement is used in the Where condition

· Default value of a column in a CREATE table or ALTER table statement columns

· The condition of a check constraint check constraint

 

 

 

 

 

 

 

 

 --------------------------------------

Dylan presents.