Open topic with navigation
CREATE SEQUENCE — define a new sequence generator
CREATE SEQUENCE [
sequence_option can be one of the following:
CREATE SEQUENCE creates a new sequence number generator. The sequence database object is owned by the user issuing the
CREATE SEQUENCE command. The user can then issue the
GRANT command to allow other users
SELECT access to the sequence. See the GRANT command and also, Example 3: Successfully delegate an INSERT permission on a table with a sequence to a non-admin user.
Note: You need to GRANT SELECT rights to a table's sequence(s) in order for a non-admin user to be able to insert into that table.
The sequence number generator assigns bigint (big integer) values, by default, starting with the value one (1). The
sequence_option can be used to provide a different starting integer value for the sequence. This starting integer value is referred to as the
floor value for the sequence. A sequence will always generate a unique integer value that is greater than or equal to the floor value for the sequence. However, these integer values are not necessarily generated in sequential, ascending order. See below how sequences are managed by Transaction Engine(s).
The sequence value is incremented each time a unique integer value is generated by the sequence. This can occur in one of two ways:
NEXT VALUENuoDB SQL expression can be used to select from a sequence.
The sequence number generator will reserve a range of integer values for each TE that requests integer values from a sequence. This range of integer values is reserved, by default, in blocks of one hundred (100) values. The
sequence_option can be used to change this block size. Once all of the integer values that have been reserved for that TE are assigned by the sequence, the next range of integer values is reserved for that TE. For example, suppose TE1 and TE2 both use the same sequence. The sequence reserves integer values 1-100 for TE1 and reserves integer values 101-200 for TE2. After the sequence assigns all of the integer values reserved for TE1, the next block that the sequence number generator will reserve for TE1 might contain integer values 201-300. If a client connects to the database via TE1 and selects the
NEXT VALUE of the sequence, the integer value 1 is assigned. If another connection to the database is made via TE2 and the
NEXT VALUE of the sequence is selected, the integer value 101 would be assigned, and so forth. As each TE returns unique integer values, the
floor value for that sequence for that TE is incremented.
The sequence number generator maintains an integer value that is greater than any integer value that has been reserved for all TEs. This integer value is referred to as the
ceiling value for the sequence. The ceiling value for a sequence is always incremented by one thousand (1000). This incremental size cannot be changed. Therefore, the initial
ceiling value of a new sequence, by default, is 1000. Once a value that is equal to or greater than 1000 has been reserved for a TE, the
ceiling value for the sequence is incremented to 2000. The sequence
ceiling value will never decrease. Accessing a sequence on a new TE, possibly after restarting the database, will generate sequence values that are greater than or equal to the sequence ceiling value.
Name of the schema in which the sequence will be created. The default is the current schema as defined by the
USE statement. If
schema is provided, and the schema does not exist, it will be created. Sequences cannot be created in the
SYSTEM schema. This schema is reserved for database metadata.
Name (optionally schema-qualified) of the sequence. The
seqname must be distinct from the name of any other sequence object in the schema in which the
SEQUENCE is created.
The optional clause
START WITH allows the sequence to begin at any value. The default starting
value is one (1). The starting
value can be a number, positive or negative. The starting
value can also be an expression that returns an integer value. Some examples of an expression include, but are not limited to:
SELECT). This must be enclosed by parentheses. This includes all valid SQL
SELECTstatements, including statements using aggregate functions,
LIMIT, etc. The SQL
SELECTstatement must return a number value.
QUANTUM SIZE clause allows you to specify how many unique integer values are included in a block of values that will be reserved for a TE. The default
size is 100.
CREATE SEQUENCE seq_hockey_fans START WITH 111; CREATE TABLE hockey_fans (id INTEGER GENERATED ALWAYS AS IDENTITY(seq_hockey_fans), name STRING); INSERT INTO hockey_fans (name) VALUES ('me'), ('you'); SELECT * FROM hockey_fans;ID NAME --- ----- 111 me 112 you
SELECT NEXT VALUE FOR seq_hockey_fans FROM DUAL;SEQ_HOCKEY_FANS ---------------- 113
INSERT INTO hockey_fans (name) VALUES ('us'); SELECT * FROM hockey_fans;ID NAME --- ----- 111 me 112 you 114 us
DROP SEQUENCE IF EXISTS seq_hockey_next; SELECT MAX(id) FROM hockey.hockey;MAX ---- 24
CREATE SEQUENCE seq_hockey_next START WITH (SELECT MAX(id) +1 FROM hockey.hockey); SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;SEQ_HOCKEY_NEXT ---------------- 25
DROP SEQUENCE IF EXISTS seq_hockey_next; CREATE SEQUENCE seq_hockey_next START WITH 25+1; SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;SEQ_HOCKEY_NEXT ---------------- 26
DROP SEQUENCE IF EXISTS seq_hockey_next; SET DELIMITER @ CREATE FUNCTION fnc_seq_hockey_next RETURNS INTEGER AS RETURN (SELECT MAX(ID) +1 FROM hockey.hockey); END_FUNCTION; @ SET DELIMITER ; CREATE SEQUENCE seq_hockey_next START WITH fnc_seq_hockey_next(); SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;SEQ_HOCKEY_NEXT ---------------- 25
GRANT INSERT on hockey.hockey_fans to app_user; GRANT SELECT on hockey. seq_hockey_fans to app_user;