Detailed Oracle Sequence usage _oracle

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, create sequence or create any sequence permissions.

The statement is created as follows:

CREATE SEQUENCE seqtest
INCREMENT by 1--add a few start with 1 at a time--
count Nomaxvalue from 1--
do not set maximum nocycle--
always tired Add, do not cycle cache
10;--Set cached cache sequence, if the system down or other conditions will cause sequence discontinuity, can also be set to---------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: Increases the value of the sequence and returns the sequence value after the increment

The resulting value statement is as follows:

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.

Cases:

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

5, an example

Create sequence seq_id
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache order
;

The Builder code is:

Create or replace trigger tri_test_id
 before insert on S_depart  --s_depart is the 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 the into NextID from sys.dual that you just created
  ;
  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

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.