Oracle Sequence Detailed

Sequence: is a database object provided by Oacle to produce a series of unique numbers.

L automatically provide a unique value

L Shared objects

L mainly used to provide primary key value

L Loading sequence values into memory improves access efficiency

To create a sequence:

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

2. Syntax for creating sequences

Create SEQUENCE SEQUENCE //Creating a sequence name

[INCREMENT by N]//increment of the sequence value is N if n is a positive number increment, if the negative is decremented by default is 1

[Start with n]//Start value, increment default is MinValue decrement is MaxValue

[{MAXVALUE n | Nomaxvalue}] Maximum Value

[{MINVALUE n | Nominvalue}] Minimum value

[{CYCLE | nocycle}] Cyclic/non-cyclic

[{CACHE n | nocache}];//allocation and in-memory

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

current value of the stored sequence in the Currval

Nextval should be specified before Currval, both should be valid at the same time

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

Explain

{

Create creates

Sequence sequence SEQEMOP sequence name

Increment by step

Stat with 1 start value

Maxvalue Maximum Value

Minvalue Minimum value

Cycle loop nocycle not cycle

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

Nextvalue Next

Currval Current Value

}

Instance Application

Automatic increment of the implementation ID

The first step

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

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:

• Roll Back

• System Exceptions

> Multiple tables using the same sequence at the same time

Modify the increment, max, min, loop option, or load memory of a sequence

Alter SEQUENCE SEQUENCE //Create sequence name

[INCREMENT by N]//increment of the sequence value is N if n is a positive number increment, if the negative is decremented by default is 1

[Start with n]//Start value, increment default is MinValue decrement is MaxValue

[{MAXVALUE n | Nomaxvalue}] Maximum Value

[{MINVALUE n | Nominvalue}] Minimum value

[{CYCLE | nocycle}] Cyclic/non-cyclic

[{CACHE n | nocache}];//allocation and in-memory

Considerations for modifying a sequence:

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

L only future sequence values will be changed.

L Changing the initial value of a sequence can only be achieved by removing the sequence after it is reconstructed

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; Oracle Sequence Detailed

Oracle Sequence Detailed