Use and description of Oracle sequence ____oracle

1, the creation and description of the sequence

To create a sequence, the syntax format for the Oracle sequence is:
CREATE SEQUENCE sequence Name
[INCREMENT by N]
[START with N]
[{Maxvalue/minvalue n| Nomaxvalue}]
[{cycle| Nocycle}]
[{CACHE n| NoCache}];

1) INCREMENT by is used to define the step size of the sequence, if omitted, the default is 1, and if a negative value is present, the values representing the Oracle sequence are decremented by this step.

2 START with defines the initial value of the sequence (that is, the first value produced), and the default is 1.

3) MAXVALUE defines the maximum value that a sequence generator can produce. Option Nomaxvalue is the default option, which represents the absence of a maximum definition, in which case the maximum value that the system can produce for an ascending Oracle sequence is 10 27; For a descending sequence, the maximum value is-1.

4) MinValue defines the minimum value that a sequence generator can produce. Option Nomaxvalue is the default option, which represents the absence of a minimum value definition, in which case the minimum value that the system can produce for a descending sequence is 10 26; For an ascending sequence, the minimum value is 1.

5 cycle and Nocycle indicate whether the value of the sequence generator loops after it reaches its limit. Cycle represents loops, and nocycle represents not loops. Loops to the minimum when the increment sequence reaches its maximum value, and loops to the maximum value for the descending sequence when it reaches the minimum value. If you do not loop, and the limit is reached, the new value continues to produce an error.

6 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.

Example:

CREATE SEQUENCE emp_sequence
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
Noorder; --the order in which the sequence is obtained in parallel

2, the syntax to delete the Oracle sequence is the drop SEQUENCE sequence name;

3, the use of

1> View sequence values:

Select sequence. Nextval (sequence. Currval) from dual

2> when you insert data into a table:
Insert into table name values (sequence name. Nextval, column 1 value, column 2 value, ...);
Insert into table name (sequence name. Currval, column 1 value, column 2 value ...);