What does Sequence mean?
SQL> select * from v $ version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production the following is an official document from 11gR2: CREATE SEQUENCE
Purpose
Use the create sequence statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. if two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. one user can never acquire the sequence number generated by another user. after a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. it is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. additionally, a single user may not realize that other users are drawing from the same sequence.
After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudo docolumn, which returns the current value of the sequence, or the NEXTVAL pseudo docolumn, which increments the sequence and returns the new value.
Note on Using Sequences with Deferred Segments If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.
See Also:
Chapter 2, "pseudo columns" for more information on using the CURRVAL and NEXTVAL
"How to Use Sequence Values" for information on using sequences
Alter sequence or drop sequence for information on modifying or dropping a sequence
Prerequisites (Prerequisites)
To create a sequence in your own schema, you must have the create sequence system privilege.
To create a sequence in another user's schema, you must have the create any sequence system privilege.
Syntax)
Create_sequence: =
A sequence is a database item that generates a sequence of integers. The generated integers can usually be used to fill the primary key columns of the numeric type.
First, create a sequence:
SQL> create sequence text
2;
The sequence has been created.
The above is a sequence without parameters. We create a sequence with parameters:
SQL> create sequence text_1 start with 10 increment by 5 minvalue 10 maxvalue 20
2 cycle cache 2 order;
The sequence has been created.
SQL> select * from dba_sequences where sequence_name like '% TEXT % ';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O
----------------------------------------------------------------------------------------------
CACHE_SIZE LAST_NUMBER
---------------------
Sys text 1 1.20.e + 28 1 N
20 1
SYS TEXT_1 10 20 5 Y
2 10
How to Use sequences? Below we will do a small experiment:
The sequence generates a series of numbers. A sequence contains two "pseudo columns", namely, currval and nextval. The current and next values of the sequence can be used respectively.
Before retrieving the current value of a sequence, the sequence must be initialized by the next value of the sequence:
SQL> select text. nextval from dual;
NEXTVAL
----------
1
SQL> select text. currval from dual;
CURRVAL
----------
1
SQL> select text. nextval from dual;
NEXTVAL
----------
2
SQL> select text. currval from dual;
CURRVAL
----------
2
SQL> select text. nextval from dual;
NEXTVAL
----------
3
========================================================== ========================================
SQL> select text_1.currval from dual;
Select text_1.currval from dual
*
Row 3 has an error:
ORA-08002: sequence TEXT_1.CURRVAL not defined in this session
SQL> select text_1.nextval from dual;
NEXTVAL
----------
10
SQL> select text_1.currval from dual;
CURRVAL
----------
10
SQL> select text_1.nextval from dual;
NEXTVAL
----------
15
SQL> select text_1.nextval from dual;
NEXTVAL
----------
20
SQL> select text_1.currval from dual;
CURRVAL
----------
20
========================================================== ====================
Fill the primary key with sequence:
When the table's primary key is an integer, the sequence can be used to generate the primary key:
SQL> create table t_text (id integer constraint t_text_pk primary key );
The table has been created.
SQL> create sequence text_te nocache;
The sequence has been created.
When the sequence is used to fill in the primary key, nocache is usually used, which can avoid the sequence discontinuous and the sequence discontinuous. The reason for this is that when the database is closed, cached
All values will be lost. However, using nocache reduces performance. However, if the primary key is not continuous, use the cache.
SQL> insert into t_text values (text_te.nextval );
One row has been created.
SQL> select * from t_text;
ID
----------
1
SQL> insert into t_text values (text_te.nextval );
One row has been created.
SQL> select * from t_text;
ID
----------
1
2
==========================================
Modify the sequence, but there are restrictions:
Sequence initial values cannot be modified.
The minimum value of the sequence cannot be greater than the current value.
The maximum value of the sequence cannot be smaller than the current value.
SQL> ALTER SEQUENCE text_te increment by 5;
The sequence has been changed.
SQL> insert into t_text values (text_te.nextval );
One row has been created.
SQL> select * from t_text;
ID
----------
1
2
7
========================================================== ==================
Delete sequence:
Drop sequence sequence_name;
