Sequence (study notes)

the role of the sequence

a number of databases will provide users with an auto-grow column Operationsequence (Sequence) can automatically implement the data number operation according to the rule of the fixed rules complete creation syntax for sequences

 create   SEQUENCE sequence name  [  INCREMENT by step  ]  [  start with start value  ]  [  MAXVALUE Max | Nomaxvalue    [  MINVALUE min value | Nominvalue    [  CYCLE | Nocycle    [  Cache buffer Size | NOCACHE  ;  

syntax for creating a default sequenceCREATE SEQUENCE sequence name Example One, creating a myseq default sequence

CREATE SEQUENCE myseq

-- querying the User_sequence data dictionary SELECT *  from user_sequences;

The main attributes are as follows: Sequence_name: Represents the name of the sequence, where the name is "Myseq" created previously, Min_value: The default minimum value for the start of this sequence (default is 0); max_ Value: The default maximum value for this sequence growth (default is 999999999999999999999999999), increment_by: The step size of each increment of the sequence (default is 1), Cycle_flag: loop marker, or "Y" if it is a loop sequence. The non-cyclic sequence is displayed as "n" (the default is "n"); Cache_size: The amount of cache for sequence operations (by default); Last_number: The value of the last operation;
example Two, manipulating by Currvar and Nextal properties

-- connection performed 2 times SELECT  from dual; -- the current sequence SELECT  from dual;

The sequence name. Currval: Indicates that the result of the current sequence has grown, that the sequence content will not change after repeated calls, and that the size of the current sequence (Last_number) will not change;
The sequence name. Nextval: Indicates that the next increment of a sequence is obtained, and the sequence automatically grows each time it is called.

example three, using a sequence when inserting data

--Create a member tableCREATE TABLEMEMBER (Mid Number, NAMEVARCHAR2( -) not NULL,       CONSTRAINTPd_midPRIMARY KEY(mid)); --query Table SELECT *  fromMEMBER;--Inserting Data INSERT  intoMEMBER (Mid,name)VALUES(Myseq.nextval,'Zhang Sanfeng'); INSERT  intoMEMBER (Mid,name)VALUES(Myseq.nextval,'Zhang Trixan'); INSERT  intoMEMBER (Mid,name)VALUES(Myseq.nextval,'Zhang Mowgli'); --query Table SELECT *  fromMEMBER;--find mid will automatically increase

Delete a sequence

DROP SEQUENCE sequence Name

-- Delete myseq sequence DROP SEQUENCE Myseq;

Create a sequence of special features

• Set the growth step of a sequence INCREMENT by

Grammar:

CREATE SEQUENCE sequence name INCREMENT by step

example Four, creating a sequence step of 3

CREATESEQUENCE Myseq INCREMENT by 3;--Query User_sequencesSELECT *  fromuser_sequences;--the step size is already 3--Call Myseq sequence--Connection ExecutionSELECTMyseq.nextval fromdual;--View the current sequenceSELECTMyseq.currval fromDual

• Sets the initial value of the sequence start with

Grammar:

CREATE SEQUENCE sequence name START with initial value

example Five, the initial value of the creation sequence is 30, each step is 2

DROPSEQUENCE myseq;CREATESEQUENCE Myseq START with  -INCREMENT by 2; --Query User_sequencesSELECT *  fromuser_sequences;--The initial value is 30 and the step size is 2--Call Myseq sequence--Connection ExecutionSELECTMyseq.nextval fromdual;--View the current sequenceSELECTMyseq.currval fromDual

• To set the cache for a sequence | NOCACHE

Grammar:
CREATE SEQUENCE sequence name cache cache Size | NOCACHE

example Six, creating a sequence with cache set to 100 and not using cache

DROPSEQUENCE myseq;--creating sequences using cachingCREATESEQUENCE Myseq CACHE -;--Query User_sequencesSELECT *  fromuser_sequences;--Create a sequence without using a cacheDROPSEQUENCE myseq;CREATESEQUENCE myseq NOCACHE;--TestSELECTMyseq.nextval fromDual

• Set up a loop sequence

Grammar:

CREATE SEQUENCE sequence name[MAXVALUE series maximum | Nomaxvalue][MINVALUE sequence minimum | Nominvalue][CYCLE | Nocycle]; example Seven,Create a loop sequence that loops the contents of a sequence between 1, 3, 5, 7, 9

DROPSEQUENCE myseq;--Create a sequenceCREATESEQUENCE Myseqstart with 1INCREMENT by 2MAXVALUETenMINVALUE1Cyclecache3;--multiple executionsSELECTMyseq.nextval fromDual

To Modify a sequence:The sequence itself belongs to the object of a database, and as long as it is a database object, it can be modified after it is created, and the modified syntax of the sequence is as follows

ALTER SEQUENCE sequence name         [  INCREMENT by step ]        []        []        []        < c19>[] ;

Modify and set the same way, just change create to alter

Oracle 12C provides an automated sequence that creates a Automatic sequence Syntax

CREATE TABLEtable name (column name type GENERATED by DEFAULT  as IDENTITY([INCREMENT by Step]                     [Start with start value]                       [MAXVALUE Maximum Value | Nomaxvalue]                        [MINVALUE Minimum Value | Nominvalue]                       [CYCLE | Nocycle]                       [Cache Buffer Size | NOCACHE]), column name type,...) ;

example Eight, creating a data table with an auto-grow column

CREATE TABLEMytab (Mid NumberGENERATED by DEFAULT  as IDENTITY(START with 1INCREMENT by 1), NAMEVARCHAR2( -) not NULL; CONSTRAINTPd_mymidPRIMARY KEY(mid));

Summary:

1. The function of automatic growth column can be realized by using sequence.
2. Two pseudo-columns of a sequence: Nextval, currval;
3. Automatic sequencing is provided after Oracle 12C.

 

Sequence (study notes)