Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE SEQUENCE

The CREATE SEQUENCE statement creates a new sequence number generator that can subsequently be used by multiple users to generate unique integers. Use the CREATE SEQUENCE statement to define the initial value of the sequence, define the increment value, the maximum or minimum value and determine if the sequence continues to generate numbers after the minimum or maximum is reached.

Required privilege

CREATE SEQUENCE (if owner) or CREATE ANY SEQUENCE (if not owner)

SQL syntax

CREATE SEQUENCE [Owner.]SequenceName
       [INCREMENT BY IncrementValue]
       [MINVALUE MinimumValue]
       [MAXVALUE MaximumValue]
       [CYCLE]
       [CACHE CacheValue]
       [START WITH StartValue]

Parameters

The CREATE SEQUENCE statement has the parameters:

Parameter Description
SEQUENCE [Owner.]SequenceName Name of the sequence number generator.
INCREMENT BY IncrementValue The incremental value between consecutive numbers. This value can be either a positive or negative integer. It cannot be 0. If the value is positive, it is an ascending sequence. If the value is negative, it is descending. The default value is 1. In a descending sequence, the range starts from MAXVALUE to MINVALUE, and vice versa for ascending sequence.
MINVALUE MinimumValue Specifies the minimum value for the sequence. The default minimum value is 1.
MAXVALUE MaximumValue The largest possible value for an ascending sequence, or the starting value for a descending sequence. The default maximum value is (263) -1, which is the maximum of BIGINT.
CYCLE Indicates that the sequence number generator continues to generate numbers after it reaches the maximum or minimum value. By default, sequences do not cycle. Once the number reaches the maximum value in the ascending sequence, the sequence wraps around and generates numbers from its minimum value. For a descending sequence, when the minimum value is reached, the sequence number wraps around, beginning from the maximum value. If CYCLE is not specified, the sequence number generator stops generating numbers when the maximum/minimum is reached and TimesTen returns an error.
CACHE CacheValue CACHE indicates the range of numbers that are cached each time. When a restart occurs, unused cached numbers are lost. If you specify a CacheValue of 1, then each use of the sequence results in an update to the database. Larger cache values result in fewer changes to the database and less overhead. The default is 20.
START WITH StartValue Specifies the first sequence number to be generated. Use this clause to start an ascending sequence at a value that is greater than the minimum value or to start a descending sequence at a value less than the maximum. The StartValue must be greater or equal MinimumValue and StartValue must be less than or equal to MaximumValue.

Description

Incrementing SEQUENCE values with CURRVAL and NEXTVAL

To refer to the SEQUENCE values in a SQL statement, use CURRVAL and NEXTVAL.

Note:

Sequences with the CYCLE attribute cannot be replicated.

Examples

Create a sequence.

CREATE SEQUENCE mysequence INCREMENT BY 1 MINVALUE 2 
       MAXVALUE 1000;

This example assumes that tab1 has 1 row in the table and that CYCLE is used:

CREATE SEQUENCE s1 MINVALUE 2 MAXVALUE 4 CYCLE;
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 3; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 4; */

After the maximum value is reached, the cycle starts from the minimum value for an ascending sequence.

SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */

To create a sequence and generate a sequence number:

CREATE SEQUENCE seq INCREMENT BY 1;
INSERT INTO student VALUES (seq.NEXTVAL, 'Sally');

To use a sequence in an UPDATE SET clause:

UPDATE student SET studentno = seq.NEXTVAL WHERE name = 'Sally';

To use a sequence in a query:

SELECT seq.CURRVAL FROM student;

See also

DROP SEQUENCE