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 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
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, если в кэше в памяти для объекта последовательности доступны неиспользованные значения.
- Вычисляется следующее значение для объекта последовательности.
- Новое текущее значение для объекта последовательности обновляется в памяти.
- Вычисленное значение возвращается к вызывающей инструкции.
Параметр CACHE при пустом кэше
Следующая процедура выполняется каждый раз, когда в объекте последовательности запрашивается создание следующего значения для параметра CACHE, если кэш пуст.
- Вычисляется следующее значение для объекта последовательности.
- Вычисляется последнее значение для нового кэша.
- Строка системной таблицы для объекта последовательности блокируется, а значение, вычисленное на шаге 2 (последнее значение), записывается в системную таблицу. Создается событие Xevent cache-exhausted, чтобы сообщить пользователю о новом сохраненном значении.
Параметр NO CACHE
Следующая процедура выполняется каждый раз, когда в объекте последовательности запрашивается создание следующего значения для параметра NO CACHE.
- Вычисляется следующее значение для объекта последовательности.
- Новое текущее значение для объекта последовательности записывается в системную таблицу.
- Вычисленное значение возвращается к вызывающей инструкции.
Метаданные
Чтобы получить сведения о последовательностях, запросите представление 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 для этого типа.
Audit
Для аудита инструкции CREATE SEQUENCE отслеживайте SCHEMA_OBJECT_CHANGE_GROUP.
Примеры
Примеры создания последовательностей и использования функции NEXT VALUE FOR для формирования порядковых номеров см. в разделе Порядковые номера.
В большинстве из следующих примеров объекты последовательности создаются в схеме с именем Test.
Чтобы создать схему Test, выполните следующую инструкцию.
CREATE SCHEMA Test ; GO А. Создание последовательности, увеличивающейся на 1
В следующем примере пользователь Thierry создает последовательность с именем CountBy1, которая увеличивается на единицу при каждом использовании.
CREATE SEQUENCE Test.CountBy1 START WITH 1 INCREMENT BY 1 ; GO B. Создание последовательности, уменьшающейся на 1
В следующем примере отсчет начинается с 0 и идет по отрицательным числам, уменьшаясь на единицу при каждом использовании.
CREATE SEQUENCE Test.CountByNeg1 START WITH 0 INCREMENT BY -1 ; GO C. Создание последовательности, увеличивающейся на 5
В следующем примере создается последовательность, которая увеличивается на 5 при каждом обращении.
CREATE SEQUENCE Test.CountBy1 START WITH 5 INCREMENT BY 5 ; GO D. Создание последовательности, начинающейся с заданного числа
После импорта таблицы 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' ; Частичный перечень выходных данных демонстрирует значения по умолчанию.
| Выходные данные | Default value |
|---|---|
| start_value | -9223372036854775808 |
| increment | 1 |
| mimimum_value | -9223372036854775808 |
| maximum_value | 9223372036854775807 |
| is_cycling | 0 |
| is_cached | 1 |
| current_value | -9223372036854775808 |
F. Создание последовательности с заданным типом данных
В следующем примере создается последовательность с типом данных smallint и диапазоном значений от –32 768 до 32 767.
CREATE SEQUENCE SmallSeq AS smallint ; G. Создание последовательности с использованием всех аргументов
В следующем примере создается последовательность с именем 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' ; SEQUENCES (AUTONUMBER) последовательность
В Oracle/PLSQL, вы можете создать автонумерацию с помощью последовательности. Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда вам нужно создать уникальный номер в качестве первичного ключа.
CREATE SEQUENCE
Синтаксис
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
sequence_name имя последовательности, которую вы хотите создать.
Пример
Oracle PL/SQL
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
Этот код создаст объект последовательность под названием supplier_seq. Первый номер последовательности 1, каждый последующий номер будет увеличиваться на 1 (т.е.. 2,3,4, . ). Это будет кэшировать до 20 значений для производительности.
Если вы опустите параметр MAXVALUE , ваша последовательность по умолчанию до:
MAXVALUE 999999999999999999999999999
Таким образом, вы можете упростить CREATE SEQUENCE. Написав следующее:
Oracle PL/SQL
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
Теперь, когда вы создали объект последовательности для автонумерации поля счетчика, мы рассмотрим, как получить значение из этого объекта последовательности. Чтобы получить следующее значение, вам нужно использовать NEXTVAL .
Например:
supplier_seq.NEXTVAL;
Это позволит извлечь следующее значение из последовательности supplier_seq . Предложение NEXTVAL нужно использовать в SQL запросе. Например:
Oracle PL/SQL
INSERT INTO suppliers
(supplier_id, supplier_name)
(supplier_seq.NEXTVAL, ‘Kraft Foods’ );
Этот isert запрос будет вставлять новую запись в таблицу suppliers (поставщики). Полю Supplier_id будет присвоен следующий номер из последовательности supplier_seq . Поле supplier_name будет иметь значение ‘Kraft Foods’.
DROP SEQUENCE
После того как вы создали последовательность в Oracle, вам можете понадобиться удалить её из базы данных.
Синтаксис:
DROP SEQUENCE sequence_name;
sequence_name имя последовательности, которую вы хотите удалить.
Пример
Рассмотрим на примере, как удалить последовательность в Oracle.
DROP SEQUENCE supplier_seq;
Этот пример удалит последовательность supplier_seq .
ЧАСТО ЗАДАВАЕМЫЕ ВОПРОСЫ
Вопрос: При создании последовательности, что означают опции cache и nocache ? Например, можно создать последовательность с опцией cache 20 следующим образом:
Oracle PL/SQL
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
Или вы могли бы создать такую же последовательность, но с опцией nocache :
Oracle PL/SQL
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
Ответ: Что касается последовательности, опция cache определяет, сколько значений последовательности будут сохранены в памяти для быстрого доступа.
Недостатком создания последовательности с cache, что если происходит отказ системы, все кэшированные значения последовательности, которые не были использованы, будут утеряны. Это приведет к разрывам в значениях, назначенной последовательности. Когда в система восстановится, Oracle будет кэшировать новые номера, с того места, где была прервана последовательность, игнорируя утерянные значения последовательности.
Примечание: Для восстановления утраченных значений последовательности, вы всегда можете выполнить команду ALTER SEQUENCE для сброса счетчика на правильное значение.
nocache означает, что ни одно из значений последовательности не хранятся в памяти. Эта опция может понизить производительность, однако, вы не должны столкнуться с разрывами в значениях, назначенной последовательности.
Вопрос: Как установить значение lastvalue в последовательность Oracle?
Ответ: Вы можете изменить lastvalue для последовательности Oracle, выполнив команду ALTER в последовательности.
Например, если последнее значение используемой последовательности Oracle был 100, и вы хотите, чтобы следующее значение было 225. Вы должны выполнить следующие команды.
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:
Сиквенсы (Sequences)
Сиквенс – это структура для генерации уникальных целочисленных значений. Только одна сессия может запросит следующее значение и таким образом увеличить счётчик. Поэтому все сгенерированные значения будут уникальными.
Сиквенс это бесценный инструмент для генерации значений первичного ключа. Многие приложения нуждаются в автоматически сгенерированных значениях первичного ключа. Например номерпокупателя и номер заказа: бизнес-аналитики могут решить что каждый заказ должен иметь уникальный номер, которые последовательно увеличивается. В других приложениях вы можете не иметь явных бизнес требований к ключам, но они понядобятся для организации ссылочной целостности. Например в учёте телефонных звонков: с точки зрения бизнес идентификатором является телефонный номер (строка) и звонком будет значение телефона и время начала звонка. Эти типы данных очень сложные для использования их как первичных ключей для больших объёмов которые обязательно будут в системе учёта звонков. Для записи этой информации гораздо легче использовать простые численные столбцы для определения первичных и внешних ключей. Значения этих столбцов могут основываться на сиквенсах.
Мехнизм сиквенсов не зависит от таблиц, механизма блокировок и транзакций. Это значит что сиквенс может генерировать тысячи уникальных значений в минуту – гораздо быстрее чем методы выборки данных, обновления и подтверждения изменений.
На рисунке 7-6 показано как две сессий выбирают значения из сиквенса SEQ1. Обратите внимание что каждый запрос SEQ1.NEXTVAL генерирует уникальный номер. Значение создаётся по порядку в зависимости от времени обращения, и значение увеличивается глобально а не для одной сессии.
Создание сиквенсов
Полный синтаксис для создания сиквенса
CREATE SEQUENCE [schema.]sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CACHE number | NOCACHE]
Создание сиквенса может быть очень простым. Например сиквенс использованный на рисунке 7-6 был создан с помощью команды
create sequence seq1;
Список доступных параметров
Директива CYCLE используется очень редко так как позволяет генерировать дубликаты. Если сиквенс используется для генерации значений первичного ключа, CYCLE имеет смысл только есть функция в БД которая удаляет старые записи быстрее чем сиквенс генерирует новые.
Кеширование значений критично для производительности. Выборка из сиквенса может осуществляться только одной сессией в момент времени. Механизм генерации значений очент эффективный: он гораздо быстрее чем блокировка строки, обновление строки или управление транзакцией. Но даже несмотря на это, выборка из сиквенса может быть точкой конкуренции сессий. Директива CACHE позволяет Oracle генерировать номера блоками. Пред-сгенерированные значения выбираются быстрее чем генерация по запросу.
The default number of values to cache is only 20. Experience shows that this is usually not enough. If your application selects from the sequence 10 times a second, then set the cache value to 50 thousand. Don’t be shy about this
Использование сиквенсов
Для использования сиквенса сессия может запросить следующее значения используя псевдо-столбец NEXTVAL, который заставляет сиквенс увеличить значение, или запросить последнее (текущее) значение для текущей сессии используя псевдостолбец CURRVAL. Значение NEXTVAL будет глобально уникальным: каждая сессия которая запрашивает это значение будет получать разный, увеличенный результат для каждого запроса. Значение CURRVAL будет постоянным для каждой сессии пока не будет новый запрос к NEXTVAL. Нет возможности узнать какое последнее значение было сегенрировано сиквенсом: вы можете выбрать только следующее значение вызвав NEXTVAL, и узнать последнее использованное значение для вашей сессии используя CURRVAL. Но вы не можете узнать последнее сгенерированное значение.
The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.
Типичным примером использования сиквенса является генерация значений первичного ключа. Следующий пример использует сиквенс ORDER_SEQ для генерации уникальных значений номера заказа и сиквенс LINE_SEQ для генерации уникального значения строки заказа. Вначале создаётся сиквенс (один раз)
create sequence order_seq start with 10;
create sequence line_seq start with 10;
Затем вставка заказа и пунктов заказа в одной транзакции
insert into orders (order_id,order_date,customer_id)
insert into order_items (order_id,order_item_id,product_id)
insert into order_items (order_id,order_item_id,product_id)
Первая команда INSERT создает заказ с уникальным номером из сиквенса ORDER_SEQ для покупателя с номером 1000. Затем вторая и третья команды INSERT добавляют два элемента заказа используя текущее значение сиквенса ORDER_SEQ как значение для внешнего ключа соединяющего элементы заказа с заказом и следующее значение сиквенса LINE_SEQ для генерации уникального идентификатора каждого элемента. И наконец транзакция подтверждается.
Сиквенс не привязан к какой-то таблице. В предыдущем примере можно использовать один сиквенс для генерации значений для первичны ключей таблицы заказов и таблицы элементов заказа.
COMMIT не обязателен для подвтерждения увеличения счетчика: увеличение счётчика происходи сразу и навсегда и становится видимым для всех в момент увеличения. Нельзя отменить увеличение счётчика. Сиквенс обновляется вне зависимости от механизма управления транзакциями. Поэтому всегда будут пропавшие номера. Разрывы могут быть большими если БД перезапускается и CACHE директива использовалась для счётчика. Все номера которые были сгенерированы и не выбирались будут потеряны в момент выключения базы данных. После следующего запуска текущее значение будет последнее сгенерированное, а не последнее использованное. Таким образом для значения по умолчанию 20, каждый перезапуск приводит к потере 20 номеров.
Если бизнес-аналитики решили что не может быть разрыва в последовательности номеров, тогда можно генерировать уникальный номер по другому. Для предыдущего примера заказов текущий номер заказа можно хранить в таблице с начальным значением в 10
create table current_on(order_number number);
insert into current_on values(10);
Тогда код для создания заказа станет следующим
update current_on set order_number=order_number + 1;
insert into orders (order_number,order_date,customer_number)
values ((select order_number from current_on),sysdate,’1000′);
Это будет работать с точки зрения генерации уникального номера заказа, и так как увеличение номера заказа происходит внутри транзакции то увеличение можно отменить в случае небходимости: тогда не будет разрывов в последовательности, до тех пор пока заказ не будет сознательно удалён. Но это гораздо менее эффективно чем использование сиквенсов, так как код будет слабо производителен в многопользовательской среде. Если много сессий попробуют заблокировать и увеличить значение в строке содержащей текущий номер заказа, то всё приложение будет подвисать посклько будет ждать своей очереди.
После создания сиквенса он может быть изменена. Синтаксис команды следующий
ALTER SEQUENCE sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CACHE number | NOCACHE]
Команда ALTER такая же как команда CREATE за одним исключением: нельзя установить начальное значение. Если вы хотите обновить начальное значение – то единственный способ это удалить сиквенс и создать новый. Для изменения значения CACHE для увеличения производительности можно выполнить следующую команду
alter sequence order_seq cache 1000;
Для удаления сиквенса выполните команду
drop sequence order_seq;
- DDL и объекты схемы — Итоги
- Создание простой таблицы
- Ограничения
- Объекты БД
- Индексы