Что такое sequence в sql
Перейти к содержимому

Что такое sequence в sql

  • автор:

CREATE SEQUENCE

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

CREATE SEQUENCE

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

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

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

  • schema —схема, в которой создается последовательность. Если schema опущена, Oracle создает последовательность в схеме пользователя.
  • sequence — имя создаваемой последовательности
  • startwith позволяет создателю последовательности указать первое генерируемое ею значение. После создания последовательность генерирует указанное в start with значение при первой ссылке на ее виртуальный столбец NEXTVAL
  • increment by n — определяет приращение последовательности при каждой ссылке на виртуальный столбец NEXVAL. Если значение не указано явно, по умолчанию устанавливается 1. Для возрастающих последовательностей устанавливается положительное n, для убывающих, или последовательностей с обратным отсчетом — отрицательное
  • minvalue — определяет минимальное значение, создаваемое последовательностью. Если оно не указано, Oracle применяет значение по умолчанию NOMINVALUE
  • nominvalue — указывает, что минимальное значение равно 1, если последовательность возрастает, или -10 26 , если последовательность убывает
  • maxvalue — определяет максимальное значение, создаваемое последовательностью. Если оно не указано, Oracle применяет значение по умолчанию NOMAXVALUE
  • nomaxvalue — указывает, что максимальное значение равно 10 27 , если последовательность возрастает, или -1, если последовательность убывает. По умолчанию принимается NOMAXVALUE
  • cycle — позволяет последовательности повторно использовать созданные значения при достижении MAXVALUE или MINVALUE. Т.е. последовательность будет продолжать генерировать значения после достижения своего максимума или минимума. Возрастающая последовательность после достижения своего максимума генерирует свой минимум. Убывающая последовательность после достижения своего минимума генерирует свой максимум. Если циклический режим нежелателен или не установлен явным образом, Oracle применяет значение по умолчанию – NOCYCLE. Указывать CYCLE вместе с NOMAXVALUE или NOMINVALUE нельзя. Если нужна циклическая последовательность, необходимо указать MAXVALUE для возрастающей последовательности или MINVALUE – для убывающей
  • nocycle — указывает, что последовательность не может продолжать генерировать значения после достижения своего максимума или минимума
  • cachen — указывает, сколько значений последовательности ORACLE распределяет заранее и поддерживает в памяти для быстрого доступа. Минимальное значение этого параметра равно 2. Для циклических последовательностей это значение должно быть меньше, чем количество значений в цикле. Если кэширование нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию – 20 значений.
  • order — гарантирует, что номера последовательности генерируются в порядке запросов. Эта опция может использоваться, к примеру, когда номера последовательности предстают в качестве отметок времени. Гарантирование порядка обычно не существенно для тех последовательностей, которые используются для генерации первичных ключей. Если упорядочение нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию NOORDER
  • noorder — не гарантирует, что номера последовательности генерируются в порядке запросов

Пример 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, независимо от того, в каком порядке они встречаются в предложении. К одной и той же последовательности могут обращаться одновременно несколько пользователей, без какого-либо ожидания или блокировки:

Чтобы обратиться к текущему или следующему значению последовательности, принадлежащей схеме другого пользователя, пользователь должен иметь либо объектную привилегию 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:

SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 20 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 19 ….. SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 1 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 0 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 20

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

SQL> SELECT sequence_2.CURRVAL FROM dual; CURRVAL————— 20 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 19 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 18 SQL> SELECT sequence_2.NEXTVAL FROM dual; NEXTVAL————— 17 SQL> SELECT sequence_2.CURRVAL FROM dual; CURRVAL————— 17

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

INSERT INTO emp VALUES (empseq.nextval, ‘LEWIS’, ‘CLERK’, 7902, SYSDATE, 1200, NULL, 20); UPDATE emp SET deptno = empseq.currval WHERE ename = ‘Jones’

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

ALTER SEQUENCE sequence_2 INCREMENT BY –4;

Когда последовательность больше не нужна, ее можно удалить. Для этого администратор базы данных или владелец последовательности должен выполнить команду DROP SEQUENCE. В результате виртуальные столбцы последовательности NEXVAL и CURRVAL — переводятся в разряд неиспользуемых. Но, если последовательность применялась для генерации значений первичных ключей, созданные ею значения останутся в базе данных. Каскадного удаления значений, сгенерированных последовательностью, при ее удалении не происходит. DROP SEQUENCE. Пример 7. Удаление последовательности SEQUENCE:

CREATE SEQUENCE (Transact-SQL)

Создает объект последовательности и указывает его свойства. Последовательность представляет собой определяемый пользователем объект, привязанный к схеме, который формирует последовательность числовых значений в соответствии со спецификацией, с которой эта последовательность создавалась. Последовательность числовых значений формируется в возрастающем или убывающем порядке с заданным интервалом; можно настроить перезапуск (зацикливание) последовательности, когда она исчерпана. В отличие от столбцов идентификаторов последовательности не связаны с конкретными таблицами. Приложение обращается к объекту последовательности, чтобы получить следующее значение. Приложения управляют связями между последовательностями и таблицами. Пользовательские приложения могут ссылаться на объект последовательности и распределять значения между несколькими строками и таблицами.

В отличие от значений столбцов идентификаторов, которые создаются при вставке строк, приложение может получить следующий порядковый номер без вставки строки, вызвав функцию NEXT VALUE FOR. Получить несколько значений из последовательности за один раз можно с помощью функции sp_sequence_get_range .

Сведения и сценарии использования функций CREATE SEQUENCE и NEXT VALUE FOR см. в разделе Порядковые номера.

Синтаксис

CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH ] [ INCREMENT BY ] [ < MINVALUE [ ] > | < NO MINVALUE >] [ < MAXVALUE [ ] > | < NO MAXVALUE >] [ CYCLE | < NO CYCLE >] [ < CACHE [ ] > | < NO CACHE >] [ ; ] 

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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

[ built_in_integer_type | user-defined_integer_type
Последовательность может быть определена с любым целочисленным типом. Допускаются следующие типы.

  • 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.
  • Любой определяемый пользователем тип данных (псевдоним типа), основанный на одном из допустимых типов.

Если тип данных не указан, то по умолчанию используется тип bigint.

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

INCREMENT BY
Значение, на которое увеличивается (или уменьшается, если оно отрицательное) значение объекта последовательности при каждом вызове функции NEXT VALUE FOR. Если значение приращения отрицательно, то объект последовательности убывает, в противном случае — возрастает. Приращение не может быть равно 0. По умолчанию для нового объекта последовательности используется приращение 1.

[ MINVALUE | NO MINVALUE ]
Указывает граничные значения для объекта последовательности. По умолчанию минимальным значением для нового объекта последовательности служит минимальное значение для типа данных объекта последовательности. Для типа данных tinyint это ноль, для всех остальных типов данных — отрицательное число.

[ MAXVALUE | NO MAXVALUE
Указывает граничные значения для объекта последовательности. По умолчанию максимальным значением для нового объекта последовательности служит максимальное значение для типа данных объекта последовательности.

[ CYCLE | NO CYCLE ]
Свойство, которое указывает, перезапускается объект последовательности с минимального значения (или максимального для объектов убывающих последовательностей) или вызывает исключение, когда достигнуто максимальное (или максимальное) значение. По умолчанию для новых объектов последовательности используется параметр цикличности NO CYCLE.

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

[ CACHE [ ] | NO CACHE ]
Повышает производительность для приложений, использующих объекты последовательностей, сводя к минимуму число операций дискового ввода-вывода, которые требуются для создания порядковых номеров. По умолчанию имеет значение CACHE.

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

Если параметр кэширования задан без указания размера кэша, то размер выбирается компонентом Database Engine. Однако пользователям не следует полагаться на предсказуемость выбора. Корпорация Майкрософт может изменить этот метод вычисления размера кэша без предварительного уведомления.

Если создание проводилось с параметром CACHE, то непредвиденное завершение работы (например, сбой электропитания) может привести к потере порядковых номеров, оставшихся в кэше.

Общие замечания

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

Управление кэшем

Для повышения производительности SQL Server заранее выделяет количество порядковых номеров, указанное в аргументе CACHE.

Например, новая последовательность создается с начальным значением 1 и размером кэша 15. Когда требуется первое значения, из памяти становятся доступными значения с 1 по 15. Последнее кэшированное значение (15) записывается в системные таблицы на диск. Когда используются все 15 номеров, то следующий запрос (для номера 16) вызывает повторное выделение кэша. Новое последнее кэшированное значение (30) записывается в системные таблицы.

Если ядро СУБД останавливается после использования 22 номеров, то следующий порядковый номер, ожидающий в памяти (23), записывается в системные таблицы, заменяя ранее хранившийся номер.

После перезапуска SQL Server, когда требуется порядковый номер, считывается начальный номер из системных таблиц (23). В память выделяется кэш размером в 15 номеров (23–38), а следующий номер, не попавший в кэш (39), записывается в системные таблицы.

Если ядро СУБД непредвиденно завершает работу (например, из-за сбоя электропитания), то последовательность перезапускается с номера, считываемого из системных таблиц (39). Все порядковые номера, выделенные в память (но не запрошенные пользователем или приложением), теряются. При такой обработке возможны пропуски в номерах, однако гарантируется, что одно значение ни в коем случае не будет дважды назначено одному объекту последовательности, если для нее не задан параметр CYCLE или не выполнен перезапуск вручную.

Кэш хранится в памяти путем отслеживания текущего значения (последнего назначенного) и количества значений, оставшихся в кэше. Таким образом, объем памяти, используемый для кэша, всегда равен размеру двух экземпляров типа данных объекта последовательности.

Если установить аргумент кэша в значение NO CACHE, то текущее значение последовательности будет записываться в системные таблицы при каждом использовании последовательности. Это может снизить производительность за счет увеличения числа обращений к диску, но снижает вероятность нежелательных пропусков номеров. Пропуски по-прежнему возможны, если номера запрашиваются с помощью функций NEXT VALUE FOR или sp_sequence_get_range, однако это означает, что пропущенные номера не используются либо используются в незафиксированных транзакциях.

Если в объекте последовательности используется параметр CACHE, то при перезапуске объекта последовательности или изменении свойств INCREMENT, CYCLE, MINVALUE, MAXVALUE или размера кэша кэш записывается в системные таблицы до выполнения изменения. Затем кэш перезагружается, начиная с текущего значения (номера не пропускаются). Изменение размера кэша вступает в силу немедленно.

Параметр CACHE при наличии кэшированных значений

Следующая процедура выполняется каждый раз, когда в объекте последовательности запрашивается создание следующего значения для параметра CACHE, если в кэше в памяти для объекта последовательности доступны неиспользованные значения.

  1. Вычисляется следующее значение для объекта последовательности.
  2. Новое текущее значение для объекта последовательности обновляется в памяти.
  3. Вычисленное значение возвращается к вызывающей инструкции.

Параметр CACHE при пустом кэше

Следующая процедура выполняется каждый раз, когда в объекте последовательности запрашивается создание следующего значения для параметра CACHE, если кэш пуст.

  1. Вычисляется следующее значение для объекта последовательности.
  2. Вычисляется последнее значение для нового кэша.
  3. Строка системной таблицы для объекта последовательности блокируется, а значение, вычисленное на шаге 2 (последнее значение), записывается в системную таблицу. Создается событие Xevent cache-exhausted, чтобы сообщить пользователю о новом сохраненном значении.

Параметр NO CACHE

Следующая процедура выполняется каждый раз, когда в объекте последовательности запрашивается создание следующего значения для параметра NO CACHE.

  1. Вычисляется следующее значение для объекта последовательности.
  2. Новое текущее значение для объекта последовательности записывается в системную таблицу.
  3. Вычисленное значение возвращается к вызывающей инструкции.

Метаданные

Чтобы получить сведения о последовательностях, запросите представление sys.sequences.

Безопасность

Разрешения

Необходимо разрешение CREATE SEQUENCE, ALTERили CONTROL для схемы SCHEMA.

  • Члены предопределенных ролей базы данных db_owner и db_ddladmin могут создавать, изменять и удалять объекты последовательности.
  • Члены предопределенных ролей базы данных db_owner и db_datawriter могут обновлять объекты последовательности, вызывая создание номеров.

В следующем примере пользователю AdventureWorks\Larry предоставляется разрешение на создание последовательностей в схеме Test.

GRANT CREATE SEQUENCE ON SCHEMA::Test TO [AdventureWorks\Larry] 

Владение объектом последовательности может быть передано с помощью инструкции ALTER AUTHORIZATION.

Если в последовательности используется определяемый пользователем тип данных, то создатель последовательности должен иметь разрешение REFERENCES для этого типа.

Аудит

Для аудита инструкции CREATE SEQUENCE отслеживайте SCHEMA_OBJECT_CHANGE_GROUP.

Примеры

Примеры создания последовательностей и использования функции NEXT VALUE FOR для формирования порядковых номеров см. в разделе Порядковые номера.

В большинстве из следующих примеров объекты последовательности создаются в схеме с именем Test.

Чтобы создать схему Test, выполните следующую инструкцию.

CREATE SCHEMA Test ; GO 

A. Создание последовательности, увеличивающейся на 1

В следующем примере пользователь Thierry создает последовательность с именем CountBy1, которая увеличивается на единицу при каждом использовании.

CREATE SEQUENCE Test.CountBy1 START WITH 1 INCREMENT BY 1 ; GO 

Б. Создание последовательности, уменьшающейся на 1

В следующем примере отсчет начинается с 0 и идет по отрицательным числам, уменьшаясь на единицу при каждом использовании.

CREATE SEQUENCE Test.CountByNeg1 START WITH 0 INCREMENT BY -1 ; GO 

В. Создание последовательности, увеличивающейся на 5

В следующем примере создается последовательность, которая увеличивается на 5 при каждом обращении.

CREATE SEQUENCE Test.CountBy1 START WITH 5 INCREMENT BY 5 ; GO 

Г. Создание последовательности, начинающейся с заданного числа

После импорта таблицы Thierry замечает, что максимальный номер идентификатора составляет 24 328. Thierry требуется последовательность, которая будет создавать номера, начиная с 24 329. В следующем коде создается последовательность, начинающаяся с 24 329 и увеличивающаяся на 1.

CREATE SEQUENCE Test.ID_Seq START WITH 24329 INCREMENT BY 1 ; GO 

Д. Создание последовательности со значениями по умолчанию

В следующем примере создается последовательность со значениями по умолчанию.

CREATE SEQUENCE Test.TestSequence ; 

Чтобы просмотреть свойства последовательности, выполните следующую инструкцию.

SELECT * FROM sys.sequences WHERE name = 'TestSequence' ; 

Частичный перечень выходных данных демонстрирует значения по умолчанию.

Выходные данные Значение по умолчанию
start_value -9223372036854775808
increment 1
mimimum_value -9223372036854775808
maximum_value 9223372036854775807
is_cycling 0
is_cached 1
current_value -9223372036854775808

Е. Создание последовательности с заданным типом данных

В следующем примере создается последовательность с типом данных smallint и диапазоном значений от –32 768 до 32 767.

CREATE SEQUENCE SmallSeq AS smallint ; 

Ж. Создание последовательности с использованием всех аргументов

В следующем примере создается последовательность с именем DecSeq, использующая тип данных decimal и диапазон от 0 до 255. Последовательность начинается со 125 и увеличивается на 25 при каждом создании номера. Поскольку для последовательности настроено циклическое повторение при превышении максимального значения 200, она перезапускается с минимального значения 100.

CREATE SEQUENCE Test.DecSeq AS decimal(3,0) START WITH 125 INCREMENT BY 25 MINVALUE 100 MAXVALUE 200 CYCLE CACHE 3 ; 

Чтобы просмотреть первое значение, выполните следующую инструкцию. Параметр START WITH равен 125.

SELECT NEXT VALUE FOR Test.DecSeq; 

Выполните инструкцию еще три раза, чтобы вернуть значения 150, 175 и 200.

Снова выполните инструкции, чтобы увидеть, как начальное значение вернется к значению параметра MINVALUE , равного 100.

Выполните следующий код, чтобы подтвердить размер кэша и показать текущее значение.

SELECT cache_size, current_value FROM sys.sequences WHERE name = 'DecSeq' ; 

Create SEQUENCE

Последовательность 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 27 , минимальное значение соответственно -10 26 .

В СУБД 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 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, который не гарантирует, что номера последовательности генерируются в порядке запросов

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

Пример 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 VALUE FOR order_id WHERE num like '%20151110/12%';

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

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

DROP SEQUENCE [SCHEMA.]SEQUENCE_NAME;

Объект SEQUENCE (последовательность) в Microsoft SQL Server

Всем привет! Сегодня мы поговорим про объект SEQUENCE (последовательность) в Microsoft SQL Server, Вы узнаете, что такое SEQUENCE, для чего этот объект нужен, какие у него особенности, и, конечно же, научитесь им пользоваться (создавать, изменять, удалять и использовать).

Объект SEQUENCE (последовательность) в Microsoft SQL Server

SEQUENCE в Microsoft SQL Server

SEQUENCE – это объект SQL Server, который генерирует числовые значения в определенной последовательности в соответствии с заданной спецификацией.

В числе основных предназначений SEQUENCE является формирования значений для столбца идентификаторов в таблицах.

В Microsoft SQL Server SEQUENCE, как объект, появился только в 2012 версии, ранее для формирования значений столбцов идентификаторов в таблицах использовалось свойство IDENTITY. Теперь для этих целей можно использовать и IDENTITY, и SEQUENCE, в чем их отличие я подробно расскажу в следующих материалах.

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

Последовательность числовых значений в Microsoft SQL Server может формироваться в возрастающем или убывающем порядке с заданным интервалом. Кроме того, можно настроить перезапуск (т.е. зацикливание) последовательности, когда она исчерпана, а также задать минимальное и максимальное значения последовательности.

Значение последовательности получают с помощью вызова функции NEXT VALUE FOR, которое возвращает одно значение, однако можно получить сразу несколько значений за один раз, это делается с помощью системной процедуры sp_sequence_get_range.

SEQUENCE можно изменить уже после создания, так же, как и другие объекты, это делается с помощью инструкции ALTER SEQUENCE.

Еще одной особенностью SEQUENCE является то, что в своей работе она использует кэширование, что повышает производительность для приложений, которые используют последовательность. Это достигается за счет того, что значения последовательности заранее создаются и хранятся в памяти, а при обращении к SEQUENCE за новым значением это значение возвращается из памяти, тем самым значительно уменьшается число операций дискового ввода-вывода, которые требуются для создания значений последовательности.

Управляется кэширование последовательности с помощью параметра CACHE, который по умолчанию включен. Для его отключения необходимо указать NO CACHE.

Посмотреть, какие последовательности есть в базе данных и получить все сопутствующие сведения о последовательностях (параметры, с которыми они создавались), Вы можете с помощью обращения к системному представлению sys.sequences.

Синтаксис SEQUENCE

CREATE SEQUENCE [schema_name].[sequence_name] [ AS [data type] ] [ START WITH < value >] [ INCREMENT BY < value >] [ MINVALUE [ < value >] | NO MINVALUE ] [ MAXVALUE [ < value >] | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE [ < value >] | NO CACHE ];

Описание синтаксиса

Параметр Описание
CREATE SEQUENCE Инструкция создания объектов последовательности.
schema_name и sequence_name Имя схемы и имя последовательности.
value Значение параметра.
AS Тип данных значения, которое будет возвращать последовательность. Допускаются только целочисленные значения: TINYINT, SMALLINT, INT, BIGINT или NUMERIC с масштабом 0. Более подробно о типах данных можете посмотреть в отдельном материале – Типы данных в T-SQL (Microsoft SQL Server). Если тип данных не указан, то по умолчанию используется BIGINT.
START WITH Начальное значение, возвращаемое объектом последовательности. Это значение должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением у возрастающей последовательности является минимально возможное значение, а для убывающей последовательности – максимально возможное.
INCREMENT BY Значение, на которое увеличивается (или уменьшается) значение объекта последовательности.
Если данное значение отрицательное, то объект последовательности убывает, если положительное, то возрастает.
По умолчанию используется значение 1. Данное значение не может быть равно 0.
MINVALUE Минимальное значение объекта последовательности.
По умолчанию минимальным значением для новой последовательности является минимальное значение для типа данных этой последовательности. Обращаю внимание, что для всех типов данных, кроме tinyint, минимальное значение – это отрицательное число.
MAXVALUE Максимальное значение объекта последовательности.
По умолчанию максимальным значением для последовательности является максимальное значение типа данных объекта последовательности.
CYCLE Параметр показывает, должна ли последовательность быть перезапущена, как только она достигнет своего максимального или минимального значения. По умолчанию используется параметр NO CYCLE.
CACHE Параметр, который управляет кэшированием значений объектов последовательности. По умолчанию имеет значение CACHE. Для отключения кэширования необходимо указать NO CACHE.

Практически все параметры являются необязательными, для создания последовательности с параметрами по умолчанию необходимо указать всего лишь инструкцию создания последовательности и имя последовательности. Однако начальное значение последовательности лучше все-таки указывать, если она будет использоваться в качестве генератора идентификаторов для таблицы.

Примеры создания SEQUENCE в Microsoft SQL Server

Теперь давайте разберём, как создается последовательность в Microsoft SQL Server.
Для выполнения примеров у меня есть база данных Test, в которой я и буду создавать последовательность.

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

Во всех примерах последовательность будет создаваться с названием TestSequence в схеме по умолчанию (dbo), поэтому в инструкциях я не буду указывать конкретное название схемы.

Создание SEQUENCE со значениями по умолчанию

CREATE SEQUENCE TestSequence;

Создание SEQUENCE с начальным значением 1 и увеличением на 1

CREATE SEQUENCE TestSequence START WITH 1 INCREMENT BY 1;

Создание SEQUENCE с начальным значением 10 и увеличением на 5

CREATE SEQUENCE TestSequence START WITH 10 INCREMENT BY 5;

Создание SEQUENCE с начальным значением 0 и уменьшением на 1

CREATE SEQUENCE TestSequence START WITH 0 INCREMENT BY -1;

Создание SEQUENCE с заданного числа и увеличением на 1

CREATE SEQUENCE TestSequence START WITH 12345 INCREMENT BY 1;

Создание SEQUENCE с заданным типом данных

CREATE SEQUENCE TestSequence AS TINYINT START WITH 1 INCREMENT BY 1;

Создание SEQUENCE с указанием максимального значения

CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;

Создание SEQUENCE с поддержкой цикла

CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50 CYCLE;

Примеры изменения SEQUENCE

Параметры существующего объекта SEQUENCE можно изменить. Это делается с помощью инструкции ALTER.

Изменение параметров существующей последовательности

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

Например, допустим Вы создали последовательность со значениями по умолчанию, а потом захотели уточнить минимальное, максимальное и начальное значение. Для этого Вы можете написать следующую инструкцию, в которой начальное значение, шаг приращения и минимальное значение будет 1, а максимальное 50.

ALTER SEQUENCE TestSequence RESTART WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;

Перезапуск последовательности

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

ALTER SEQUENCE TestSequence RESTART WITH 1;

где 1 – это новое начальное значение последовательности.

Примеры использование SEQUENCE

Создавать и изменять объект SEQUENCE мы научились, теперь давайте научимся использовать его для получения значения последовательности.

Обратиться к последовательности и получить следующее значение этой последовательности можно с помощью функции NEXT VALUE FOR, которую можно использовать даже в простом запросе SELECT.

Примечание! Функцию NEXT VALUE FOR нельзя использовать в запросах с операторами DISTINCT, UNION, UNION ALL, EXCEPT, INTERSECT, а также с инструкциями FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY и FOR XML.

Простое обращение к SEQUENCE

SELECT NEXT VALUE FOR TestSequence; SELECT NEXT VALUE FOR TestSequence; SELECT NEXT VALUE FOR TestSequence;

Скриншот 1

Использование SEQUENCE с INSERT

Сейчас давайте посмотрим, как можно использовать SEQUENCE в инструкции INSERT для генерирования значения для столбца идентификаторов в таблице.

Исходные данные для примеров

Для выполнения примеров нам потребуется таблица, в которую мы будет вставлять новые строки. В примерах будет использована таблица, созданная следующей инструкцией (для каждого примера данная таблица пересоздавалась).

CREATE TABLE Goods( ProductId INT NOT NULL, ProductName VARCHAR(100) NOT NULL );

Инструкция INSERT INTO с использованием SEQUENCE

Следующая инструкция вставляет 3 строки данных в таблицу Goods, значение ProductId мы берем из последовательности TestSequence с помощью функции NEXT VALUE FOR.

INSERT INTO Goods VALUES (NEXT VALUE FOR TestSequence, 'Компьютер'), (NEXT VALUE FOR TestSequence, 'Монитор'), (NEXT VALUE FOR TestSequence, 'Клавиатура'); SELECT * FROM Goods;

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

SEQUENCE создана со значениями по умолчанию

Скриншот 2

Как видите у нас возникла ошибка, это произошло потому, что мы не указали никаких параметров последовательности, все они были созданы со значениями по умолчанию.

Так, тип данных значения последовательности по умолчанию BIGINT, отсюда начальное значение последовательности очень большое (отрицательное) и оно не входит в диапазон значений типа данных INT, а именно этот тип данных имеет столбец ProductId в таблице Goods.

В результате мы имеем ошибку арифметического переполнения.

SEQUENCE создана с начальным значением 1 и увеличением на 1

Скриншот 3

SEQUENCE создана с начальным значением 10 и увеличением на 5

Скриншот 4

SEQUENCE создана с начальным значением 0 и уменьшением на 1

Скриншот 5

SEQUENCE создана с заданного числа и увеличением на 1

Здесь мы указали 12345 в качестве начального значения последовательности.

Скриншот 6

SEQUENCE создана с указанием максимального значения

CREATE SEQUENCE TestSequence AS INT START WITH 49 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50;

Скриншот 7

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

SEQUENCE создана с поддержкой цикла

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

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

CREATE SEQUENCE TestSequence AS INT START WITH 49 INCREMENT BY 1 MINVALUE 1 MAXVALUE 50 CYCLE;

Скриншот 8

Удаление SEQUENCE

Удаляется последовательность, так же, как и другие объекты в SQL Server, с помощью инструкции DROP. Таким образом, для удаления последовательности необходимо написать DROP SEQUENCE. Параметр IF EXISTS у инструкции DROP также можно использовать (IF EXISTS доступен, начиная с 2016 версии SQL Server).

Например, для удаления нашей тестовой последовательности можно использовать следующую инструкцию

DROP SEQUENCE IF EXISTS TestSequence;

Заключение

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *