Oracle Sequence detailed ____oracle

Sequence: A database object provided by Oacle to produce a series of unique digits.

L provide a unique numeric value automatically

L Shared objects

L primarily used to provide primary key values

L Loading sequence values into memory can improve access efficiency

To create a sequence:

1, to have the right to create a sequence sequence or create any sequence

2, create the syntax of the sequence

Create SEQUENCE SEQUENCE//creating sequence names

[INCREMENT by n]//incremented sequence value is N if n is a positive number increments, if is a negative number the default is 1

[start with n]//Start value, incrementing by default is MinValue decrement is MaxValue

[{MAXVALUE n | Nomaxvalue}]//MAX value

[{minvalue n | Nominvalue}]//min value

[{CYCLE | Nocycle}]//Cycle/No loops

[{CACHE n | nocache}];//Allocation in memory

 

Nextval Returns the next valid value in the sequence, which any user can reference

the current value of a sequence in Currval

Nextval should be specified before Currval, both should be valid

 

Create sequence seqemp increment by 1 start with 1 MaxValue 3 MinValue 1

Cycle cache 2;

////First nextval after Currval

Select seqemp.nextval from dual;

Select seqemp.currval from dual;

cache<max-min/increment

 

//Interpretation

{

Create Creation

Sequence sequence SEQEMOP sequence name

Increment by Step

Stat with 1 start value

Maxvalue max value

MinValue Minimum Value

 

Cycle cycle nocycle not cycle

Cache Cache Cache<maxvalue-minvalue/increment by//generally do not use caching

Nextvalue Next

Currval Current Value

 

}

//Instance application

//Implementation ID automatically increment

//First step

CREATE TABLE CDPT (

ID Number (6),

name VARCHAR2,

constraint pk_id primary key (ID)

);

 

Create sequence SEQ_CDPT

Increment by 1

Start with 1

Maxvalue 999999

MinValue 1

nocycle

NoCache

 

INSERT into CDPT values (seq_cdpt.nextval, ' Feffefe ');

commit;

select * from CDPT;

/Use sequence

will create cracks .

The L sequence cracks in the following cases:

• rolling back

• System Exceptions

> multiple tables using the same sequence at the same time

//Modify sequence increments, Max, Min, loop options, or whether to load memory

alter SEQUENCE SEQUENCE//Create sequence name

[INCREMENT by n]//incremented sequence value is N if n is a positive number increments, if is a negative number the default is 1

[start with n]//Start value, incrementing by default is MinValue decrement is MaxValue

[{MAXVALUE n | Nomaxvalue}]//MAX value

[{minvalue n | Nominvalue}]//min value

[{CYCLE | Nocycle}]//Cycle/No loops

[{CACHE n | nocache}];//Allocation in memory

Considerations for modifying a sequence:

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

L only the future sequence values will be changed

L Changing the initial value of a sequence can only be achieved by deleting the sequence

Delete a sequence

L Delete a sequence using the drop SEQUENCE statement

L after deletion, the sequence cannot be referenced again

Alter sequence seqemp maxvalue 5;

Select seqemp.nextval from dual;