ORACLE Sequence Usage

Sequence in Oracle is the serial number, which automatically increases each time it is fetched. Sequence is not related to the table.

1, create Sequence first to have create Sequence or create any Sequence permissions.

The statement is created as follows:

CREATE SEQUENCE Seqtest
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 ten ; --Sets the cached cache sequence, which can also be set to---------if the system is down or other conditions will cause the sequence to be discontinuous nocache

2, Get sequence value

Once you have defined the sequence, you can use Currval,nextval to get the value.
Currval: Returns the current value of sequence
Nextval: Increase the value of the sequence, and then return the increment after the sequence value gets the value statement 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, view
-Subquery in INSERT statement
-The values of the INSERT statement
-In the set of the UPDATE

As in the INSERT statement

Insert into table name (Id,name) VALUES (seqtest. Nextval, ' sequence insert test ');

Note:-The first time the Nextval returns the initial value, then the nextval automatically increases the increment by value you define and then returns the added value.

Currval always returns the value of the current sequence, but cannot use currval after the first nextval initialization, otherwise there will be an error.

Once nextval will add a sequence value, so if you use multiple nextval within the same statement, the value is different.
-If the cache value is specified, Oracle can place some sequence in memory in advance so that the access is faster. Cache inside of the finished, Oracle automatically take a group to cache. Use cache may jump number, such as the database suddenly abnormal down (shutdown abort), the cache in the sequence will be lost. So you can use NoCache to prevent this when you 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.

Example: Alter sequence seqtest maxvalue 9999999 ; Another: Sequence_cache_entries parameters, set can be CACHE at the same time the number of SEQUENCE.

4, Drop Sequence drop Sequence seqtest;

5, an example

  Create  sequence seq_id
minvalue  1
maxvalue  99999999
start  with   1
increment  by   1
N Ocache
Order;

The sender code is:

Create   or   replace   trigger  tri_test_id
  before  Inser T   on  S_Depart   -s_depart  is the table name
   for  each row
Declare br>   nextid  number;
Begin
   IF  :new. departid  is  nullor :new. Departid = 0   THEN  -departid is column name
     Select  seq_id.nextval  -seq_id just created
     into  nextid
     from   sys.dual;
    :new. Departid: = NextID;
   end   if;
End  tri_test_id;

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

Note:: New represents the value of data changes, corresponding to the following: 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

Article reproduced from the blog park