Creates a sequence. A sequence is a database object from
which multiple users can generate unique integers. You can use
sequences to automatically generate primary key values.
1 – Environment
You can use the CREATE SEQUENCE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
2 – Format
CREATE SEQUENCE <sequence-name> ------------------+
+--------------------<----------------------------+
+---+---------------->-------------------------+--+
+---> STORED NAME IS ----> <stored-name> --+ |
+--------------------<----------------------------+
+-+------------------>-------------------------+-->
+-+-+---> sequence-attributes --------+-+----+
| +---> START WITH <numeric-value> -+ |
+------------------<------------------+
sequence-attributes =
--+--> INCREMENT BY <numeric-value> ---------+->
+--> sequence-range -----------------------+
+--> CYCLE --------------------------------+
+--> NOCYCLE ------------------------------+
+--> CACHE <numeric-value> ----------------+
+--> NOCACHE ------------------------------+
+--> ORDER --------------------------------+
+--> NOORDER ------------------------------+
+--> RANDOMIZE ----------------------------+
+--> NORANDOMIZE --------------------------+
+--> RESTART WITH -------------------------+
+--> WAIT ---------------------------------+
+--> NOWAIT -------------------------------+
+--> DEFAULT WAIT -------------------------+
+--> COMMENT -+-------+--+-> '<string>' -+-+
+-> IS -+ +------ / <-----+
sequence-range =
-+-> MINVALUE -+-+--> <numeric-value> -+--+-->
+-> MAXVALUE -+ +--> TINYINT ----+ |
| +--> SMALLINT ---+ |
| +--> INTEGER ----+ |
| +--> BIGINT -----+ |
+-> NOMINVALUE --------------------------+
+-> NOMAXVALUE --------------------------+
3 – Arguments
3.1 – CACHE
Syntax options:
CACHE numeric-value | NOCACHE
The CACHE clause specifies how many values of the sequence Oracle
Rdb should preallocate and keep in memory for faster access. The
numeric value must be between 2 and 2147483647.
You cannot cache more values than will fit in a given cycle of
sequence numbers; thus, the maximum value allowed for the CACHE
clause must be less than the value resulting from the following
formula:
(MAXVALUE-MINVALUE)/ABS(INCREMENT)
The SET FLAGS option SEQ_CACHE can be used to override the
setting of CACHE at runtime. See the SET_FLAGS statement for
more details.
A cache for a given sequence is populated at the first request
for a number from that sequence, and whenever a value is
requested when the cache is empty. If a system failure occurs,
or when the cache is released any unfetched values will be
discarded. The maximum number of lost values is equal to the
current cache size. This may be the value specified by CACHE or
by the SET FLAGS SEQ_CACHE option.
The NOCACHE clause specifies that values will be allocated one
at a time. This will require more I/O to the Rdb root file than
using a CACHE value.
By default, Oracle Rdb caches 20 sequence values.
3.2 – COMMENT IS 'string '
Adds a comment about the sequence. SQL displays the text of the
comment when it executes a SHOW SEQUENCE statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
3.3 – CYCLE
Syntax options:
CYCLE | NOCYCLE
The CYCLE clause specifies that the sequence is to continue
generating values after reaching either the MINVALUE or MAXVALUE.
After an ascending sequence reaches the MAXVALUE, the sequence
starts again from its MINVALUE. After a descending sequence
reaches its MINVALUE, the sequence starts again at its MAXVALUE.
The NOCYCLE clause specifies that the sequence should not
continue generating values after reaching either its minimum
or maximum value. An error is generated if an attempt is made to
increment the sequence beyond its limits. The NOCYCLE clause is
the default.
3.4 – INCREMENT BY numeric-value
Specifies the size of the increment and the direction (ascending
or descending) of the sequence. This numeric value must be in the
range -2147483648 through 2147483647, excluding 0. The absolute
value must be less than the difference of MAXVALUE and MINVALUE.
A negative value specifies a descending sequence; a positive
value specifies an ascending sequence. By default, the numeric
value is 1.
3.5 – MAXVALUE
Syntax options:
MAXVALUE numeric-value | NOMAXVALUE
The MAXVALUE clause specifies the maximum signed quadword
(BIGINT) value that the sequence can generate. The numeric value
must be between -9223372036854775808 and 9223372036854775808. The
MAXVALUE must be equal to or greater than the value specified for
the START WITH clause and greater than the value specified with
the MINVALUE clause. The NOMAXVALUE clause specifies that the
maximum value for an ascending sequence is 9223372036854775808
(plus the cache size) and -1 for a descending sequence.
The NOMAXVALUE clause is the default.
3.6 – MAXVALUE integer
Syntax options:
MAXVALUE TINYINT
MAXVALUE SMALLINT
MAXVALUE INTEGER
MAXVALUE BIGINT
SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
to follow MAXVALUE instead of a numeric value. This allows easy
range setting for sequences used with these data types. The value
supplied will be the largest positive value that can be assigned
to this data type.
3.7 – MINVALUE
Syntax options:
MINVALUE numeric-value | NOMINVALUE
The MINVALUE clause specifies the minimum signed quadword
(BIGINT) value that the sequence can generate. The numeric
value must be equal to or greater than -9223372036854775808. The
MINVALUE must be less than or equal to the value specified with
the START WITH clause and less than the value specified with the
MAXVALUE clause. The NOMINVALUE clause specifies that the minimum
value for an ascending sequence is 1, and -9223372036854775808
(plus the cache size) for a descending sequence.
The NOMINVALUE clause is the default.
3.8 – MINVALUE integer-value
Syntax options:
MINVALUE TINYINT
MINVALUE SMALLINT
MINVALUE INTEGER
MINVALUE BIGINT
SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
to follow MINVALUE instead of a numeric value. This allows easy
range setting for sequences used with these data types. The value
supplied will be the smallest negative value that can be assigned
to this data type.
3.9 – ORDER
Syntax options:
ORDER | NOORDER
The ORDER clause specifies that sequence numbers are guaranteed
to be assigned in order for each requesting process, thus
maintaining a strict history of requests. The NOORDER clause
specifies that sequence numbers are not guaranteed to be
generated in order of request.
The NOORDER clause is the default.
3.10 – RANDOMIZE
Syntax options:
RANDOMIZE | NORANDOMIZE
The RANDOMIZE clause specifies that the sequence numbers are to
be returned with a random value in the most significant bytes
of the BIGINT value. This allows unique values to be generated
that have a random distribution. When you specify the NORANDOMIZE
clause, sequence numbers are close in value to others created at
the same time.
The advantage of the RANDOMIZE clause is that updates to columns
of a sorted index to which these values are written occur in
different locations in the index structure. This may improve
concurrent access for large indexes as leaf nodes in different
parts of the index can be updated independently. In contrast,
the sequence numbers generated when you specify the NORANDOMIZE
clause (which are likely to be close in numeric value to other
sequences) result in index updates that occur in the same or
nearby index nodes, which may lead to contention in one part of
the sorted index.
The full range of values in the BIGINT value returned for the
sequence are used; therefore, the NOMAXVALUE and NOMINVALUE
clauses must be specified (or defaulted to) for the sequence
definition. The most significant bits of the BIGINT value
are set to a randomly generated positive value. A generated
distinct value is returned in the least significant 32 bits so
that uniqueness is guaranteed. If you also specify the CYCLE
clause, then only the least significant 32 bits are cycled. When
a query is performed on the column RDB$NEXT_SEQUENCE_VALUE in
the RDB$SEQUENCES table, only the generated value of the least
significant bits is returned, because the most significant bits
are not assigned until the NEXTVAL pseudocolumn is referenced.
If you specify RANDOMIZE, you cannot also specify ORDER,
MAXVALUE, or MINVALUE. The NORANDOMIZE clause is the default.
3.11 – sequence-name
The name of the sequence that you want to create. Use a name that
is unique among all sequence, synonym, table, and view names in
the database, or in the schema if you are using a multischema
database. Use any valid SQL name.
3.12 – START WITH numeric-value
Specifies the initial numeric value to be used for the sequence.
This value must be in the range specified by (or defaulted to)
the other sequence attribute clauses. Valid values are in the
range -9223373036854775808 to 9223372036854775807.
If omitted, the START WITH value defaults to the value of
MINVALUE for ascending sequences and MAXVALUE for descending
sequences.
3.13 – STORED NAME IS stored-name
Specifies a name that Oracle Rdb uses to access a sequence
created in a multischema database. The stored name allows you to
access multischema definitions using interfaces, such as Oracle
RMU, that do not recognize multiple schemas in one database. You
cannot specify a stored name for a sequence in a database that
does not allow multiple schemas.
3.14 – WAIT
Syntax options:
WAIT | NOWAIT | DEFAULT WAIT
Specifies what wait state is used when a reference to NEXTVAL
is used. A reference to NEXTVAL for a sequence may require
synchronization with other users of the sequence. When you
specify DEFAULT WAIT, the wait state (WAIT or NOWAIT) of the
current transaction is used. This may mean that no waiting is
performed during a NOWAIT transaction.
If you specify WAIT (the default) for the sequence, then
regardless of the wait state set for the current transaction, all
synchronization waits for the next value. This is the recommended
setting if the application uses NOWAIT transactions. The current
WAIT timeout interval defined for the transaction or database is
used.
If you specify NOWAIT for the sequence, then regardless of the
current transaction setting, all synchronization does not wait
for the next value.
4 – Examples
Example 1: Creating a Sequence
SQL> -- This example creates a new sequence using the default
SQL> -- values for NOMINVALUE, NOMAXVALUE, INCREMENT BY 1, NOCYCLE,
SQL> -- and CACHE 20. The START WITH value is set to 147.
SQL> -- Allyn Stuart will be assigned an EMPLOYEE_ID value of 147.
SQL> -- Nick Jones will be assigned an EMPLOYEE_ID of 148.
SQL> --
SQL> CREATE SEQUENCE EMPID START WITH 00147;
SQL> -- Use NEXTVAL to fetch a sequence number for the primary key column.
SQL> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME)
cont> VALUES (EMPID.NEXTVAL, 'STUART', 'ALLYN')
cont> RETURNING EMPLOYEE_ID;
EMPLOYEE_ID
147
1 row inserted
SQL> -- Use CURRVAL to reuse the EMPLOYEE_ID value for the foreign key columns
SQL> -- in the associated tables.
SQL> INSERT INTO SALARY_HISTORY
cont> (EMPLOYEE_ID, SALARY_AMOUNT,SALARY_START, SALARY_END)
cont> VALUES (EMPID.CURRVAL, 35000, '6-FEB-1998', NULL)
cont> RETURNING EMPLOYEE_ID;
EMPLOYEE_ID
147
1 row inserted
SQL> INSERT INTO JOB_HISTORY
cont> (EMPLOYEE_ID, DEPARTMENT_CODE, JOB_START, JOB_END)
cont> VALUES (EMPID.CURRVAL, 'ENGR', '6-FEB-1998', NULL)
cont> RETURNING EMPLOYEE_ID;
EMPLOYEE_ID
147
1 row inserted
SQL> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME)
cont> VALUES (EMPID.NEXTVAL, 'JONES ', 'NICK ')
cont> RETURNING EMPLOYEE_ID;
EMPLOYEE_ID
148
1 row inserted