Что такое сиквенс в базе данных
Перейти к содержимому

Что такое сиквенс в базе данных

  • автор:

sys.sequences (Transact-SQL)

Содержит строку для каждого объекта последовательности в базе данных.

Имя столбца Тип данных Описание:
Наследует все столбцы из sys.objects.
start_value sql_variant НЕ NULL Стартовое значение для объекта последовательности. Если объект последовательности перезапускается с помощью инструкции ALTER SEQUENCE, он начинается с этого значения. Когда объект последовательности циклит, он переходит к minimum_value или maximum_value, а не к start_value.
increment sql_variant НЕ NULL Значение, на которое увеличивается значение объекта последовательности после каждого созданного значения.
minimum_value значение NULL sql_variant Минимальное значение, возвращаемое объектом последовательности. По достижении этого значения объект последовательности либо возвращает ошибку при попытке создать дополнительные значения, либо перезапускается, если для него указан параметр CYCLE. Если значение MINVALUE не указано, этот столбец возвращает минимальное значение, поддерживаемое типом данных генератора последовательностей.
maximum_value значение NULL sql_variant Максимальное значение, возвращаемое объектом последовательности. По достижении этого значения объект последовательности либо начинает возвращать ошибку при попытке создать дополнительные значения, либо перезапускается, если для него указан параметр CYCLE. Если параметр MAXVALUE не задан, этот столбец возвращает максимальное значение, допустимое типом данных объекта последовательности.
is_cycling bit NOT NULL Возвращает значение 0, если для объекта последовательности указан параметр NO CYCLE, и 1, если указан параметр CYCLE.
is_cached bit NOT NULL Возвращает значение 0, если для объекта последовательности указан параметр NO CACHE, и 1, если указан параметр CACHE.
cache_size int NULL Возвращает заданный размер кэша для объекта последовательности. Этот столбец содержит значение NULL, если последовательность была создана с параметром NO CACHE или был указан параметр CACHE без указания размера кэша. Если значение cache_size больше максимального числа значений, которые может возвращать объект последовательности, все равно показывается такой недостижимый размер кэша.
system_type_id tinyint NOT NULL Идентификатор системного типа для типа данных объекта последовательности.
user_type_id int NOT NULL Определенный пользователем идентификатор типа данных для объекта последовательности.
precision tinyint NOT NULL Максимальная точность типа данных.
scale tinyint NOT NULL Максимальный масштаб типа данных. Масштаб возвращается вместе с точностью для предоставления пользователю полных метаданных. Масштаб объектов последовательности всегда равен 0, поскольку для них допустимы только целочисленные типы.
current_value sql_variant НЕ NULL Последнее предоставленное значение. То есть значение, возвращаемое из последнего выполнения функции NEXT VALUE FOR или последнего значения при выполнении процедуры sp_sequence_get_range . Если последовательность не использовалась, возвращается значение START WITH.
is_exhausted bit NOT NULL 0 указывает, что последовательность еще может предоставлять новые значения. 1 указывает, что объект последовательности достиг значения MAXVALUE и для последовательности не задан параметр CYCLE. Функция NEXT VALUE FOR будет возвращать ошибку, пока последовательность не будет перезапущена с помощью инструкции ALTER SEQUENCE.
last_used_value значение NULL sql_variant Возвращает последнее значение, созданное функцией Next Value For . Применяется к SQL Server 2017 и более поздним версиям.

Разрешения

В SQL Server 2005 (9.x) и более поздних версиях видимость метаданных в представлениях каталога ограничена защищаемыми объектами, которыми владеет пользователь или которым пользователь получил некоторое разрешение. Дополнительные сведения см. в разделе Metadata Visibility Configuration.

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

Основные ключевые слова и параметры 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 — создать генератор последовательности

Синтаксис

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 , как того требует стандарт.

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

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