In oracle, what is a sequence ?, Oracle sequence?

In oracle, what is a sequence ?, Oracle sequence?

Sequence: a database object provided by oacle to generate a series of unique numbers.

L automatically provides unique values

L shared object

L mainly used to provide the primary key value

L loading sequence values into memory improves access efficiency

Creation sequence:

1. create sequence or create any sequence

2. Sequence creation syntax

Create sequence sequence // CREATE a SEQUENCE name

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

[Start with n] // The START value. The default value of increment is minvalue and the value of increment is maxvalue.

[{MAXVALUE n | NOMAXVALUE}] // maximum value

[{MINVALUE n | NOMINVALUE}] // minimum value

[{CYCLE | NOCYCLE}] // cyclic/non-cyclic

[{CACHE n | NOCACHE}]; // The allocation is included in the memory.

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

Store the current value of the sequence in CURRVAL

NEXTVAL should be specified before CURRVAL, and both should be valid at the same time

Create sequence seqEmp increment by 1 start with 1 maxvalue 3 minvalue 1

Cycle cache 2;

// Nextval first and currval

Select seqEmp. nextval from dual;

Select seqEmp. currval from dual;

Cache <max-min/increment

// Explanation

{

Create

Sequence seqEmop Sequence name

Increment by step size

Stat with 1 start value

Maxvalue maximum

Minvalue minimum value

Cycle nocycle no Cycle

Cache <maxvalue-minvalue/increment by // Cache is not generally used

Next to Nextvalue

Current Currval Value

}

// Instance application

// Auto increment of IDS

// Step 1

Create table cdpt (

Id number (6 ),

Name varchar2 (30 ),

Constraint pk_id primary key (id)

);

Create sequence seq_cdpt

Increment by 1

Start with 1

Max value 999999

Minvalue 1

Nocycle

Nocache

Insert into cdpt values (seq_cdpt.nextval, 'feffefe ');

Commit;

Select * from cdpt;

/Use sequence

Cracks may occur.

L The sequence has cracks in the following cases:

• Rollback

• System exception

> Multiple tables use the same sequence at the same time

// Modify the sequence increment, maximum value, minimum value, loop option, or whether to load the memory

Alter SEQUENCE sequence // create a SEQUENCE name

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

[Start with n] // The START value. The default value of increment is minvalue and the value of increment is maxvalue.

[{MAXVALUE n | NOMAXVALUE}] // maximum value

[{MINVALUE n | NOMINVALUE}] // minimum value

[{CYCLE | NOCYCLE}] // cyclic/non-cyclic

[{CACHE n | NOCACHE}]; // The allocation is included in the memory.

Notes for sequence modification:

L must be the sequence owner or have the ALTER permission on the sequence

L only future sequence values will be changed

L changing the initial values of a sequence can only be achieved by deleting the sequence and recreating the sequence.

Delete Sequence

L use the drop sequence statement to delete a SEQUENCE

L after deletion, the sequence cannot be referenced again

Alter sequence seqEmp maxvalue 5;

Select seqEmp. nextval from dual;