Table about building a primary key from 1 in Oracle database

Small knowledge Point: Dual is a pseudo table, is a single field, a row of records of the table.
    do not need to get the data from the table, but simply in order to get some of the information we want, and to complete through select, it is necessary to use an object, this object, is dual;

--1, creating a sequence

Create sequence Seq_test
Increment by 1--add a few at a time
Start with 1-counting from 1
Nomaxvalue--Do not set maximum value
Nocycle--Cumulative, not cyclic
Cache 10--Number of caches

--2, direct use sequence
INSERT into test values (seq_test. Nextval, ' Liu Bei ', 27;
INSERT into test values (seq_test. Nextval, ' Zhang Fei ', 28;
--ID Name age
-------------------------------------
--1 Liu Bei 27
--2 Flyer 28

--3, creating a trigger implicitly using a sequence
Create trigger trg_test before insert on test
For each row
Begin
Select Seq_test.nextval into:new.id from dual;
End
--no longer explicit call sequence when used
INSERT into Test (name,age) values (' Liu Bei 2 ', 27);
INSERT into Test (name,age) VALUES (' Zhang Fei 2 ', 28);
--ID Name age
-------------------------------------
--1 Liu Bei 27
--2 Flyer 28
--3 Liu Bei 2 27
--4 Zhang Fei 2 28



--I. Grammar:
--CREATE SEQUENCE sequence name
--[INCREMENT by N]
--[START with N]
--[{Maxvalue/minvalue n| Nomaxvalue}]
--[{cycle| Nocycle}]
--[{CACHE n| NoCache}];
--
--1, INCREMENT by: To define the step size of the sequence, if omitted, the default is 1, if a negative value,
-The value representing the Oracle sequence is decremented by this step.
--2, START with: Defines the initial value of the sequence (that is, the first value produced), and defaults to 1.
--3.1, MAXVALUE: Defines the maximum value that a sequence generator can produce. Option Nomaxvalue is the default option,
--represents no maximum definition, at which point the system can produce the maximum
--The value is 10 27; For descending sequences, the maximum value is-1.
--3.2, MinValue: Defines the minimum value that a sequence generator can produce. Option Nomaxvalue is the default option,
--on behalf of the absence of a minimum definition, then for the descending sequence, the minimum value that the system can produce is 10 of the 26-second party;
For an ascending sequence, the minimum value is 1.
--4, cycle, and nocycle: Indicates whether the value of the sequence generator loops after it reaches its limit.
--cycle represents circulation, nocycle represents not cycle. If the loop, then when the increment sequence reaches
--loops to the minimum when the maximum value is reached, and loops to the maximum when the descending sequence reaches the minimum value.
--If you do not loop, and you reach a limit, you will get an error when you continue to produce a new value.
--5, cache (buffer): Defines the size of the memory block that holds the sequence, and defaults to 20. NoCache indicates no memory buffering of the sequence.
--memory buffering of the sequence can improve the performance of the sequence.
--
--ii. modification of sequence
-Must be owner of the SEQUENCE, or have alter any SEQUENCE permission to change SEQUENCE.
--You can alter all sequence parameters except the start value. If you want to change the start value,
--Must drop sequence again re-create.
--an example of Alter sequence
--ALTER SEQUENCE emp_sequence
--INCREMENT by 10
--MAXVALUE 10000
--CYCLE--start from scratch after 10000
--NoCache;
--Third, delete Sequence:drop SEQUENCE seq_test;