Introduction to the simple use of ORACLE PL/SQL sequence (sequence)

If I were Cristiano Ronaldo . Brief introduction of the simple use method of ORACLE PL/SQL sequence (sequence)

Sequence is widely used in Oracle, meaning the serial number, which automatically increases the specified variable, such as 1 or 2 successive increments or more.

1. Creating a sequence Create Sequence

You first have to have create SEQUENCE or create any SEQUENCE permissions

CREATE SEQUENCE cux_demo_sequence
MINVALUE 1
MAXVALUE 99999999999
START with 10000
INCREMENT by 1
Nocycle
CACHE 20
ORDER;

Comments:

MINVALUE 1--Minimum value
MAXVALUE 99999999999--Maximum value
Start with 10000--start value
INCREMENT by 1--1 increase each time
Nocycle------always accumulate without circulation
Cache 20--caching
ORDER;

There are other parameters, such as:

Nomaxvalue--No maximum value
NOCACHE--Do not set the cache

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.

2. Using sequences

Define sequence, and 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

EXAMPLE:

Cux_demo_sequence. Currval
Cux_demo_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 Cux_demo_table VALUES
(Cux_demo_sequence. Nextval, 123, ' IBAD ', ' MARK ', ' Y ');

SELECT cux_demo_sequence. Currval from DUAL;

Attention:

The first nextval returns the initial value, and the subsequent nextval automatically increments the IncrementBy 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.

3. Modify the sequence alter SEQUENCE

You are either the owner of the SEQUENCE or have alter any SEQUENCE permission to change the SEQUENCE. You can alter all SEQUENCE parameters except start to. If you want to change the start value, you must DROP SEQUENCE and recreate the SEQUENCE.

Alter SEQUENCE
ALTER SEQUENCE cux_demo_sequence
INCREMENT by 10
MAXVALUE 10000
CYCLE--from the beginning to 10000
NOCACHE;

Initialization parameters that affect sequence:

Sequence_cache_entries = Sets the number of SEQUENCE that can be simultaneously CACHE.

4. Delete Sequence Drop SEQUENCE

Can be very simple drop SEQUENCE

DROP SEQUENCE cux_demo_sequence;

An improvement of PL/SQL access sequence in 11g

5. Access a sequence in PL/SQL code

Prior to Oracle 11g, Puber, who was familiar with PL/SQL programming, knew that when accessing a sequence in PL/SQL code, the general practice would be, for example:
DECLARE v_n number;
BEGIN
SELECT Seq.nextval to v_n from Dual;
....
END;

So we all feel very cumbersome, and so write a certain performance overhead, but no way, so good, to 11g, this problem Oracle developers worry about you.

In 11g, the re-modification of the access method not only improves the efficiency of the operation, but also makes the invocation method in PL/SQL very simple, in 11g, you can simply handle:
DECLARE V_n Number:=seq.nextval;
BEGIN
....
END;

Introduction to the simple use of sequence (sequence) in ORACLE PL/SQL