ORACLE does not have an auto-increment data type. To generate a business-independent primary key column or a unique constraint column, you can use the sequence. CREATESEQUENCE statements and parameters: For details, see the official document docs.oracle.comcdB19306_01server.102b14200statements_6015.htm # SQLRF01314 to CREATE a sequence: CREATE
ORACLE does not have an auto-increment data type. To generate a business-independent primary key column or a unique constraint column, you can use the sequence. Create sequence statement and parameter Introduction: see the official documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm#SQLRF01314 create sequence: Need to have CREATE
ORACLE does not have an auto-increment data type. To generate a business-independent primary key column or a unique constraint column, you can use the sequence.
Create sequence statement and parameter Introduction: see the official documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm#SQLRF01314
Create sequence: You must have the create sequence or create any sequence permission,
Create sequence [schema.] sequence
[{Increment by | start with} integer
| {MAXVALUE integer | NOMAXVALUE}
| {MINVALUE integer | NOMINVALUE}
| {CYCLE | NOCYCLE}
| {CACHE integer | NOCACHE}
| {ORDER | NOORDER}
];
Parameters of create sequence:
SchemaSpecify the user schema in which the sequence is created. If this parameter is not specified, the sequence is created under the current user by default.
SequenceSequence name to be created
Note:If you only specify the preceding parameters, an incremental sequence starts from 1 and increments in units of 1 without the maximum value.
If you want to create a sequence without constraints, when increasing the sequence: Ignore the MAXVALUE parameter or specify NOMAXVALUE; descent sequence: omit the MINVALUE parameter or specify NOMINVALUE.
If you want to create a restricted sequence, specify the MAXVALUE parameter for the incremental sequence, and specify the MINVALUE parameter for the descent sequence. When the sequence reaches the limit, an error is returned:
If you want to create a restricted sequence and restart it after it reaches the limit, after specifying MAXVALUE and MINVALUE, you also need to specify CYCLE. If MINVALUE is not specified, the default value is NOMINVALUE. The value is 1.
######
INCREMENTSpecifies the interval between serial numbers. This integer can be any positive integer or negative integer, but cannot be 0. This value can contain up to 28 numbers. The absolute value must be less than the difference between MAXVALUE and MINVALUE (for example, if not in this range, the error: ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE ). If this value is negative, the sequence decreases. If the value is positive, the sequence goes up. If this clause is omitted, the default interval is 1.
Start with specifies the first serial number to be generated.
This clause starts an incremental sequence, which must be greater than the minimum value; or starts a descending sequence, which is less than the maximum value. For incremental sequence, the default value is the minimum value in the sequence. For a descending sequence, the default value is the maximum value in the sequence. This integer can contain a maximum of 28 numbers.
This value does not matter when it is restarted after reaching the Maximum/minimum limit. (For example, if the maximum and minimum values are specified during incremental sequence creation and the CYCLE value is specified, after the sequence reaches the maximum value, starts from the minimum value. If the minimum value of the package is not specified, the default value starts from 1.
MaxValueSpecify the maximum value that can be generated by the sequence. This integer can contain a maximum of 28 numbers. MAXVALUE must be greater than or equal to start with and MINVALUE.
NOMAXVALUE:If NOMAXVALUE is specified, the maximum value of the ascending sequence is 10 to the power of 27, or the maximum value of the descending sequence is-1. This is the default one.
MINVALUE:Specify the minimum value of the sequence. This integer can contain a maximum of 28 numbers. MINVALUE must be <= start with and must be smaller than MAXVALUE. The following error occurs: ORA-04006: start with cannot be less than MINVALUE. If this parameter is not specified, the default value is 1.
NOMINVALUE:Specify NOMINVALUE to indicate that the minimum value of the ascending sequence is 1, and the descending sequence is the 26 power of the negative 10. This is the default one.
CYCLE:Indicates the value generated after the maximum or minimum value of a sequence is reached. When the incremental sequence reaches the maximum value, the cycle starts from the minimum value. When the descending sequence reaches the minimum value, the cycle starts from the maximum value.
NOCYCLE:Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default one. An error is reported when the maximum value is reached: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
CACHE:Specify the number of pre-allocated values of the database in the memory for faster access. This integer can contain a maximum of 28 numbers. The minimum value of this parameter is 2. The value must be smaller than the number of CYCLE cycles (for example, from 1 to 100 is a loop, and the CACHE must be smaller, otherwise, an error may occur when the CACHE value is repeated. The error is: ORA-04013: number to CACHE must be less than one cycle ).
Calculation formula: (CEIL (MAXVALUE-MINVALUE)/ABS (INCREMENT)
If the system fails, unused CACHE values in the memory will be lost, resulting in sequential disconnections. ORACLE recommends using CACHE in RAC to improve performance.
NOCACHE:This sequence value is not pre-allocated. If CACHE and NOCACHE are omitted, the database caches 20 serial numbers by default.
ORDER:This parameter must be specified only when RAC is used. The ORDER is specified to ensure that the serial number is generated only when a request is required. It is useful when serial numbers are used as a timestamp.
NOORDER: This is the default value.
Sequence has the following impact on system performance: --> Seq $ base table
See: http://blog.itpub.net/17203031/viewspace-717042
1.Seq $ base table is a data dictionary table that records system sequence. Each call to nextval will recursively call and update and COMMIT Seq $ base table.
2.Updating the Seq $ base table and submitting the table will generate a redo log of several hundred bytes. Frequent COMMIT will lead to LGWR pressure. Excessive redo log generation will lead to LGWR pressure and recovery time.
3.Multiple sessions may use sequence, waiting for the event row lock contention
For nocache/cache parameters:
Nocache: each time nextval is used, the Seq $ base table is updated and COMMIT is performed.
Cache:The Seq $ base table is updated and submitted only after the serial number of the cache in the memory is used. For example
CacheIf this parameter is set to 2000, the performance impact of sequence is times lower than that of nocache.
Therefore, we recommend that you set a large cache value for performance optimization. (Nocache is 20 by default)
If the sequence number is not ordered, we recommend that you use NOORDER +CACHE
######################################## #############
ORACLE sequence creation example: create sequence bys. test_seq
Increment by 3
Start with 5
Maxvalue 18
Minvalue 4
Cycle
Cache 4;
Create a sequence named test_seq under the bys user
Starting from 5, increase 3 each time, the maximum value is 18, and the minimum value is 4
Reuse is allowed. cache 4 indicates that four serial numbers will be cached, for example, 5 8 11 14.
Of course, you can also use the simplest one in the experiment: create sequence bys. test_seq2; Do not write other parameters, use the system default haha
######################################## #############
ORACLE sequence Modification and deletion example: alter sequence [schema.] sequence
{Increment by integer
| {MAXVALUE integer | NOMAXVALUE}
| {MINVALUE integer | NOMINVALUE}
| {CYCLE | NOCYCLE}
| {CACHE integer | NOCACHE}
| {ORDER | NOORDER}
}
Three notes for modification:
If you want to make the sequence start with different numbers, you can only delete the sequence reconstruction.
If you change the value of increment by before using NEXTVAL to initialize the sequence, some serial numbers will be skipped. Solution to skip problem -- delete and recreate
The new values of the modified parameters must still meet the descriptions described in the parameters of create sequence.
Example of changing the value of increment by before the NEXTVAL initialization sequence:
Create sequence bys. seq3
Increment by 3
Start with 5
Maxvalue 18
Nominvalue
Cycle
Cache 4;
BYS @ bys3> alter sequence bys. seq3 increment by 5; -- Modified before Initialization
Sequence altered.
BYS @ bys3> select seq3.nextval from dual; -- some numbers are indeed skipped during initialization ..
NEXTVAL
----------
7
BYS @ bys3> select seq3.nextval from dual;
NEXTVAL
----------
12
Example modification statement:
Alter sequence customers_seq MAXVALUE 1500;
Alter sequence customers_seq cycle cache 5;
Delete sequence statement:
Drop sequence [schema.] sequence_name;
For example, BYS @ bys3> drop sequence bys. seq2;
############################
ORACLE sequence use example see Official documentation-http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#i1006157
Common sequence scenarios:
It can be used in SELECT statements, create table... as select statements, and create materialized view... as select statements.
In the SET of UPDATE, in the INSERT clause or VALUES. The sequence can be accessed by multiple users at the same time without waiting or locking.
The first query uses NEXTVAL to return the initial value of the sequence.
Use CURRVAL to query the current serial number, and return the value returned by the last referenced NEXTVAL.
Use NEXTVAL to query the next serial number. When this command is used, the sequence first adds 1 or the value specified by increment by, and then returns the sequence value.
Query in this experiment:
BYS @ bys3> select test_seq.currval from dual; --- this error is returned because NEXTVAL is not used for initialization.
Select test_seq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session.
BYS @ bys3> select test_seq.nextval from dual; the first time NEXTVAL is used, the value specified by start with is displayed.
NEXTVAL
----------
5
BYS @ bys3> select test_seq.currval from dual; Use currval to find the current serial number-value returned by the last reference to NEXTVAL
CURRVAL
----------
5
#######
BYS @ bys3> select test_seq.nextval from dual; -- execute nextval all the time and observe how to use the sequence cyclically after it reaches the value specified by maxvalue
NEXTVAL
----------
17
BYS @ bys3> select test_seq.nextval from dual; -- after the sequence reaches the value specified by maxvalue, the value specified by minvalue is returned instead of start. If minvalue is not specified or NOMINVALUE is specified, 1 is returned.
NEXTVAL
----------
4
########
BYS @ bys3> insert into test values (test_seq.nextval, 'seqtest'); -- use the INSERT statement to call the sequence
1 row created.
BYS @ bys3> select * from test;
OBJECT_NAME STATUS
-------------------
10 seqtest
BYS @ bys3> insert into test values (test_seq.currval, 'seqtest ');
1 row created.
BYS @ bys3> select * from test;
OBJECT_NAME STATUS
-------------------
10 seqtest
10 seqtest
BYS @ bys3> insert into test (object_name) select test_seq.nextval from dual; -- use the INSERT sub-language to call the sequence
1 row created.
BYS @ bys3> select * from test;
OBJECT_NAME STATUS
-------------------
10 13
10 16
4
###########
BYS @ bys3> update test set status = test_seq.nextval; -- use the UPDATE statement to call the sequence
2 rows updated.
BYS @ bys3> select * from test;
OBJECT_NAME STATUS
-------------------
10 13
10 16
BYS @ bys3> delete test where status = test_seq.currval; -- sequence cannot be used as a condition in DELETE.
Delete test where status = test_seq.currval
*
ERROR at line 1:
ORA-02287: sequence number not allowed here
###################
Auto-increment sequence is automatically inserted for the table using the unreseller: --- similar to the auto-increment Field
The code of the producer is:
Create or replace trigger tri_test_id
Before insert on test -- test is the table name.
For each row
Declare
Nextid number;
Begin
IF: new. testid is null or: new. testid = 0 THEN -- DepartId IS the column name.
Select seq1.nextval -- seq1 is the name of the sequence created in advance.
Into nextid from sys. dual;
: New. testid: = nextid;
End if;
End tri_test_id;
