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:
MS SQL 2011 – новый объект Sequence
Возможность, которой не удивишь нынче пользователей Oracle, DB2, PostgreSQL и множества других реляционных баз данных, наконец-то появилась и в MS SQL Server. На арене Sequence!
Sequence – генерирует последовательность чисел так же как и identity. Однако основным плюсом sequence является то, что последовательность не зависит от какой-либо конкретной таблицы и является объектом базы данных.
Рассмотрим пример скрипта написанного на SQL Server 2008. Создание простой таблицы с двумя колонками, одна из которых будет автоинкрементной.
Create Table WithOutSequence1 ( EmpId int identity not null primary key ,EmpName varchar(50) not null ) Insert into WithOutSequence1 Select 'Violet' Union All Select 'Tape' Select * from WithOutSequence1Похожим образом создадим еще одну таблицу.
Create Table WithOutSequence2 ( EmpId int identity not null primary key ,EmpName varchar(50) not null ) Insert into WithOutSequence2 Select 'Violet' Union All Select 'Tape' Select * from WithOutSequence2Как можно заметить из примеров, мы записали значения в таблицу при этом значение инкрементального поля автоматически и независимо от нас заполнилось. Мы не можем повторно использовать значение этого поля в другой таблице. Давайте посмотрим как можно выйти из этой ситуации с помощью Sequence.
Общий синтаксис для команды выглядит так:
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 >Создадим последовательность чисел:
IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateNumberSequence' AND TYPE='SO') DROP Sequence GenerateNumberSequence GO SET ANSI_NULLS ON GO CREATE SEQUENCE GenerateNumberSequence START WITH 1 INCREMENT BY 1; GOПосле выполнения указанного скрипта, в браузере объектов базы, в узле Sequences можно найти наш объект.
После того как объект создан, можно его использовать в создании и заполнении таблиц как показано ниже:
Create Table WithSequence1 ( EmpId int not null primary key ,EmpName varchar(50) not null ); Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence1;Если создать вторую таблицу в таком же духе, то можно снова использовать GenerateNumberSequence и получать сквозную нумерацию объектов.
Create Table WithSequence2 ( EmpId int not null primary key ,EmpName varchar(50) not null ); Insert into WithSequence2(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence2;Последовательность (Sequence) которую мы создали, можно посмотреть в системном каталоге sys.sequences.
SELECT Name ,Object_ID ,Type ,Type_Desc ,Start_Value ,Increment ,Minimum_Value ,Maximum_Value ,Current_Value ,Is_Exhausted FROM sys.sequences
Это не вся доступная информация по sequence, просто эти колонки нам понадобятся далее. Чтобы получить всю информацию замените имена колонок на звездочку. Про Is_Exhausted будет упомянуто позднее.
- Int
- Smallint
- Tinyint
- Bigint
- Decimal
- Numeric
Проверим на практике, что скажет SQL Server при задании начального числа вне допустимого диапазона. Начнем с левой границы.
CREATE SEQUENCE GenerateNumberSequence START WITH -2147483649 --outside the range of the int datatype boundary INCREMENT BY 1;An invalid value was specified for argument ‘START WITH’ for the given data type.
Что и ожидалось. Теперь нарушим правую границу.
CREATE SEQUENCE GenerateNumberSequence START WITH 2147483647 --the max range of the int datatype INCREMENT BY 1;Сервер сообщит нам об ошибке так:
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.
ALTER SEQUENCE dbo.GenerateNumberSequence RESTART WITH 1;Значение должно быть в пределах допустимого диапазона объявленного типа. Далее последовательность начнется с указанного значения, не со следующего.
ALTER SEQUENCE dbo.GenerateNumberSequence RESTART WITH 10;А потом выполнить скрипт:
Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'violet'), (NEXT VALUE FOR GenerateNumberSequence, 'tape') SELECT * FROM WithSequence1;То результат будет таким:
EmpId EmpName ----- ------- 10 violet 11 tapeПоследовательность началась с заданного значения.
Получить минимальные и максимальные значения можно из каталога sys.sequences.
MIN и MAX значения
Для последовательностей можно задавать границы допустимых значений. Попробуем выполнить такой скрипт ниже.
CREATE SEQUENCE GenerateNumberSequence START WITH 1 INCREMENT BY 1 MINVALUE 10 MAXVALUE 20Минимальное значение равняется 10, максимальное – 20, но мы пытаемся задать начальное значение равное единице. Это за пределами допустимого диапазона и поэтому нас порадуют сообщением:
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
Данная опция зацикливает последовательность и, достигнув максимального значения, последовательность продолжается с минимального. Например:
CREATE SEQUENCE GenerateNumberSequence START WITH 20 INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 CYCLEПосле того как максимальное значение было достигнуто, результаты станут такими:
EmpId EmpName ----- ------- 10 Tape 20 VioletДля выборки использовался запрос:
Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence1;Если внимательно посмотреть на вывод, то можно заметить, что записи были перепутаны. Если бы мы не использовали последовательности, то вывод был бы
EmpId EmpName ----- ------- 20 Violet 21 TapeНо из-за того, что вторая запись пересекла диапазон допустим значений, номер был сброшен на минимальное значение, заданное для последовательности (10). Если сейчас посмотреть в каталог sys.sequences, то будет видно, что текущее значение равняется 10.
В следующий раз, заполнение таблицы могло бы быть таким:
EmpId EmpName ---- ------- 11 Violet 12 TapeВ этот момент Sequence проверит порядок в котором записи будут вставлены и так как “Violet” идет раньше “Tape” и текущий номер равен 10, записи будут вставлены как:
Следующее_значение =Текущее_значение +Сдвиг т.е. 10 +1 будет присвоено для “Violet”. Теперь значение Sequence = 11 и для второй записи значение будет 12 следуя то же самой формуле.
Опция NO CYCLE
Поведение такой опции уже рассматривалось в самом начале, и является значением по умолчанию при создании Sequence.
Sequence в сочетании с Over()
Можно использовать последовательность вместе с выражением Over для генерирования порядковых номеров как показано ниже:
--Declare a table Declare @tblEmp Table ( EmpId int identity ,EmpName varchar(50) not null ) --Populate some records Insert Into @tblEmp Select 'Niladri' Union All Select 'Arina' Union All Select 'Deepak' Union All Select 'Debasis' Union All Select 'Sachin' Union All Select 'Gaurav' Union All Select 'Rahul' Union All Select 'Jacob' Union All Select 'Williams' Union All Select 'Henry' --Fire a query SELECT e.* ,Seq = NEXT VALUE FOR GenerateNumberSequence OVER (ORDER BY EmpName) FROM @tblEmp e
Можно заметить, что записи были отсортированы и последовательность была применена верно к сохраненным данным. Это означает, что записи сначала сортируются и только потом применяется нумерация последовательности.
Ограничения использования 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 для получения необходимого диапазона значений, которые можно использовать впоследствии. Сейчас продемонстрирую как это можно осуществить.
-- удаляем последовательность, если она существует IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateRangeNumberSequence' AND TYPE='SO') DROP Sequence GenerateRangeNumberSequence GO -- удаляем таблицу, если она существует IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_RangeSequence' AND type = 'U') DROP TABLE tbl_RangeSequence GO SET ANSI_NULLS ON GO -- создаем последовательность CREATE SEQUENCE GenerateRangeNumberSequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2000 CYCLE GO -- создаем таблицу CREATE TABLE [dbo].[tbl_RangeSequence]( [EmpId] [int] NOT NULL, [EmpName] [varchar](50) NOT NULL, PRIMARY KEY CLUSTERED ( [EmpId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --объявляем необходимые параметры для процедуры sp_sequence_get_range DECLARE @sequence_name nvarchar(100) = N'GenerateRangeNumberSequence', @range_size int = 1000, @range_first_value sql_variant, @range_last_value sql_variant, @sequence_increment sql_variant, @sequence_min_value sql_variant, @sequence_max_value sql_variant; -- запускаем процедуру sp_sequence_get_range EXEC sp_sequence_get_range @sequence_name = @sequence_name, @range_size = @range_size, @range_first_value = @range_first_value OUTPUT, @range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT, @sequence_min_value = @sequence_min_value OUTPUT, @sequence_max_value = @sequence_max_value OUTPUT; -- показываем значения SELECT @range_size AS [Range Size], @range_first_value AS [Start Value], @range_last_value AS [End Value], @sequence_increment AS [Increment], @sequence_min_value AS [Minimum Value], @sequence_max_value AS [Maximum Value]; -- строим массив значений с помощью СТЕ ;With Cte As ( Select Rn = 1, SeqValue = Cast(@range_first_value as int) Union All Select Rn+1, Cast(SeqValue as int) + Cast( @sequence_increment as int) From Cte Where Rn
Вот что будет в результате выполнения:
Здесь можно увидеть, что последовательность была увеличена до 1000 и пропущенные значения не были использованы нигде без нашего ведома. В данном случае мы их использовали для вставки значений.
Сравнение между Sequence и Identity
- Identity относится к таблице и является ее частью неотделимой, Sequence – независимый объект базы данных.
- Можно получить набор последовательности с помощью sp_sequence_get_range, что в принципе невозможно с Identity.
- Для Sequence можно определять границы значений, что так же невозможно для Identity.
- Цикличность значений можно задать так же только для Sequence.
- Sequence дает больший прирост производительности по сравнению с Identity. Сравнение и результаты в статье Аарона Бертарнда (Aaron Bertrand)
- Можно задавать права доступа к Sequence, так же как и к другим объектам базы.
- CREATE SEQUENCE
- Creating and Using Sequence Numbers
- sp_sequence_get_range
Что такое сиквенс в базе данных
CREATE SEQUENCE — создать генератор последовательности
Синтаксис
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ]имя[ INCREMENT [ BY ]шаг] [ MINVALUEмин_значение| NO MINVALUE ] [ MAXVALUEмакс_значение| NO MAXVALUE ] [ START [ WITH ]начало] [ CACHEкеш] [ [ NO ] CYCLE ] [ OWNED BY <имя_таблицы.имя_столбца| NONE > ]
Описание
CREATE SEQUENCE создаёт генератор последовательности. Эта операция включает создание и инициализацию специальной таблицы имя , содержащей одну строку. Владельцем генератора будет пользователь, выполняющий эту команду.
Если указано имя схемы, последовательность создаётся в заданной схеме, в противном случае — в текущей. Временные последовательности существуют в специальной схеме, так что при создании таких последовательностей имя схемы задать нельзя. Имя последовательности должно отличаться от имён других последовательностей, таблиц, индексов, представлений или сторонних таблиц, уже существующих в этой схеме.
После создания последовательности работать с ней можно, вызывая функции nextval , currval и setval . Эти функции документированы в Разделе 9.16.
Хотя непосредственно изменить значение последовательности нельзя, получить её параметры и текущее состояние можно таким запросом:
SELECT * FROM name;
В частности, поле last_value последовательности будет содержать последнее значение, выделенное для какого-либо сеанса. (Конечно, ко времени вывода это значение может стать неактуальным, если другие сеансы активно вызывают nextval .)
Параметры
TEMPORARY или TEMP
Если указано, объект последовательности создаётся только для данного сеанса и автоматически удаляется при завершении сеанса. Существующая постоянная последовательность с тем же именем не будут видна (в этом сеансе), пока существует временная, однако к ней можно обратиться, дополнив имя указанием схемы. IF NOT EXISTS
Не считать ошибкой, если отношение с таким именем уже существует. В этом случае будет выдано замечание. Заметьте, что нет никакой гарантии, что существующее отношение как-то соотносится с последовательностью, которая могла бы быть создана — это может быть даже не последовательность. имя
Имя создаваемой последовательности (возможно, дополненное схемой). шаг
Необязательное предложение INCREMENT BY шаг определяет, какое число будет добавляться к текущему значению последовательности для получения нового значения. С положительным шагом последовательность будет возрастающей, а с отрицательным — убывающей. Значение по умолчанию: 1. мин_значение
NO MINVALUE
Необязательное предложение MINVALUE мин_значение определяет наименьшее число, которое будет генерировать последовательность. Если это предложение опущено, либо указано NO MINVALUE , используется значение по умолчанию: 1 и -2 63 -1 для возрастающих и убывающих последовательностей, соответственно. макс_значение
NO MAXVALUE
Необязательное предложение MAXVALUE макс_значение определяет наибольшее значение. Если это предложение опущено, либо указано NO MAXVALUE , используется значение по умолчанию: 2 63 -1 и -1 для возрастающих и убывающих последовательностей, соответственно. начало
Необязательное предложение START WITH начало позволяет запустить последовательность с любого значения. По умолчанию началом считается мин_значение для возрастающих последовательностей и макс_значение для убывающих. кеш
Необязательное предложение CACHE кеш определяет, сколько чисел последовательности будет выделяться и сохраняться в памяти для ускорения доступа к ним. Минимальное значение равно 1 (за один раз генерируется только одно значение, т. е. кеширования нет), и оно же предполагается по умолчанию. CYCLE
NO CYCLE
Параметр CYCLE позволяет зациклить последовательность при достижении макс_значения или мин_значения для возрастающей или убывающей последовательности, соответственно. Когда этот предел достигается, следующим числом этих последовательностей будет соответственно мин_значение или макс_значение .
Если указывается NO CYCLE , при каждом вызове nextval после достижения предельного значения будет возникать ошибка. Если указания CYCLE и NO CYCLE отсутствуют, по умолчанию предполагается NO CYCLE . OWNED BY имя_таблицы . имя_столбца
OWNED BY NONE
Предложение OWNED BY позволяет связать последовательность с определённым столбцом таблицы так, чтобы при удалении этого столбца (или всей таблицы) последовательность удалялась автоматически. Указанная таблица должна иметь того же владельца и находиться в той же схеме, что и последовательность. Подразумеваемое по умолчанию предложение OWNED BY NONE указывает, что такая связь не устанавливается.
Замечания
Для удаления последовательности применяется команда DROP SEQUENCE .
Последовательности основаны на арифметике bigint , так что их значения не могут выходить за диапазон восьмибайтовых целых (-9223372036854775808 .. 9223372036854775807).
Так как вызовы nextval и setval никогда не откатываются, объекты последовательностей не подходят, если требуется обеспечить непрерывное назначение номеров последовательностей. Непрерывное назначение можно организовать, используя исключительную блокировку таблицы со счётчиком; однако это решение будет гораздо дороже, чем применение объектов последовательностей, особенно когда последовательные номера будут затребоваться сразу многими транзакциями.
Если значение параметра кеш больше единицы, и объект последовательности используется параллельно в нескольких сеансах, результат может оказаться не вполне ожидаемым. Каждый сеанс будет выделять и кешировать несколько очередных значений последовательности при одном обращении к объекту последовательности и соответственно увеличивать последнее_значение этого объекта. Затем при следующих кеш -1 вызовах nextval в этом сеансе будет просто возвращать заготовленные значения, не касаясь объекта последовательности. В результате, все числа, выделенные, но не использованные в сеансе, будут потеряны при завершении сеанса, что приведёт к образовании « дырок » в последовательности.
Более того, хотя разным сеансам гарантированно выделяются различные значения последовательности, если рассмотреть все сеансы в целом, порядок этих значений может быть нарушен. Например, при значении кеш , равном 10, сеанс A может зарезервировать значения 1..10 и получить nextval =1, затем сеанс B может зарезервировать значения 11..20 и получить nextval =11 до того, как в сеансе A сгенерируется nextval =2. Таким образом, при значении кеш , равном одному, можно быть уверенными в том, что nextval генерирует последовательные значения; но если кеш больше одного, рассчитывать можно только на то, что все значения nextval различны; их порядок может быть непоследовательным. Кроме того, last_value возвращает последнее зарезервированное значение для всех сеансов, вне зависимости от того, было ли оно уже возвращено функцией nextval .
Также следует учитывать, что действие функции setval , выполненной для такой последовательности, проявится в других сеансах только после того, как в них будут использованы все предварительно закешированные значения.
Примеры
Создание возрастающей последовательности с именем serial , с начальным значением 101:
CREATE SEQUENCE serial START 101;
Получение следующего номера этой последовательности:
SELECT nextval('serial'); nextval --------- 101
Получение следующего номера этой последовательности:
SELECT nextval('serial'); nextval --------- 102
Использование этой последовательности в команде INSERT :
INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
Изменение значения последовательности после COPY FROM :
BEGIN; COPY distributors FROM 'input_file'; SELECT setval('serial', max(id)) FROM distributors; END;
Совместимость
Команда CREATE SEQUENCE соответствует стандарту SQL , со следующими исключениями:
Описанное в стандарте выражение AS тип_данных не поддерживается.
Для получения следующего значения применяется функция nextval() , а не выражение NEXT VALUE FOR , как того требует стандарт.
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;