ORACLE Sequence Usage Solution

In Oracle, sequence is the ordinal, and it increases automatically each time it is taken. The sequence is not tied to the table.

1. Create Sequence

The first thing to do is create sequence or create any sequence rights.
The following are the words of the creative language:

CREATE SEQUENCE Seqtest
INCREMENT by 1--each time you add a few
Start with 1--start from 1
Nomaxvalue--not setting maximum
Nocycle--always accumulating, not circulating
CACHE 10; --Set the buffer cache sequence or set it to NoCache

2. Get Sequence value

After defining good sequence, www.aspphp.online you can use Currval,nextval to get the value.
Currval: Returns the current value of the sequence
Nextval: Increase the value of sequence and then return the sequence value after increment

Get the value of the sentence as follows:
SELECT sequence name. Currval from DUAL;

If you get the words that create sequence values on the top side:

Select Seqtest.currval from dual

Where you can use sequence in a SQL sentence:
-Do not include sub-search, snapshot, view of the SELECT language
-In the sub-search of the Insert language
-In the values of the Insert language
-In the set of UPDATE

As in the Insert language

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

Note:

– The first Nextval return is the initial value, and then the nextval will automatically increase the value of your increment by, and then return the value added.
Currval always returns the value of the former sequence, but the currval will not be used until the first nextval is initialized, or it would be wrong.

A nextval will increase the value of sequence once, so if you use multiple nextval in the same language, the value is different.
-If you specify a cache value, Oracle can first place some sequence in the inside of the store, which is quicker to access. After the cache is taken, Oracle automatically takes a group to the cache. Using a cache or a shutdown, such as a sudden failure of the library to drop (abort), the sequence in the cache is lost. So you can use NoCache to prevent this situation when you create sequence.

3. Alter Sequence
Alter any SEQUENCE rights 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 the SEQUENCE number that can be CACHE at the same time.
4. Drop Sequence
DROP SEQUENCE seqtest;
5. An example
CREATE sequence seq_id
MinValue 1
MaxValue 99999999
START with 1
Increment by 1
NoCache
ORDER;

Build the Generator code:
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 was just created.
Into NextID
From Sys.dual;
: NEW. Departid:=nextid;
END IF;
END tri_test_id;

OK, the above code will be able to actually self-increment function.

Note: The new value is represented by the change, and the corresponding LD original value

: = Delegate Value

: NextID represents the variable that references the definition in Sqlplus

More related articles: http://www.aspphp.online/shujuku/oraclesjk/Oraclejc/201701/117038.html

ORACLE Sequence Usage Solution