Последовательность SEQUENCE это объект базы данных, предназначенный для генерации целых чисел в соответствии с правилами, установленными при его создании. Генерируемые числа могут быть как положительные, так и отрицательные. Как правило, SEQUENCE используют для автоматической генерации значений первичных ключей. Последовательность является объектом базы данных, и генерируемое ею значения можно использовать для различных таблиц.

Синтаксис CREATE SEQUENCE

В общем виде синтаксис создания последовательности SEQUENCE для СУБД Oracle можно представить в следующем виде :

CREATE SEQUENCE [SCHEMA.]SEQUENCE_NAME
   [START WITH start_num]
   [INCREMENT BY increment_num]
   [ { MAXVALUE maximum_num | NOMAXVALUE } ]
   [ { MINVALUE minimum_num | NOMINVALUE } ]
   [ { CYCLE | NOCYCLE } ]
   [ { CACHE cache_num | NOCACHE } ]
   [ { ORDER | NOORDER } ];

Несмотря на однозначное назначение SEQUENCE в различных СУБД имеются определенные различия, которые и будут рассмотрены в данной статье.

Тип генерируемого SEQUENCE значения

В Oracle для последовательности установлено максимальное значение равное 10²⁷, минимальное значение соответственно -10²⁶.

В СУБД PostgreSQL при генерации значения последовательностью используется тип bigint, определяемое 8-байтным числом в диапазоне от -9223372036854775808 до 9223372036854775807. В некоторых старых версиях поддерживается значение в диапазоне от -2147483648 до +2147483647.

В MS SQL тип генерируемого значения можно определить при помощи оператора [ built_in_integer_type | user-defined_integer_type]. Если тип данных не указан, то по умолчанию используется тип bigint. Синтаксис выражения CREATE SEQUENCE для СУБД MS SQL :

CREATE SEQUENCE [SCHEMA.]SEQUENCE_NAME
   [AS [ built_in_integer_type | user-defined_integer_type ]]
   [START WITH start_num]
   ...

SEQUENCE СУБД MS SQL может быть определена с определенным типом. Допускаются следующие типы :

tinyint — диапазон от 0 до 255;
smallint — диапазон от -32 768 до 32 767;
int — диапазон от -2 147 483 648 до 2 147 483 647.
bigint — диапазон от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807
decimal и numeric с масштабом 0.
Любой определяемый пользователем тип данных (псевдоним типа), основанный на одном из допустимых типов.

Для SEQUENCE СУБД Apache Derby, аналогично MS SQL, может быть определен тип. Допускаются типы smallint, int, bigint. Синтаксис генератора последовательности SEQUENCE СУБД Apache Derby :

CREATE SEQUENCE [SCHEMA.]SEQUENCE_NAME
   [AS AS dataType ]
   [START WITH start_num]
   ...

Атрибуты SEQUENCE

SCHEMA

SCHEMA определяет схему, в которой создается последовательность. Если SCHEMA опущена, то :

Oracle создает последовательность в схеме пользователя.
MSSQL и PostgreSQL создают последовательность в схеме, к которой подключено приложение. Для MS SQL Можно использовать SQL оператор "use" для подключения к определенной схеме.

SEQUENCE_NAME

SEQUENCE_NAME определяет имя создаваемой последовательности.

START WITH

START WITH start_num — это первое значение, возвращаемое объектом последовательности. Значение должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением для нового объекта последовательности служит минимальное значение для объекта возрастающей последовательности и максимальное — для объекта убывающей.

INCREMENT BY

INCREMENT BY increment_num - приращение генерируемого значения при каждом обращении к последовательности. По умолчанию значение равно 1, если не указано явно. Для возрастающих последовательностей приращение положительное, для убывающих — отрицательное. Приращение не может быть равно 0. Для PostgreSQL можно использовать только INCREMENT.

MAXVALUE maximum_num

MAXVALUE — максимальное значение maximum_num, создаваемое последовательностью. Если оно не указано, то применяется значение по умолчанию NOMAXVALUE.

MINVALUE minimum_num

MINVALUE — минимальное значение minimum_num, создаваемое последовательностью. Если оно не указано, то применяется значение по умолчанию NOMINVALUE.

NOMAXVALUE

NOMAXVALUE в Oracle определяет максимальное значение равное 10²⁷, если последовательность возрастает, или -1, если последовательность убывает. По умолчанию принимается NOMAXVALUE.

В СУБД PostgreSQL при включении данного параметры в скрипт необходимо использовать следующий синтаксис : NO MAXVALUE. Значение по умолчанию равно 2⁶³-1 или -1 для возрастающей или убывающей последовательности соответственно.

NOMINVALUE

NOMINVALUE в Oracle определяет минимальное значение равное 1, если последовательность возрастает, или -10²⁶, если последовательность убывает.

В СУБД PostgreSQL при включении данного параметры в скрипт необходимо использовать следующий синтаксис : NO MINVALUE. Значение по умолчанию равно -2⁶³-1 или 1 для убывающей или возрастающей последовательности соответственно.

CYCLE

Применение в скрипте CYCLE позволяет последовательности повторно использовать созданные значения при достижении MAXVALUE или MINVALUE. Т.е. последовательность будет повторно гененировать значения с начальной позиции (со START'a). По умолчанию используется значение NOCYCLE. Указывать CYCLE вместе с NOMAXVALUE или NOMINVALUE нельзя.

NOCYCLE

NOCYCLE указывает, что последовательность не сможет генерировать значения после достижения максимума или минимума.

CACHE cache_num

Оператор CACHE в скрипте позволяет создавать заранее и поддерживать в памяти заданное количество значений последовательности для быстрого доступа.

В СУБД PostgreSQL минимальное значение равно 1 и соответствует значению NOCACHE.

В СУБД Oracle минимальное значение равно 2.

ORDER

Данный оператор используется только в СУБД Oracle. Он гарантирует, что номера последовательности генерируются в порядке запросов. Если упорядочение нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию NOORDER, который не гарантирует, что номера последовательности генерируются в порядке запросов

Применение последовательности

Пример Oracle SEQUENCE :

-- создание последовательности в Oracle
CREATE SEQUENCE seq_orders
  START WITH 10
  INCREMENT BY 2
  MAXVALUE 200000
  MINVALUE 5
  CYCLE
  ORDER
  CACHE 2;

-- генерирование значения
select seq_orders.nextval FROM dual;

Пример MS SQL SEQUENCE :

-- создание последовательности в MS SQL
CREATE SEQUENCE test.seq_users
    AS decimal(3,0) 
    START WITH 25
    INCREMENT BY 5
    MINVALUE 50
    MAXVALUE 2000
    CYCLE
    CACHE 3;

-- генерирование значения : используется NEXT VALUE FOR
SELECT NEXT VALUE FOR test.seq_users;

Пример PostgreSQL SEQUENCE :

-- создание последовательности в PostgreSQL
CREATE SEQUENCE seq_users 
  START 20;

-- генерирование значения
select nextval('seq_users');

Пример Apache Derby SEQUENCE :

-- создание последовательности
CREATE SEQUENCE seq_orders
  AS BIGINT
  START WITH 30;

-- генерирование значения
UPDATE orders
  SET id = NEXT VALUE FOR order_id
    WHERE num like '%20151110/12%';

Удаление последовательности, DROP SEQUENCE

Синтаксис удаления последовательности :

DROP SEQUENCE [SCHEMA.]SEQUENCE_NAME;