Sequence oracle что это

Обновлено: 02.07.2024

Возможность, которой не удивишь нынче пользователей Oracle, DB2, PostgreSQL и множества других реляционных баз данных, наконец-то появилась и в MS SQL Server. На арене Sequence!

Sequence – генерирует последовательность чисел так же как и identity. Однако основным плюсом sequence является то, что последовательность не зависит от какой-либо конкретной таблицы и является объектом базы данных.

Рассмотрим пример скрипта написанного на SQL Server 2008. Создание простой таблицы с двумя колонками, одна из которых будет автоинкрементной.


Похожим образом создадим еще одну таблицу.

Как можно заметить из примеров, мы записали значения в таблицу при этом значение инкрементального поля автоматически и независимо от нас заполнилось. Мы не можем повторно использовать значение этого поля в другой таблице. Давайте посмотрим как можно выйти из этой ситуации с помощью Sequence.

Общий синтаксис для команды выглядит так:

Создадим последовательность чисел:

После выполнения указанного скрипта, в браузере объектов базы, в узле Sequences можно найти наш объект.


После того как объект создан, можно его использовать в создании и заполнении таблиц как показано ниже:

Если создать вторую таблицу в таком же духе, то можно снова использовать GenerateNumberSequence и получать сквозную нумерацию объектов.

Последовательность (Sequence) которую мы создали, можно посмотреть в системном каталоге sys.sequences.


Это не вся доступная информация по sequence, просто эти колонки нам понадобятся далее. Чтобы получить всю информацию замените имена колонок на звездочку. Про Is_Exhausted будет упомянуто позднее.

  • Int
  • Smallint
  • Tinyint
  • Bigint
  • Decimal
  • Numeric

Проверим на практике, что скажет SQL Server при задании начального числа вне допустимого диапазона. Начнем с левой границы.

An invalid value was specified for argument 'START WITH' for the given data type.

Что и ожидалось. Теперь нарушим правую границу.


Сервер сообщит нам об ошибке так:

The sequence object 'GenerateNumberSequence' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.

И если мы обратим внимание на колонку Is_Exhausted в каталоге sys.sequences, то увидим, что значение стало равно 1. Что говорит нам о невозможности дальнейшего использования данной последовательности.


При попытке создать таблицу с использованием такой последовательности, сервер выдаст ошибку:

The sequence object 'GenerateNumberSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Это можно трактовать как просьбу движка рестартовать указанную последовательность. Для этого необходимо воспользоваться конструкцией RESTART WITH.

Значение должно быть в пределах допустимого диапазона объявленного типа. Далее последовательность начнется с указанного значения, не со следующего.


А потом выполнить скрипт:

То результат будет таким:

Последовательность началась с заданного значения.

Получить минимальные и максимальные значения можно из каталога sys.sequences.

MIN и MAX значения

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

The start value for sequence object 'GenerateNumberSequence' must be between the minimum and maximum value of the sequence object.

Далее можем представить, что следующее значение в последовательности нарушает границу. В таком случае получим ошибку:

The sequence object 'GenerateNumberSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

  • Использовать служебные слова Restart или Restart With.
  • Использовать опцию CYCLE

Опция CYCLE

Данная опция зацикливает последовательность и, достигнув максимального значения, последовательность продолжается с минимального. Например:


После того как максимальное значение было достигнуто, результаты станут такими:

Для выборки использовался запрос:

Если внимательно посмотреть на вывод, то можно заметить, что записи были перепутаны. Если бы мы не использовали последовательности, то вывод был бы

Но из-за того, что вторая запись пересекла диапазон допустим значений, номер был сброшен на минимальное значение, заданное для последовательности (10). Если сейчас посмотреть в каталог sys.sequences, то будет видно, что текущее значение равняется 10.

В следующий раз, заполнение таблицы могло бы быть таким:

В этот момент Sequence проверит порядок в котором записи будут вставлены и так как “Violet” идет раньше “Tape” и текущий номер равен 10, записи будут вставлены как:

Следующее_значение =Текущее_значение +Сдвиг т.е. 10 +1 будет присвоено для “Violet”. Теперь значение Sequence = 11 и для второй записи значение будет 12 следуя то же самой формуле.

Опция NO CYCLE


Поведение такой опции уже рассматривалось в самом начале, и является значением по умолчанию при создании Sequence.

Sequence в сочетании с Over()

Можно использовать последовательность вместе с выражением Over для генерирования порядковых номеров как показано ниже:


Можно заметить, что записи были отсортированы и последовательность была применена верно к сохраненным данным. Это означает, что записи сначала сортируются и только потом применяется нумерация последовательности.

Ограничения использования Next Value для функций.

  • Проверкой ограничений (constraints)
  • Значениями по умолчанию
  • Вычисляемыми колонками
  • Представлениями (views)
  • Пользовательскими функциями
  • Пользовательскими функциями агрегации
  • Подзапросами
  • СТЕ (Common Table Expression)
  • Подтаблицами
  • Выражением TOP
  • Выражением Over
  • Выражением Output
  • Выражением On
  • Выражением Where
  • Выражением Group By
  • Выражением Having
  • Выражением Order By
  • Выражением Compute
  • Выражением Compute By

Функция sp_sequence_get_range

Если рассмотреть все использованные выше подходы к добавлению строк в таблицы используя NEXTVALUEFOR, то становится заметно, что это выражение присутствует в каждом уровне VALUES, что выглядит несколько утомительно. Вместо этого можно использовать функцию sp_sequence_get_range для получения необходимого диапазона значений, которые можно использовать впоследствии. Сейчас продемонстрирую как это можно осуществить.

Вот что будет в результате выполнения:


Здесь можно увидеть, что последовательность была увеличена до 1000 и пропущенные значения не были использованы нигде без нашего ведома. В данном случае мы их использовали для вставки значений.

Последовательность CREATE SEQUENCE – это объект базы данных, который генерирует целые числа в соответствии с правилами, установленными во время его создания. Для последовательности можно указывать как положительные, так и отрицательные целые числа. В системах баз данных последовательности применяют для самых разных целей, но в основном для автоматической генерации первичных ключей. Тем не менее к первичному ключу таблицы последовательность никак не привязана, так что в некотором смысле она является еще и объектом коллективного пользования. Если первичный ключ нужен лишь для обеспечения уникальности, а не для того, чтобы нести определенный смысл, последовательность является отличным средством.

Последовательность создается командой CREATE SEQUENCE.

CREATE SEQUENCE

CREATE SEQUENCE

Синтаксис команды CREATE SEQUENCE

Синтаксис команды CREATE SEQUENCE

Основные ключевые слова и параметры CREATE SEQUENCE:

Пример 1 CREATE SEQUENCE
Создание последовательности sequence_1.s Первое обращение к этой последовательности возвратит 1. Второе обращение возвратит 11. Каждое следующее обращение возвратит значение, на 10 большее предыдущего:

Пример 2 CREATE SEQUENCE
Создание последовательности sequence_2. Последовательность убывающая, циклическая, при достижении нуля последовательность вновь обращается к старшему числу. Такой последовательностью удобно пользоваться в тех программах, где до наступления некоторого события должен быть выполнен обратный отсчет:

CREATE SEQUENCE sequence_2
START WITH 20
INCREMENT BY –1
MAXVALUE 20
MINVALUE 0
CYCLE
ORDER
CACHE 2;

После создания последовательности к ней можно обращаться через псевдостолбцы CURRVAL (возвращает текущее значение последовательности) и NEXTVAL (выполняет приращение последовательности и возвращает ее следующее значение). Текущее и следующее значения последовательности пользователи базы данных получают, выполняя команду SELECT. Последовательности – не таблицы, а простые объекты, генерирующие целые числа с помощью виртуальных столбцов, поэтому нужна общедоступная таблица словаря данных DUAL, из которой будут извлекаться данные виртуальных столбцов.

Первое обращение к NEXTVAL возвращает начальное значение последовательности. Последующие обращения к NEXTVAL изменяют значение последовательности на приращение, которое было определено, и возвращают новое значение. Любое обращение к CURRVAL всегда возвращает текущее значение последовательности, а именно, то значение, которое было возвращено последним обращением к NEXTVAL. Прежде чем обращаться к CURRVAL в текущем сеансе работы, необходимо хотя бы один раз выполнить обращение к NEXTVAL.

В одном предложении SQL приращение последовательности может быть выполнено только один раз. Если предложение содержит несколько обращений к NEXTVAL для одной и той же последовательности, то ORACLE наращивает последовательность один раз, и возвращает одно и то же значение для всех вхождений NEXTVAL. Если предложение содержит обращения как к CURRVAL, так и к NEXTVAL, то ORACLE наращивает последовательность и возвращает одно и то же значение как для CURRVAL, так и для NEXTVAL, независимо от того, в каком порядке они встречаются в предложении.

К одной и той же последовательности могут обращаться одновременно несколько пользователей, без какого-либо ожидания или блокировки:

< имя последовательности >.CURRVAL

< имя последовательности >.NEXTVAL

Чтобы обратиться к текущему или следующему значению последовательности, принадлежащей схеме другого пользователя, пользователь должен иметь либо объектную привилегию SELECT по этой последовательности, либо системную привилегию SELECT ANY SEQUENCE, и должен дополнительно квалифицировать эту последовательность именем содержащей ее схемы:

<имя схемы>.<имя последовательности >.CURRVAL

<имя схемы>.<имя последовательности >.NEXTVAL

Значения CURRVAL и NEXTVAL используются в следующих местах:

  • в списке SELECT предложения SELECT
  • в фразе VALUES предложения INSERT
  • в фразе SET предложения UPDATE.

Нельзя использовать значения CURRVAL и NEXTVAL в следующих местах:

  • в подзапросе
  • в предложении SELECT с оператором DISTINCT
  • в предложении SELECT с фразой GROUP BY или ORDER BY
  • в предложении SELECT, объединенном с другим предложением SELECT оператором множеств UNION
  • в фразе WHERE предложения SELECT
  • в умалчиваемом (DEFAULT) значении столбца в предложении CREATE TABLE или ALTER TABLE
  • в условии ограничения CHECK.

SELECT SEQUENCE. Пример 3.
Действие циклической последовательности sequence_2 при достижении ею значения MINVALUE:

CREATE SEQUENCE. Пример 4.
В следующем примере SEQUENCE после ссылки на столбец NEXVAL значение CURRVAL обновляется так, чтобы соответствовать значению NEXVAL, а предыдущее значение CURRVAL теряется:

CREATE SEQUENCE. Пример 5.
Ссылка на последовательности при изменении данных:

ALTER SEQUENCE. Пример 6.
Любой параметр последовательности можно изменить командой ALTER SEQUENCE. Новое значение вступает в силу немедленно. Все параметры последовательности, не указанные в команде ALTER SEQUENCE, остаются без изменений:

ALTER SEQUENCE sequence_2

INCREMENT BY –4;

DROP SEQUENCE. Пример 7.
Удаление последовательности SEQUENCE:

DROP SEQUENCE sequence_2

В Oracle/PLSQL, вы можете создать автонумерацию с помощью последовательности. Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда вам нужно создать уникальный номер в качестве первичного ключа.

CREATE SEQUENCE

Синтаксис

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

sequence_name имя последовательности, которую вы хотите создать.

Пример

Этот код создаст объект последовательность под названием supplier_seq. Первый номер последовательности 1, каждый последующий номер будет увеличиваться на 1 (т.е.. 2,3,4, . ). Это будет кэшировать до 20 значений для производительности.

Если вы опустите параметр MAXVALUE , ваша последовательность по умолчанию до:

Таким образом, вы можете упростить CREATE SEQUENCE. Написав следующее:

Теперь, когда вы создали объект последовательности для автонумерации поля счетчика, мы рассмотрим, как получить значение из этого объекта последовательности. Чтобы получить следующее значение, вам нужно использовать NEXTVAL .
Например:

Это позволит извлечь следующее значение из последовательности supplier_seq . Предложение NEXTVAL нужно использовать в SQL запросе. Например:

Этот isert запрос будет вставлять новую запись в таблицу suppliers (поставщики). Полю Supplier_id будет присвоен следующий номер из последовательности supplier_seq . Поле supplier_name будет иметь значение 'Kraft Foods'.

DROP SEQUENCE

После того как вы создали последовательность в Oracle, вам можете понадобиться удалить её из базы данных.

Синтаксис:

sequence_name имя последовательности, которую вы хотите удалить.

Пример

Рассмотрим на примере, как удалить последовательность в Oracle.

Этот пример удалит последовательность supplier_seq .

ЧАСТО ЗАДАВАЕМЫЕ ВОПРОСЫ

Вопрос: При создании последовательности, что означают опции cache и nocache ? Например, можно создать последовательность с опцией cache 20 следующим образом:

Или вы могли бы создать такую же последовательность, но с опцией nocache :

Ответ: Что касается последовательности, опция cache определяет, сколько значений последовательности будут сохранены в памяти для быстрого доступа.

Недостатком создания последовательности с cache, что если происходит отказ системы, все кэшированные значения последовательности, которые не были использованы, будут утеряны. Это приведет к разрывам в значениях, назначенной последовательности. Когда в система восстановится, Oracle будет кэшировать новые номера, с того места, где была прервана последовательность, игнорируя утерянные значения последовательности.

Примечание: Для восстановления утраченных значений последовательности, вы всегда можете выполнить команду ALTER SEQUENCE для сброса счетчика на правильное значение.
nocache означает, что ни одно из значений последовательности не хранятся в памяти. Эта опция может понизить производительность, однако, вы не должны столкнуться с разрывами в значениях, назначенной последовательности.

Вопрос: Как установить значение lastvalue в последовательность Oracle?

Ответ: Вы можете изменить lastvalue для последовательности Oracle, выполнив команду ALTER в последовательности.

Например, если последнее значение используемой последовательности Oracle был 100, и вы хотите, чтобы следующее значение было 225. Вы должны выполнить следующие команды.

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

Синтаксис CREATE SEQUENCE

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

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

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

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

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

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 :

Атрибуты 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 27 , если последовательность возрастает, или -1, если последовательность убывает. По умолчанию принимается NOMAXVALUE.

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

NOMINVALUE

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

В СУБД PostgreSQL при включении данного параметры в скрипт необходимо использовать следующий синтаксис : NO MINVALUE. Значение по умолчанию равно -2 63 -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, который не гарантирует, что номера последовательности генерируются в порядке запросов

Читайте также: