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;