ORACLE Create a self-increment field method-oracle sequence Simple Introduction

Referenced from: http://www.2cto.com/database/201307/224836.html ORACLE create a self-increment field method-oracle A simple introduction to sequence first, let's assume there's a table:

Create  table S_depart  (    departid              INT                              not  null,    departname           NVARCHAR2 (+)                    not  NULL,    Departorder           INT                             default  0,     constraint pk_s_depart  Primary  

In Oracle, sequence is the so-called serial number, which is automatically incremented each time it is taken, and is typically used where serial numbers need to be sorted. 1, create Sequence you first want to have create Sequence or create any Sequence permissions, create Sequence emp_sequence INCREMENT by 1--add a few START each time With 1-counting from 1 nomaxvalue--not setting maximum nocycle--always accumulate, do not cycle CACHE 10; --Set the cache sequence, if the system is down or otherwise will cause the sequence to be discontinuous or set to---------NoCache the sequence created for S_depart are as follows:

Create sequence S_s_depart minvalue  1 maxvalue  999999999999999999999999999 start with  1 increment  by  

Note, however, that the first nextval returns the initial value, and the subsequent nextval automatically increases the increment by value that you define, and then returns the incremented value. 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. A nextval will increment the value of sequence once, so if you use multiple nextval in the same statement, the value is different.  Got it? -If you specify the cache value, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdown abort), the cache sequence will be lost.  So you can use NoCache to prevent this when the create sequence. 2, Alter Sequence you or the owner of the Sequence, or 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.

Alter sequence example of ALTER sequence emp_sequence INCREMENT by MAXvalue 10000 CYCLE-from 10000 onwards NOCACHE; Initialization parameters that affect SEQUENCE: Sequence_cache_entries = Sets the number of SEQUENCE that can be simultaneously affected by the CACHE. Can be very simple drop Sequence drop Sequence order_seq; A simple example: Create sequence Seq_idminvalue 1maxvalue 99999999start with 1increment by 1nocacheorder;

The built-in code for the hair generator is:

Create or replace trigger tri_test_id  before insert on S_depart   --s_depart is the table name for each  rowdeclare  NextID number;begin  if:new. Departid is nullor:new. Departid=0 then--departid is the column name    Select Seq_id.nextval--seq_id was just created into the    nextid from    sys.dual;    : New. Departid:=nextid;  End If;end tri_test_id;

OK, the above code can realize the function of auto increment.

ORACLE Create a self-increment field method-oracle sequence Simple Introduction