Oracle sequence creation and use

I. Sequence INTRODUCTION

Sequence is a series of numbers automatically added by the database system according to certain rules, which is mainly used to generate database data records. This sequence is generally used as the surrogate primary key (because it is not duplicated).

sequence is the data in a special storage arithmetic progression table, the table is controlled by the database system, at any time the database system can be based on the current number of records and the size of the table to get to the next record should be how much, this table does not have practical significance, often used for the master key. sequence is the characteristic of database system, some databases have sequence, some are not. For example, Oracle, DB2, PostgreSQL database has sequence,mysql, SQL Server, Sybase and other databases are not sequence.

Here, the main discussion is the creation and use of Oracle sequence, the other is similar.

two. Sequence Create

　　Create Sequence before you want to get create sequence or create any sequence permissions.

1 --Create Sequence2 CreateSequence Seq_product_standard_chg--Sequence Instance name3MinValue1                                --minimum value, which can be set to 04MaxValue2147483647                       --Maximum Value5Start with 1                              --counting starting from 16Increment by 1                            --add a few each time7Cache -;--sets the cache sequence, if the system is down or otherwise causes the sequence to be discontinuous or set to---------NOCACHE

　Or it can be created as follows.

1 CREATESEQUENCE Seq_product_standard_chg2START with 1                      　　　　  --counting starting from 13Nomaxvalue--do not set the maximum value4INCREMENT by 1                    　　　　  --add a few each time5Nocycle--keep accumulating, not looping6CACHETen; --sets the cache sequence, if the system is down or otherwise causes the sequence to be discontinuous or set to---------NOCACHE

three. Sequence use

　　After creating the sequence, you can use Currval and nextval.

Currval: Returns the current value of the sequence

Nextval: Increase the value of sequence and return the sequence value after increment

If you get the statement above to create the sequence value (where Kingstar is the user, the user can actually omit it):

1 Select kingstar. Seq_product_standard_chg.currval 2 from dual

　Where sequence can be used in SQL statements:

1. 1. 2. SELECT Statements that do not contain subqueries, snapshot, and view
      4. In the subquery of the INSERT statement
      6. In the values of the INSERT statement
      8. In the set of UPDATE

Note: 1. Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs.

2. The first nextval returns the initial value, and the subsequent nextval automatically increments the increment by value that you defined, and then returns the incremented value.

four. Sequence modification

　have alter any SEQUENCE permission to change SEQUENCE. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

< Span class= "Apple-converted-space" >< Span class= "Apple-converted-space" >  1 alter sequence Kingstar. seq_product_standard_chg  maxvalue 9999999 ;   /span>

1 ALTER SEQUENCE Kingstar. Seq_product_standard_chg2by3100004cycle- 5 NOCACHE from the beginning to 10000  ;

Five. Sequence Delete

1 DROP SEQUENCE Kingstar. Seq_product_standard_chg;

　　

Oracle sequence creation and use