"Go" Oracle Sequence

http://blog.csdn.net/zhoufoxcn/article/details/1762351

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 Seqtest
INCREMENT by 1--add a few each time
Start with 1-counting starting from 1
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
CACHE 10; --Set cache caches sequence, if the system is down or otherwise will cause sequence discontinuity, can also be 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

Create sequence seq_id
MinValue 1
MaxValue 99999999
Start with 1
Increment by 1
NoCache
Order

The built-in code for the hair generator is:

Create or Replace Trigger tri_test_id
Before insert on S_depart--s_depart is a table name
For each row
Declare
NextID number;
Begin
If:new. Departid is nullor:new. Departid=0 then--departid is the column name
Select Seq_id.nextval--seq_id is exactly what you 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 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

"Go" Oracle Sequence