ORACLE sequence usage (self-growth)

In Oracle, sequence is the serial number, which automatically increases each time it is taken. Sequence is not related to tables.  

1. Create Sequence

First, create sequence or create any sequence permissions.

Create the following statement:

　　

 create   SEQUENCE seqtestincrement  Span style= "color: #0000ff;" >by  1  -- Span style= "color: #008080;" > add several  START with  1  --  starting from 1 count  nomaxvalue --  not set maximum  nocycle --  keep accumulating, do not loop  CACHE 10 ; --  sets the cache sequence, if the system is down or otherwise causes the sequence to be discontinuous, or set to---------NOCACHE  

2. Get sequence value

Once you've defined the sequence, you can use Currval,nextval to get the value.
Currval: Returns the current value of the sequence
Nextval: Increase the value of sequence and return the sequence value after increment

The resulting value statement is as follows:SELECT sequence name. Currval from DUAL;

If you get the statement above to create the sequence value:

Select seqtest.currval from dual

Where sequence can be used in SQL statements:
-SELECT statements that do not contain subqueries, snapshot, and view
-INSERT statement in subqueries
-In the values of the INSERT statement
-In the set of UPDATE

As in the INSERT statement

Insert into  table name (ID, Name) values ( seqtest. Nextval , sequence  Insert Test );

Note:

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

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

3. Alter Sequence
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.

Cases:

alter sequence seqtest maxvalue 9999999; Another: Sequence_cache_entries parameter, set the number of SEQUENCE that can be simultaneously CACHE.

4. Drop SequenceDROP SEQUENCE seqtest;

5. An example

Createsequence Seq_idminvalue1MaxValue99999999Start with 1Increment by 1NoCacheOrder; The code for the hair generator is:Create or Replace Triggertri_test_id beforeInsert  onS_depart--S_depart is the table name   forEach rowDeclareNextID Number;begin  IF: New. Departid isNullor:new. Departid=0  Then --Departid is a column name    SelectSeq_id.nextval--seq_id is exactly what you just created.     intoNextID fromsys.dual; : New. Departid:=NextID; End if;Endtri_test_id;

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

Note: New represents the changed value of the data, corresponding to: old original value

: = Delegate Assignment

:NextID represents a reference to a variable defined in Sqlplus

Reference Document: Http://www.cnblogs.com/scottckt/archive/2011/09/20/2182168.html

ORACLE sequence usage (self-growth)