Oracle SEQUENCE Create, modify, delete

Oracle Create serialization:

CREATE SEQUENCE seq_itv_collection
INCREMENT by 1--add a few each time
Start with 1399-counting starting from 1
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
CACHE 10;

Oracle Modified serialization: Alter Sequence
If you want to change the start value, you must drop sequence and then re-create.
An example of Alter sequence
ALTER SEQUENCE emp_sequence
INCREMENT by 10
MAXVALUE 10000
CYCLE--from the beginning to 10000
NOCACHE;

Oracle Delete Serialization:

DROP SEQUENCE seq_itv_collection;

It is important to note that the minvalue that want to change the serialization must be deleted and then re-serialized. The serialized MinValue cannot be modified.

ORACLE Sequence Usage

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

Let's first assume that there is a table:

CREATE TABLE S_depart (
Departid INT NOT NULL,
Departname NVARCHAR2 (+) NOT NULL,
Departorder INT Default 0,
Constraint Pk_s_depart primary KEY (Departid)
);

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 have to have create sequence or create any sequence permissions,
CREATE SEQUENCE emp_sequence
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
The sequence created for S_depart are as follows:

Create sequence S_s_depart
MinValue 1
MaxValue 999999999999999999999999999
Start with 1
Increment by 1
NoCache

Once you've defined emp_sequence, you can use Currval,nextval
Currval= returns the current value of the sequence
Nextval= increases the value of sequence and then returns the sequence value
Like what:
Emp_sequence. Currval
Emp_sequence. Nextval

Places where you can use sequence:
-SELECT statements that do not contain subqueries, snapshot, and view
-INSERT statement in subqueries
-In the values of the Nsert statement
-In the set of UPDATE

You can see the following example:

Insert into S_depart (Departid,departname,departorder) VALUES (s_s_depart.nextval, ' 12345 ', 1);

SELECT empseq.currval from dual; 

But note that:  
-The first time Nextval returns an initial value The subsequent nextval will automatically increase the increment by value you define, and then return the added 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? &NBSP

-If you specify a 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. &NBSP

2, alter SEQUENCE 
you or the owner of the Sequence, or 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 again re-create.  
Alter sequence example  
Alter SEQUENCE emp_sequence 
INCREMENT by 10 
MAXvalue 10000 
CYCLE--  from the beginning to 10000;
NOCACHE;  


Affects initialization parameters of SEQUENCE:  
Sequence_cache_entries = Sets the number of SEQUENCE that can be CACHE at the same time.  

can be very simple drop sequence 
Drop Sequence order_seq; 

A simple 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:

CreateorReplaceTriggertri_test_id
before Insert onS_depart--s_depart is a table name
for eachRow
Declare
NextID Number;
begin
IF: New. Departid isNullor: 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.

Oracle SEQUENCE Create, modify, delete