Генераторы последовательностей, идентифицирующие и генерируемые столбцы
В SQL:2003 появилась возможность определения нового вида объектов базы данных – генераторов последовательностей (sequence generators). Такого рода объекты производят изменяемые во времени точные числовые значения. Генераторы последовательностей могут оказаться полезными в разных контекстах среды SQL, но мы решили включить их обсуждение именно в этот подраздел по причине близкой связи со следующими обсуждаемыми в нем вопросами.
Для создания генератора последовательности в SQL:2003 введен оператор CREATE SEQUENCE. Он определяется следующими синтаксическими правилами:
CREATE SEQUENCE sequence_generator_name
[ sequence_generator_option_list> ]
sequence_generator_option ::=
AS data_type
| START WITH signed_numeric_literal
| INCREMENT BY signed_numeric_literal
| maxvalue_option
| minvalue_option
| cycle_option
maxvalue_option ::= MAXVALUE signed_numeric_literal
| NO MAXVALUE
minvalue_option ::= MINVALUE signed_numeric_literal
| NO MINVALUE
cycle_option ::= CYCLE
| NO CYCLE
Прежде всего, приведем несколько комментариев к синтаксическим правилам. Каждая разновидность опций оператора может входить в список опций не более одного раза. Если тип данных создаваемого генератора последовательности указывается явно, то он должен быть точным числовым типом со шкалой 0. В противном случае типом данных должен быть точный числовой тип со шкалой 0, выбираемый в реализации. Для остальных опций разумные значения по умолчанию также определяются в реализации (за исключением того, что значением инкремента по умолчанию является 1, а умолчание cycle_option
предполагает NO CYCLE). Как видно из синтаксических правил, при создании генератора последовательности можно указать минимальное и максимальное значения последовательности, стартовое значение, значение инкремента, а также то, должна ли являться последовательность циклической.
В каждый момент времени у генератора последовательности имеется текущее базовое значение и цикл, включающие все возможные значения между минимальным и максимальным значениями последовательности такие, что из можно выразить в форме (текущее_базовое_значение + M * инкремент), где M – некоторое натуральное число. Сразу после создания генератора последовательности текущим базовым значением генератора становится заданное стартовое значение.
В связи с генераторами последовательностей в SQL: 2003 введена новая встроенная функция NEXT VALUE FOR sequence_generator_name, вызов которой приводит к замене текущего базового значения указанного генератора на значение V, принадлежащее текущему циклу генератора и представимому в виде (текущее_базовое_значение + N * инкремент), где N – некоторое натуральное число. Результатом вызова функции является это число V. Например, если определить генератор последовательности следующим образом:
CREATE SEQUENCE SAMPLESEQ AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NO CYCLE ;
то последовательные вызовы NEXT VALUE FOR для SAMPLESEQ могут образовать последовательность значений 1, 2, 3, 4, …
При создании генератора последовательности можно указать опции CYCLE или NO CYCLE. Если указывается NO CYCLE, то при вызове NEXT VALUE FOR для данного генератора возбуждается исключительная ситуация, если функция пытается возвратить значение, не принадлежащее числовому интервалу между минимальным и максимальным значениями этого последовательностей этого генератора. Если же специфицируется CYCLE, то в такой ситуации функция возвращает минимальное значение последовательностей генератора, если значение инкремента положительно, и минимальное значение, если значение инкремента отрицательно (нулевые значения инкремента запрещены).
В SQL:2003 также специфицированы операторы ALTER SEQUENCE и DROP SEQUENCE. Оператор ALTER SEQUENCE позволяет изменять минимальное и максимальное значения, значение инкремента, а также изменять установку опции цикличности для указанного генератора последовательности. Кроме того, можно указать новое стартовое значение генератора последовательности (опция RESTART WITH). Тогда следующий за выполнением оператора ALTER SEQUENCE вызов функции NEXT VALUE FOR для данного генератора последовательности выдаст именно это новое стартовое значение.
Хотя генераторы последовательностей обеспечивают общий механизм генерации уникальных значений, непосредственное использование этой возможности достаточно громоздко – нужно явно создавать требуемый генератор и в нужное время вызывать функцию NEXT VALUE FOR. В SQL:2003 обеспечивается более специализированное общее средство идентифицирующих столбцов (identity columns), которое избавляет пользователей от излишних действий.
Идентифицирующий столбец в определении таблицы специфицируется в соответствии со следующими синтаксическими правилами:
identity_column_definition> ::=
column_name data_type
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( common_sequence_generator_option_list ) ]
[ column_constraint_definition_list ]
common_sequence_generator_option ::=
START WITH signed_numeric_literal
| INCREMENT BY signed_numeric_literal
| maxvalue_option
| minvalue_option
| cycle_option
Типом данных идентифицирующего столбца должен быть точный числовой тип со шкалой 0 или индивидуальный тип, основанный на таком числовом типе. Определение идентифицирующего столбца вызывает неявное определение для него ограничения NOT NULL NOT DEFERRABLE. В таблице можно определить не более одного идентифицирующего столбца.
Вот пример определения базовой таблицы с идентифицирующим столбцом:
CREATE TABLE PARTS (
PART_NO INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NO CYCLE ),
PART_DESCR VARCHAR (100),
PART_QUANTITY INTEGER );
При вставке строк в эту таблицу значение номера детали задавать не требуется (и даже нельзя, поскольку в спецификации соответствующего столбца указано GENERATED ALWAYS). Например, если выполнить оператор вставки
INSERT INTO PARTS (PART_DESCR, PART_QUANTITY) VALUES (‘BOLT’, 30);
то значение столбца PART_NO будет сгенерировано автоматически с использованием тех же правил, которые используются при генерации значений генераторов последовательностей. Если же при определении идентифицирующего столбца указать опцию GENERATED BY DEFAULT, то автоматическая генерация значений столбца будет производиться только в тех случаях, когда в операторе вставки не задано явное значение.
Конечно же, как и в случае генераторов последовательностей, можно изменить характеристики идентифицирующего столбца с помощью оператора ALTER TABLE.
Наконец, один или несколько столбцов определяемой базовой таблицы могут быть специфицированы как генерируемые столбцы (generated columns). Определение такого столбца подчиняется следующим синтаксическим правилам:
generated_column_definition> ::=
column_name [ data_type ]
GENERATED ALWAYS AS ( value_expression )
[ column_constraint_definition_list ]
Указываемое в определении столбца выражение должно строиться из констант и ссылок на основные (не генерируемые) столбцы определяемой таблицы. Если тип столбца явно указан, то он должен быть совместимым с типом задаваемого выражения. При отсутствии явного указания типа генерируемого столбца его типом считается тип задаваемого выражения.
При вставке строки в таблицу, содержащую генерируемый столбец, вычисляется ассоциированное с ним выражение, и полученное значение становится значением этого столбца во вставляемой строке. Предположим, что служащие получают зарплату и премиальные. Тогда можно определить следующую таблицу с генерируемым столбцом, значения которого будут характеризовать общий заработок служащих:
CREATE TABLE EMP (
EMP_NO INTEGER,
EMP_SAL DECIMAL(7,2),
EMP_BONUS DECIMAL(7,2),
EMP_TOTAL GENERATED ALWAYS AS (EMP_SAL + EMP_BONUS));
При выполнении оператора вставки строки
INSERT INTO EMP (EMP_NO, EMP_SAL, EMP_BONUS)
VALUES (4431, 50000.00, 5000.00);
путем вычисления выражения EMP_SAL + EMP_BONUS
будет автоматически сгенерировано значение столбца EMP_TOTAL, и в таблицу EMP будет занесена строка (4431, 50000.00, 5000.00, 55000.00). Конечно, во вставляемой строке нельзя явно указывать значение генерируемого столбца, но в соответствующей позиции можно указать DEFAULT.
При модификации строк таблицы производится соответствующее обновление значений генерируемых столбцов: ассоциированное с каждым генерируемым столбцом выражение вычисляется заново, и столбец получает соответствующее значение.
