Oracle sequence details (turn) __oracle

Preface:

People who have done web development basically know that the primary key value in a database table sometimes we use the numeric type
and the self increment. This makes it easy to use the tools to create tables in MySQL, SQL Server
. But there is no way to do this in Oracle, and in general we will use sequences and triggers
to achieve the function of the primary key to self increase. The following article mainly introduces the sequence.    

Knowledge Point One: what is a sequence.

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

Knowledge Point Two: What is the function of the sequence.

1.  automatically provide a unique value of
2.  Shared Object
3.  Primarily used to provide a primary key value of
4.  Loading sequence values into memory can improve access efficiency

Knowledge Point Three: How to create a sequence.

1,  want to have the permission to create a sequence creates sequence or create any sequence

2,  creates a sequence syntax create
        sequence sequence  //create a 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, increment by default is MinValue decrement is
      //MaxValue

       [{MAXVALUE n | Nomaxvalue}]//Max
       [{minvalue n | Nominvalue}]//min value
       [{CYCLE | Nocycle}]//loops/Not loops
       [{CACHE n | nocache}];//allocation into memory




  Nextval returns the next valid value in the sequence, and any user can reference the
  current value of the stored sequence in Currval
  nextval should be specified before Currval, Both should be effectively

Create sequence seqemp increment by 1-start with 1 MaxValue 3 MinValue 1
Cycle cache 2;
Currval
Select seqemp.nextval from dual before nextval  ; 
Select Seqemp.currval from  dual;
Cache<max-min/increment



//explanation
{
Create creates
Sequence sequence SEQEMOP sequence name increment by
step
Stat with 1 start value
Maxvalue  maximum
minvalue  minimum

Cycle loop nocycle non-cyclic
cache cache   cache< Maxvalue-minvalue/increment by//generally do not use the
save
Nextvalue next
currval current value

}

Knowledge Point IV: Examples

Instance application
//implementation ID automatic 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;

Knowledge Point Five: When cracks occur (discontinuous).

The sequence is cracked in the following cases:
    1, rollback
    2,  system exception
    3,  multiple tables using the same sequence at the same time

Knowledge Point Six: How to modify the sequence?

Modify the increment, maximum, minimum, loop option of the sequence, or whether to load memory
alter SEQUENCE SEQUENCE  //create sequence name
       [INCREMENT by n]  //increment sequence value is n If n is a positive number increment, if it is a negative number the default is 1
       [start    with N]//Start value, incrementing by default is MinValue decrement is MAXVALUE ' here write code slice '
       [{MAXVALUE n | Nomaxvalue}]//Max
       [{minvalue n | Nominvalue}]//min value
       [{CYCLE | Nocycle}]//loops/Not loops
       [{CACHE n | nocache}];//allocations coexist into memory

for example:
    Alter sequence seqemp maxvalue 5;
    Select Seqemp.nextval from dual;

Change Note:

Considerations for Modifying a sequence:
1.   Must be the owner of the sequence or have ALTER permission 2 on the sequence
.   Only future sequence values will be changed by
3.   Changing the initial value of a sequence can only be achieved by deleting the sequence

Knowledge Point seven: How to delete a sequence.

1.   Delete the sequence 2 using the Drop SEQUENCE statement
.   After deletion, the sequence cannot be referenced again