Chapter 7: Sequence
Sequence
is a Database Object
, you can use it to generates sequential integers that organizations can use to assist with internal control or simply to serve as primary keys for tables.
Create Sequence
CREATE SEQUENCE sequencename
[INCREMENT BY value]
[START WITH value]
[{MAXVALUE value | NOMAXVALUE}]
[{MINVALUE value | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{ORDER | NOORDER}]
[{CACHE value | NOCACHE}]
The naming convention is including
_seq
at the end of the name to make it easier to identify as a sequence.INCREMENT BY
: specifies the interval between two sequential values. The default value is1
. If the sequence is incremented by a positive value, the values generated by the sequence are in ascending order. However, values generated by the sequence are in descending order when a negative value are assigned to this option.START WITH
: specifies the starting value for the sequence. The default value is1
in a ascending order,-1
in a descending order.MAXVALUE
: establish a maximum of value for the sequence. If a nagative increment is used and you setMINVALUE
,MAXVALUE
must also be set. Typecally,MAXVALUE
is to set the same value as theSTART WITH
NOMAXVALUE
: it is default option if you do not specifyMAXVALUE
.-1
is the highest value for a descending sequence.10^27
is the highest value for a ascending sequence.
MINVALUE
: establish a minimum of value for the sequence. it does not make sense when the sequence increments by a positive value.NOMINVALUE
: it is default option if you do not specifyMINVALUE
.1
is the lowest value for a ascending sequence.-10^26
is the lowest value for a descending sequence.
CYCLE
: reissue values from sequences after reaching theMINVALUE
orMAXVALUE
option.NOCYCLE
: oracle server does not generate value after reachingMINVALUE
orMAXVALUE
option. And return an error message.ORDER
andNOORDER
: Used in application cluster environments.ORDER
option instructs oracle server to return sequence values in the same order in which requests are received.NOORDER
is the default option. However, there are no problem to generate unique value for each request.
CACHE
: Used to have oracle server generate a set of values ahead of time and store them in the memory.If
CACHE
is not specified, the default option ofCACHE 20
will be assigned.These generated values are lost if the system crashes or users do not use the values.
NOCACHE
: means each number is generated when the request is received.
CREATE SEQUENCE orders_order#_seq
INCREMENT BY 1
START WITH 1021
MAXVALUE 10000
NOCYCLE
NOCACHE;
-- Start with -1 in descending order
CREATE SEQUENCE negative_value_seq
INCREMENT BY -1;
-- must specify MAXVALUE option as same as START WITH while MINVALUE is assigned in a descending order.
CREATE SEQUENCE negative_value_seq
INCREMENT BY -1
START WITH 1
MAXVALUE 1
MINVALUE -100
NOCYCLE
CACHE 20;
-- MINVALUE is unnecessary in a ascending order.
CREATE SEQUENCE customers_customer#_seq
START WITH 1000
MAXVALUE 10000;
-- verify which sequences exist by querying `user_object` data dictionary.
SELECT object_name FROM user_objects WHERE object_type = 'SEQUENCE';
-- verify each setting for sequence options by query `user_sequences` data dictionary.
-- `LAST_NUMBER` column in the query result indicates the next value to be assigned in a sequence created with `NOCACHE`.
SELECT * FROM user_sequences WHERE sequence_name = UPPER('customers_customer#_seq');
Using Sequence Value
NEXTVAL
: Used to actually generate value from a sequence. In the other words, it calls the sequence object and requests the next value in a sequence.
CURRVAL
: Used to store the current value of the sequence so that you can reference it again.
SELECT orders_order#_seq.NEXTVAL FROM dual;
SELECT orders_order#_seq.CURRVAL FROM dual;
INSERT INTO orders (order#, customer#, orderdate, shipdate, shipstreet, shipcity, shipstate, shipzip)
VALUES (orders_order#_seq.NEXTVAL, 1010, '06-APR-09', NULL, '123 West Main', 'ATLATA', 'GA', 30418);
Setting Sequence Value
Oracle 12c allows a sequence value to be set as default value of a column.
The sequence must be created before being referenced in the default expression of a column.
If the sequence is dropped, the default expression referenceing the sequence must also be removed to avoid errors upon insert operations affecting the column.
If a value is provided in the insert operation, the default sequence value will not be used.
CREATE SEQUENCE test_defval_seq
INCREMENT BY 1
START WITH 100,
NOCACHE
NOCYCLE;
CREATE TABLE test_defval(
col1 NUMBER DEFAULT test_defval_seq.NEXTVAL,
col2 NUMBER
);
INSERT INTO test_defval(col1, col2) VALUES (DEFAULT, 350);
INSERT INTO test_defval(col2) VALUES (351);
INSERT INTO test_defval(col1, col2) VALUES (222, 352);
INSERT INTO test_defval(col1, col2) VALUES (NULL, 353);
Altering Sequence Definition
Any changes are applied to value generated after the modifications are made.
START WITH
can not be changed because the sequence would have to be dropped and re-created to make this change.The changes could not make the previously issued sequence value invalid. (For exmaple, you can not set the
MAXVALUE
to a number less than a number that had already been generated).
ALTER SEQUENCE sequencename
[INCREMENT BY value]
[{MAXVALUE value | NOMAXVALUE}]
[{MINVALUE value | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{ORDER | NOORDER}]
[{CACHE value | NOCACHE}]
ALTER SEQUENCE orders_order#_seq
INCREMENT BY 10;
Removing Sequence
When a sequence is dropped, it does not affect any value previously generated and stored in a database table.
DROP SEQUENCE sequencename;
DROP SEQUENCE orders_order#_seq;
Identity Column
An Identity Column
is particularly suited for use as primary key column values in which random or surrogate values are needed to serve as a unique id for each row.
CREATE TABLE test_ident(
col1 NUMBER GENERATED AS IDENTITY PRIMARY KEY,
col2 NUMBER
);
INSERT INTO test_ident(col2) VALUES (350);
INSERT INTO test_ident(col1, col2) VALUES (DEFAULT, 351);
only one
Identity Column
is allowed in one table.the
DEFAULT
clause can not be assigned to the same column inCREATE TABLE
orALTER TABLE
. (AnIdentity Column
automatically creates a sequence and default setting for the column).A
NOT NULL
constraint andUNIQUE
constraint are automatically applied to the column.An
Identity Column
must use theNUMBER
data type.A value can be assigned to insert into the column.
Last updated
Was this helpful?