Sequential (learning notes), sequential learning notes

Sequential (learning notes), sequential learning notes
Functions of Sequences

 

In many databases, users are provided with a Sequence of auto-increment columns, which can be automatically numbered based on specified rules. Complete Sequence creation syntax

Create sequence name [increment by step] [start with start value] [MAXVALUE maximum value | NOMAXVALUE] [MINVALUE minimum value | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE size | NOCACHE];

Create default sequence syntaxCreate sequence name Example 1,Create a default sequence of myseq

CREATE SEQUENCE myseq

-- Query the user_sequence data dictionary SELECT * FROM user_sequences;

The main attributes are as follows: SEQUENCE_NAME: indicates the sequence name. The name here is the previously created "MYSEQ"; MIN_VALUE: the default minimum value for the sequence to start (the default value is 0); MAX_VALUE: the default maximum value for this sequence increase (default value: 999999999999999999999999999); INCREMENT_BY: The step size for each sequence increase (default value: 1); CYCLE_FLAG: indicates the loop mark. If it is a loop sequence, "Y" is displayed ", non-cyclic sequence is displayed as "N" (default value: "N"); CACHE_SIZE: cache volume of sequence operations (default value: 20); LAST_NUMBER: value of the last operation;
Example 2,Using currvar and nextal attributes

-- The connection is executed twice SELECT myseq. nextval FROM dual; -- the current sequence SELECT myseq. currval FROM dual;

Sequence name. currval: indicates that the current sequence has grown. After repeated calls, the sequence content does not change and the size of the current sequence (LAST_NUMBER) does not change;
Sequence name. nextval: indicates the next Growth Value of a sequence. The sequence increases automatically every time it is called.

Example 3,Use sequence when inserting data

-- CREATE member table create table member (mid NUMBER, NAME VARCHAR2 (50) not null, CONSTRAINT pd_mid primary key (mid); -- Query table select * from member; -- insert into member (Mid, Name) VALUES (myseq. nextval, 'zhang Sanfeng '); insert into member (Mid, Name) VALUES (myseq. nextval, 'zhang cuishan '); insert into member (Mid, Name) VALUES (myseq. nextval, 'zhang Wuji '); -- Query table SELECT * from member; -- it is found that the MID will automatically increase

Delete Sequence

Drop sequence name

-- Delete the myseq sequence drop sequence myseq;

Sequence for creating special functions

• Set the INCREMENT step of the sequence: INCREMENT

Syntax:

Create sequence name increment by step size

Example 4,The creation sequence step is 3.

Create sequence myseq increment by 3; -- Query user_sequencesSELECT * FROM User_Sequences; -- step size is 3 -- call myseq SEQUENCE -- join and execute SELECT myseq. nextval FROM dual; -- view the current sequence SELECT myseq. currval FROM dual;

• Set the sequence's initial value START

Syntax:

Create sequence name start with Initial Value

Example 5,The initial creation order is 30, and each step is 2

 

Drop sequence myseq; create sequence myseq start with 30 increment by 2; -- Query user_sequencesSELECT * FROM User_Sequences; -- Initial Value: 30, step size: 2 -- call myseq SEQUENCE -- join to execute SELECT myseq. nextval FROM dual; -- view the current sequence SELECT myseq. currval FROM dual;

 

• Set the CACHE of the sequence | NOCACHE

Syntax:
Create sequence name CACHE size | NOCACHE

Example 6,Create a sequence, set cache to 100, and do not use Cache

Drop sequence myseq; -- create sequence use cache create sequence myseq CACHE 100; -- Query user_sequencesSELECT * FROM User_Sequences; -- create sequence without using cache drop sequence myseq; create sequence myseq NOCACHE; -- Test SELECT myseq. nextval FROM dual;

• Set loop Sequence

Syntax:

Create sequence name [maxvalue sequence maximum value |NOMAXVALUE] [MINVALUE sequence minimum value |NOMINVALUE] [CYCLE |NOCYCLE]; Example 7,Create a circular sequence to make the content of the sequence cycle between 1, 3, 5, 7, and 9.

Drop sequence myseq; -- create sequence myseqSTART WITH 1 increment by 2 MAXVALUE 10 MINVALUE 1 CYCLECACHE 3; -- execute SELECT myseq. nextval FROM dual multiple times;

Modify sequence:The sequence itself is also a database object. As long as it is a database object, you can modify it after creation. The Sequence Modification syntax is as follows:

Alter sequence name [increment by step] [MAXVALUE maximum value | NOMAXVALUE] [MINVALUE minimum value | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE size | NOCACHE];

The modification method is the same as the setting method, but the change of "CREATE" to "ALTER"

An Automatic sequence is provided after Oracle 12C, which is created in the creation table. Automatic sequence syntax

Create table 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 size | NOCACHE]), column name type ,... );

 

Example 8,Create a data table with auto-increment Columns

CREATE TABLE mytab(       mid               NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),       NAME              VARCHAR2(20)     NOT NULL;       CONSTRAINT pd_mymid PRIMARY KEY(mid)       );

Summary: