Stfw.RuНаиболее интересные новшества в стандарте SQL:2003
SQL:2003 Сергей Кузнецов
🕛 26.09.2006, 12:24
В конце 2003 г. был принят и опубликован новый вариант международного стандарта SQL:2003 [1]. Многие специалисты считали, что в варианте стандарта, следующем за SQL:1999, будут всего лишь исправлены неточности SQL:1999. Но на самом деле, в SQL:2003 специфицирован ряд новых и важных свойств. Прежде всего, претерпела некоторые изменения общая организация стандарта. Стандарт SQL:2003 состоит из следующих частей:
9075-1, SQL/Framework;
9075-2, SQL/Foundation;
9075-3, SQL/CLI;
9075-4, SQL/PSM;
9075-9, SQL/MED;
9075-10, SQL/OLB;
9075-11, SQL/Schemata;
9075-13, SQL/JRT;
9075-14, SQL/XML.
Части 1-4 и 9-10 с необходимыми изменениями остались такими же, как и в SQL:1999. Часть 5 (SQL/Bindings) больше не существует; соответствующие спецификации включены в часть 2. Раздел части 2 SQL:1999, посвященный информационной схеме, выделен в отдельную часть 11. Появились две новые части - 13 и 14. Часть 13 полностью называется “SQL Routines and Types Using the Java Programming Language” (“Использование подпрограмм и типов SQL в языке программирования Java”). Появление такой отдельной части стандарта оправдано повышенным вниманием к языку Java со стороны ведущих производителей SQL-ориентированных СУБД. Наконец, последняя часть SQL:2003 посвящена спецификациями языковых средств, позволяющих работать с XML-документами в среде SQL. В этой статье мы ограничимся кратким обсуждением изменений и дополнений, произведенных комитетом по стандартизации SQL в части 2.
Как указывается в [2], наиболее серьезные изменения языка SQL, специфицированные в части 2 стандарта SQL:2003, касаются следующих аспектов:
типы данных;
подпрограммы, вызываемые из SQL;
расширенные возможности оператора CREATE TABLE;
новый объект схемы - генератор последовательностей;
новые виды столбцов - идентифицирующие столбцы (identity column) и генерируемые столбцы (generated column);
новый оператор MERGE;
В следующих разделах статьи1 мы кратко обсудим эти аспекты.
Новые типы данных
В SQL:2003 произошли некоторые изменения в системе типов SQL. Некоторые типы удалены, а другие добавлены. Среди новых типов наиболее важным, с точки зрения автора этой статьи, является конструктор типов мультимножеств; по этой причине его обсуждение выделяется в отдельный подраздел.
Битовые строки, целые числа и XML
В SQL:2003 исключена поддержка типов битовых строк BIT и BIT VARYING. Основанием является то, что эти типы не поддерживаются в существующих SQL-ориентированных СУБД, и компании-производители не собираются внедрять поддержку битовых строк в обозримом будущем. Так что типы битовых строк просуществовали в стандарте SQL очень недолго (они появились только в стандарте SQL:1999).
Введен новый точный числовой целый тип - BIGINT. Тип BIGINT аналогичен ранее существовавшим в SQL целым типам INTEGER и SMALLINT, но обладает большей (точнее, не меньшей) точностью. Хотя стандартом не предписывается конкретная точность типа INTEGER (как и для всех числовых типов, она определяется в реализации), в большинстве реализаций поддерживаются 32-х битовые целые значения этого типа. В этих реализациях обычно поддерживаются и 64-х битовые значения типа BIGINT. Однако стандарт оставляет другим реализациям свободу выбора точности этого нового типа. Для типа BIGINT обеспечивается тот же набор арифметических операций, что и для типов INTEGER и SMALLINT: “+”, “-“, ABS, MOD и т.д.
В части 14 стандарта SQL:2003 специфицируется специальный “тип XML” (XML type), значениями которого, по существу, являются XML-документы. Для этого типа определяется ряд операций, обеспечивающих доступ к элементам значения типа XML, преобразования этих значений и т.д. Заметим, что тип XML вообще не упоминается в базовой второй части стандарта. Да и часть 14 пока не производит впечатление зрелого набора спецификаций.
Конструктор типов мультимножества
В стандарте SQL:1999 допускалась возможность использования только одного вида коллекций - динамических массивов с элементами любого допустимого в SQL типа, кроме типа массива. Тип массива образовывался с помощью конструктора типов массивов ARRAY.
Стандарт SQL:2003 расширяет возможности использования коллекций в двух важных направлениях. Во-первых, вводится новый конструктор типов мультимножеств MULTISET. Во-вторых, типом элементов любого типа коллекций теперь может быть любой допустимый в SQL тип данных, кроме самого конструируемого типа коллекции. Оба эти расширения качественно влияют на возможную природу организации SQL-ориентированных баз данных и на способы работы с этими базами данных. Мы остановимся на этом несколько более подробно в конце данного подраздела после обсуждения конкретных свойств типов мультимножеств.
При определении местоположения (например, столбца таблицы) типа мультимножеств используется конструкция dt MULTISET, где dt задает тип данных элементов конструируемого типа мультимножеств. Значениями типа мультимножеств являются мультимножества, т.е. неупорядоченные коллекции элементов одного и того же типа, среди которых допускаются дубликаты. Например, значениями типа INTEGER MULTISET являются мультимножества, элементами которых являются целые числа. Примером такого значения может быть мультимножество {12, 34, 12, 45, -64}.
В отличие от массива, мультимножество является неограниченной коллекцией; при конструировании типа мультимножеств не указывается предельная кардинальность значений этого типа. Однако это не означает, что возможность вставки элементов в мультимножество действительно не ограничена; стандарт всего лишь не требует наличия границы. Ситуация аналогична той, которая возникает при работе с таблицами, для которых в SQL не объявляется максимально допустимое число строк.2
Значения-мультимножества создаются путем использования специальной конструкции SQL:2003, называемой конструктором значений-мультимножеств (multiset value constructor). Эта конструкция определяется следующими синтаксическими правилами:
multiset_value_constructor ::= multiset_value_constructor_by_enumeration> | multiset_value_constructor_by_query> | table_value_constructor_by_query multiset_value_constructor_by_enumeration ::= MULTISET left_bracket value_expression_commalist right_bracket multiset_value_constructor_by_query ::= MULTISET ( query_expression) table_value_constructor_by_query ::= TABLE ( query_expression> )
Например, следующие выражения являются допустимыми значениями-мультимножествами: MULTISET [14, 16, 17] или MULTISET (SELECT DEPT_NO FROM EMP). Второй случай демонстрирует возможность преобразования таблицы в мультимножество3. Допускается и использование значения-мультимножества в качестве ссылки на таблицу в разделе FROM запроса. Для этого к значению-мультимножеству применяется операция UNNEST. Вот простой пример допустимого запроса:
SELECT T.A, T.A + 2 AS PLUS_TWO FROM UNNEST(MULITISET [14,16,17]) AS T(A) В результате выполнения запроса будет получена таблица A PLUS_TWO 14 16 16 18 17 19
Для типов мультимножеств поддерживаются операции для преобразования типа значения-мультимножества к типу массивов или другому типу мультимножеств с совместимым типом элементов (операция CAST), для удаления дубликатов из мультимножества (функция SET), для определения числа элементов в заданном мультимножестве (функция CARDINALITY), для выборки элемента мультимножества, содержащего в точности один элемент (функция ELEMENT). Кроме того, для мультимножеств обеспечиваются операции объединения (MULTISET UNION), пересечения (MULTISET INTERSECT) и определения разности (MULTISET EXCEPT). Каждая из операций может выполняться в режиме с сохранением дубликатов (режим ALL) или с устранением дубликатов (режим DISTINCT).
Наконец, введены три новые агрегатные функции. Агрегатная функция COLLECT создает мультимножество из значений аргумента в каждой строке группы строк. Функция FUSION производит объединение значений-мультимножеств из всех строк группы строк. Функция INTERSECT производит пересечение значений-мультимножеств из всех строк группы строк. Покажем на простом примере, как работают эти агрегатные функции. Пусть имеется таблица PROGRAMMERS следующего вида:
PROGRAMMER FAVOURITE_LANGUAGES ‘Smith’ MULTISET [‘Java’, ‘Pascal’, ‘Perl’] ‘Brown’ MULTISET [‘Python’, ‘C++’, ‘Java’] ‘Scott’ MULTISET [‘Python’, ‘Java’]
Тогда в результате запроса
SELECT COLLECT(PROGRAMMER) AS ALL_PROGRAMMERS, FUSION(FAVOURITE_LANGUAGES) AS ALL_LANGUAGES INTERSECT(FAVOURITE_LANGUAGES) AS COMMON_LANGUAGES FROM PROGRAMMERS будет получена следующая таблица с одной строкой, все три столбца которой содержат значения-мультимножества: ALL_PROGRAMMERS ALL_LANGUAGES COMMON_LANGUAGES MULTISET [‘Smith’, ‘Brown’ ‘Scott’ MULTISET [‘Java’, ‘Pascal’, ‘Perl’ ‘Python’, ‘C++’, ‘Java’ ‘Python’, ‘Java’] MULTISET [‘Java’]
При использовании мультимножеств в условных выражениях можно применять следующие предикаты:
сравнения по равенству (“=”);
сравнения по неравенству (“<>”);
проверки того, что заданное значение является элементом мультимножества (MEMBER);
проверки того, что одно мультимножество входит в другое мультимножество (SUBMULTISET);
проверки того, что мультимножество содержит дубликаты (IS A SET).
После короткого рассмотрения особенностей типов мультимножеств в SQL:2003 обсудим, почему мы считаем отмеченные в начале подраздела расширенные возможности работы с типами коллекций принципиально важными. Дело в том, что даже при наличии определяемых пользователями типов данных и типов массивов SQL:1999 (см. [3]) не предоставлял полных возможностей для преодоления исторически присущего реляционной модели данных вообще и SQL, в частности, ограничения “плоских таблиц”. Теперь, после появления конструктора типов мультимножеств и устранения ограничений на тип данных элементов коллекции, это историческое ограничение полностью ликвидировано.
Конечно же, мультимножество, типом элементов которого является анонимный строчный тип4 является полным аналогом таблицы. Тем самым, в базе данных допускается произвольная вложенность таблиц. Возможности выбора структуры базы данных безгранично расширяются.
Другой вопрос, принесут ли эти новые возможности существенную практическую пользу разработчикам и пользователям SQL-ориентированных баз данных? Как это обычно бывает в случае SQL, на этот вопрос трудно ответить однозначно. Скорее всего, большинство разработчиков, традиционно работающих в SQL-среде, просто не будет использовать новые средства, как не использует и объектно-реляционные расширения SQL. Но возможно, что расширенная поддержка типов коллекций привлечет к SQL-ориентированным СУБД новую категорию разработчиков и пользователей из числа, например, тех, которые традиционно использовали объектно-ориентированную или какую-либо другую среду, отличную от SQL.
Между прочим, как кажется автору этой статьи, на введение типов мультимножеств в SQL:2003 оказали влияние работы Дейта и Дарвена [3]. Конечно, SQL:2003 остается языком SQL со всеми присущими ему недостатками. Конечно, предложения Третьего манифеста, выглядят изящнее и стройнее, чем то, что появилось в SQL. Но похоже, что с использованием SQL:2003 теперь можно добиться почти тех же результатов, которые обеспечило бы применение какого-либо языка D.
Табличные функции
В SQL:2003 поддерживается механизм табличных функций, т.е. функций, вызываемых из SQL и возвращающих значение-“таблицу”. В связи с отсутствием поддержки в SQL “типа таблиц” типом результата табличной функции в действительности является тип мультимножеств (типом элементов которого является соответствующий анонимный строчный тип). Однако к результату табличной функции можно адресовать запросы таким же образом, как и к таблице.
Разработчики стандарта SQL:2003 полагают, что табличные функции полезны сами по себе, вне связи с поддержкой общего механизма мультимножеств. По этой причине стандарт не требует поддержки мультимножеств в качестве обязательного условия поддержки табличных функций.
Происхождение термина “табличная функция” очевидно. В синтаксических конструкциях определения и вызова табличных функций их близость с таблицами подчеркивается требованием наличия ключевого слова TABLE в различных местах выражений. Например, в разделе RETURN определения табличной функции указывается ключевое слово TABLE, вслед за которым перечисляются пары имя_столбца/тип_данных. На рис. 1 приведены определения двух табличных функций, первая из которых является внешней, а вторая содержит тело, определяемое на языке SQL.
CREATE FUNCTION timetable () RETURNS TABLE ( CINEMA_NAME VARCHAR (20), MOVIE_NAME VARCHAR (4), TIME_TABLE TIME ) NOT DETERMINISTIC NO SQL LANGUAGE C EXTERNAL PARAMETER STYLE SQL
(a) Определение внешней табличной функции
CREATE FUNCTION DEPTEMP (DEPTNO INTEGER) RETURNS TABLE ( EMPNO INTEGER, EMPNAME VARCHAR (20)) LANGUAGE SQL READS SQL DATA DETERMINISTIC RETURN TABLE ( SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP.DEPT_NO = DEPTEMP.DEPTNO )
(b) Определение табличной функции с телом, задаваемым на языке SQL
Внешние табличные функции позволяют запрашивать данные, которые не хранятся в базовых таблицах и являются внешними по отношению к базе данных. Например, табличная функция, определение которой приведено на рис. 1a, возвращает множество строк, представляющих кинотеатры, названия демонстрируемых в них фильмов и расписание сеансов.
В определении внешней функции может содержаться ряд опций, которые влияют на ожидаемое поведение функции. В нашем случае функция реализуется на языке C (для краткости, на рис. A.1a не приведен код тела функции). Об этом факте свидетельствует наличие раздела LANGUAGE C, который определяет конкретный способ передачи параметров. Наличие раздела NO SQL говорит о том, при выполнении функции не будут производиться обращения к SQL-процессору для обработки операторов SQL. Этот факт нужно учитывать при управлении транзакциями. Наличие раздела NOT DETERMINISTIC означает, что функция может возвращать разные результаты при разных вызовах с одним и тем же набором аргументов. Знание этого факта влияет на работу оптимизатора SQL-запросов. Наконец, наличие раздела PARAMETER STYLE SQL говорит о том, что аргументы и результаты функции могут являться неопределенными значениями.
С другой стороны, табличные функции с телом, специфицируемым на языке SQL, представляют собой параметризованные представления (parameterized views (обычные представления в SQL фиксируются в момент создания). Например, у табличной функции, определение которой приведено на рис. 1b, имеется один параметр - DEPTNO. Этот параметр используется в разделе WHERE запроса в теле функции и определяет результирующее множество строк. Разные значения аргумента вызова функции приведут к разным возвращаемым ее множествам строк (строк служащих, работающих в указываемом отделе).
И в этом случае в определении функции могут содержаться необязательные разделы, влияющие на поведение функции. Наличие раздела LANGUAGE SQL говорит о том, что тело функции написано на языке SQL (в нашем случае тело состоит из единственного оператора RETURN). Раздел READS SQL DATA означает, что доступ к данным, хранимым в базе данных, будет производиться в режиме только чтения. Наличие раздела DETERMINISTIC свидетельствует о том, что функция возвращает один и тот же результат при наличии одних и тех же аргументов и одного и того же состояния базы данных.
Наиболее естественным местом вызова табличной функции является раздел FROM оператора выборки (хотя табличные функции могут вызываться и во многих других контекстах). В этом случае вызов функции предваряется ключевым словом TABLE и может встречаться везде, где может присутствовать ссылка на таблицу. Ниже приведен простой пример запроса, в котором используется вызов табличной функции (“Получить названия кинотеатров, в которых демонстрируется фильм «Властелин колец. Две крепости», и расписание сеансов):
SELECT CINEMA_NAME, TIME_TABLE
FROM TABLE(timetable ()) AS TT
WHERE TT.MOVIE_NAME = ‘The Lord of Rings: Two Towers’;
Расширенные возможности оператора CREATE TABLE
В SQL:2003 возможности оператора SQL CREATE TABLE, предназначенного для определения базовых таблиц, существенно расширены, и в этом разделе мы кратко обсудим эти расширения.
Раздел LIKE оператора CREATE TABLE
Одной из возможностей SQL:1999 являлась возможность определения новой базовой таблицы, подобной (like) одной или нескольким существующим таблицам. В форме, специфицированной в стандарте SQL:1999, она не представляла существенного интереса. Однако, чтобы пояснить суть расширения, введенного в SQL:2003, мы должны сначала обсудить исходный вариант.
Итак, в SQL:1999 в списке элементов определения таблицы оператора CREATE TABLE, помимо определений столбцов и табличных ограничений, могли присутствовать элементы вида LIKE table_name. Наличие одного или нескольких подобных разделов в определении новой таблицы приводило к копированию в ее определении структуры указанных существующих таблиц. Однако при этом копировались только имена столбцов указанных таблиц и типы данных этих столбцов. Другими словами, выполнялась примитивная макроподстановка. Пример определения таблицы с использованием раздела LIKE показан на рис. 2.
CREATE TABLE T1 ( C1 VARCHAR (100) NOT NULL DEFAULT ‘TEST’, C2 INTEGER ); CREATE TABLE T2 ( LIKE T1, C3 CHAR (20)); CREATE TABLE T3 ( C1 VARCHAR (100), C2 INTEGER, C3 CHAR (20));
Как показывает рис. 2, определение таблицы T2, полученное с использованием раздела LIKE, эквивалентно определению таблицы T3. В определении таблицы T2 утрачена некоторая информация, присутствующая в определении T1: в столбце C1 не допускается наличие неопределенных значений, и для него указано значение по умолчанию.
Хотя и вариант раздела LIKE, специфицированный в SQL:1999, может быть полезен на практике при определении похожих таблиц с большим числом столбцов, в SQL:2003 введены некоторые необязательные расширения, позволяющие управляемым образом копировать больше информации о столбцах существующих таблиц. Более точно, в SQL:2003 раздел LIKE имеет следующую синтаксическую форму:
like_clause ::= LIKE table_name [ like_option_list ]
like_option ::= INCLUDING IDENTITY | EXCLUDING IDENTITY
| INCLUDING DEFAULTS | EXCLUDING DEFAULTS
| INCLUDING GENERATED | EXCLUDING GENERATED
Свойства IDENTITY и GENERATED - это новые свойства столбцов, введенные в SQL:2003. Мы обсудим их ниже в этом разделе. Пока же ограничимся примером определения таблицы с использованием раздела LIKE в стиле SQL:2003, в котором копируется информация о значении столбца по умолчанию:
CREATE TABLE T4 (
LIKE T1,
INCLUDING DEFAULTS);
Таблица T4 теперь имеет в точности ту же структуру, что и таблица T1. При отсутствии опций раздел LIKE ведет себя так же, как и в SQL:1999, за тем исключением, что свойство NOT NULL столбца всегда копируется. Заметим также, что выполнение оператора CREATE TABLE с разделом LIKE не приводит к образованию какой-либо зависимости между новой таблицей и той, которая использовалась в разделе LIKE (т.е. их структура может независимо изменяться).
Раздел AS оператора CREATE TABLE
Использование раздела LIKE в операторе CREATE TABLE полезно, если в определение новой таблицы требуется включить полную копию структуры одной или нескольких существующих таблиц. Однако в некоторых случаях оказывается полезным скопировать только часть структуры существующих таблиц, а в общем случае - создать таблицу по образу некоторого выражения запросов.
Для этих целей предназначена расширенная форма оператора CREATE TABLE, включающая раздел AS. В этом случае используется следующая синтаксическая форма оператора CREATE TABLE:
CREATE TABLE table_name [ column_name_commalist ]
AS subquery { WITH NO DATA | WITH DATA }
При выполнении подобного оператора создается таблица со структурой (именами столбцов, их типами данных и т.д.) выражения запросов. Допускаются также возможности переименования столбцов и занесения в создаваемую таблицу строк, генерируемых при выполнении выражения запроса.
Выполнение оператора CREATE TABLE с разделом AS не приводит к порождению зависимости новой таблицы от определяющего ее выражения запросов. После начального заполнения новой таблицы обновления таблиц, над которыми задано выражение запросов, не будут автоматически отражаться в состоянии новой таблицы.
Генераторы последовательностей, идентифицирующие и генерируемые столбцы
В 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.
При модификации строк таблицы производится соответствующее обновление значений генерируемых столбцов: ассоциированное с каждым генерируемым столбцом выражение вычисляется заново, и столбец получает соответствующее значение.
Новый оператор обновления базы данных MERGE
В приложениях SQL-ориентированных баз данных часто возникает потребность в передаче множества строк из таблицы, обновлявшейся при выполнении транзакции (транзакционной таблицы), в некоторую основную таблицу базы данных. Обычно транзакционная таблица содержит обновленные варианты строк, существующих в основной таблице, а также, возможно, новые строки, которые должны быть занесены в основную таблицу. При наличии традиционных средств обновления базы данных содержимое транзакционной таблицы может быть перенесено в основную таблицу путем выполнения двух отдельных шагов. На первом шаге требуется выполнить оператор UPDATE для всех строк основной таблицы, для которых имеются модифицированные “двойники” в транзакционной таблице. Затем нужно выполнить оператор INSERT для занесения в основную таблицу всех строк транзакционной таблицы, для которых таких двойников нет. Оператор MERGE, введенный в SQL:2003, позволяет выполнить такую операцию за один шаг, более эффективный и проще специфицируемый. Вот общий синтаксис этого нового оператора в немного упрощенной форме:
MERGE INTO table_name [ [ AS ] correlation_name ]
USING table_reference
ON conditional_expression merge_operation_specification
merge_operation_specification ::=
{ merge_when_matched_clause | merge when not matched clause }
| merge_when_matched_clause merge_when_not_matched_clause
| merge_when_not_matched_clause merge_when_matched_clause
merge_when_matched_clause ::=
WHEN MATCHED THEN UPDATE SET update_assignment_commalist
merge_when_not_matched_clause ::=
WHEN NOT MATCHED THEN INSERT [ ( column_commalist ) ]
VALUES ( value_expression_commalist )
Как видно из синтаксиса, в операторе обязательно содержится условное выражение и, по крайней мере, один из разделов “слияния при наличии сопоставления” (merge_when_matched_clause) и “слияния при отсутствии сопоставления” (merge when not matched clause). Пусть T1 обозначает таблицу, указанную в разделе USING, а T2 - имя таблицы, указанной в разделе INTO. Тогда семантика выполнения операции определяется следующим образом:
Строки таблицы просматриваются в некотором порядке. Пусть R1 - очередная строка T1. Для этой строки вычисляется условное выражение. В этом условном выражении могут присутствовать имена столбцов таблиц T1 и T2 (формально при построении этого выражения можно использовать все предикаты, допустимые в условном выражении раздела WHERE оператора SELECT).
Если значением условного выражения является true, то
Если в операторе содержится раздел “слияния при наличии сопоставления”, то
Множество строк {R2} таблицы T2, для которых удовлетворяется это условие, должно содержать ровно одну строку (иначе во время выполнения оператора генерируется исключительная ситуация);
Эта строка подвергается модификации в соответствии со спецификацией раздела SET (хотя это явно не требуется синтаксисом, разумно предположить, что в выражениях списка присваиваний раздела SET будут указываться имена столбцов таблицы T1, т.е. что строка R2 будет модифицироваться на основе значений столбцов строки R1).
В противном случае строка R1 игнорируется.
Если значением условного выражения не является true, то
Если в операторе содержится раздел “слияния при отсутствии сопоставления”, то в таблицу T2 вставляется строка, специфицируемая списком выражений раздела INSERT (хотя это явно не требуется синтаксисом, разумно предположить, что в выражениях раздела INSERT будут указываться имена столбцов таблицы T1, т.е. что строка, заново вставляемая в таблицу T2, будет формироваться на основе значений столбцов строки R1).
В противном случае строка R1 игнорируется.
Следует сразу заметить, что оператор MERGE в том виде, в каком он специфицирован в стандарте SQL:2003, производит не вполне хорошее впечатление. Во-первых, то требование, что при задаваемом условии сопоставления каждой строке таблицы T1 должна соответствовать не более чем одна строка таблицы T2, не подкрепляется каким-либо явными синтаксическими ограничениями для этого условия. В результате на самих пользователей возложена нетривиальная задача - убедиться в том, что задаваемое ими условие сопоставления действительно соответствует этому требованию.5 Во-вторых, очевидным образом не просматриваются разумные способы использования оператора MERGE, отличные от тривиальных случаев.
Приведем пример тривиального (хотя и вполне полезного и осмысленного) использования оператора. Предположим, что в базе данных предприятия поддерживается сводная таблица INVENTORY (инвентарная ведомость), содержащая данные обо всех деталях, которые имеются на предприятии. Дополнительные детали поступают на предприятие путем поставок от поставщиков, причем каждой поставке соответствует транзакционная таблица SHIPMENT. В завершение транзакции поставки требуется “перелить” данные из таблицы SHIPMENT в таблицу INVENTORY. Пусть таблицы SHIPMENT и INVENTORY имеют одну и ту же структуру
INVENTORY
PART_NO
PART_NAME
PART_QUANTITY
1
Bolt
5
2
Screw
10
3
Nut
30
SHIPMENT
PART_NO
PART_NAME
PART_QUANTITY
1
Bolt
5
4
Nail
10
3
Nut
30
MERGE INTO INVENTORY ...
PART_NO
PART_NAME
PART_QUANTITY
1
Bolt
10
2
Screw
10
3
Nut
60
4
Nail
10
В обеих таблицах столбец PART_NO является первичным ключом, а столбец PART_QUANTITY содержит данные о числе деталей в инвентарной ведомости или в поставке. Тогда в результате выполнения приведенного ниже оператора MERGE таблица INVENTORY примет вид, показанный в нижней части рис. 3.
MERGE INTO INVENTORY AS INV
USING (SELECT PART_NO, PART_NAME, PART_QUANTITY FROM SHIPMENT) AS SH
ON (INV.PART_NO = SH.PART_NO)
WHEN MARCHED THEN UPDATE
SET PART_QUANTITY = INV.PART_QUANTUTY + SH.PART_QUANTITY
WHEN NOT MARCHED THEN INSERT
(PART_NO, PART_NAME, PART_QUANTITY)
VALUES (SH.PART_NO, SH.PART_NAME, SH.PART_QUANTITY) ;
Заключение
В этой статье мы кратко обсудили некоторые интересные новые возможности языка SQL, специфицированные в стандарте SQL:2003. Заметим, что новые средства языка не связаны напрямую с объектно-реляционными расширениями [3]. Однако введение, например, конструктора типа мультимножества и снятие ограничений на тип элементов типов массива и мультимножества открывают массу новых возможностей для применения языка, в том числе, и в объектном стиле.
Литература
1. Во время написания этой статьи текст стандарта SQL:2003 был доступен на сайте http://www.wiscorp.com/sql/sql_2003_standard.zip.
2. Andrew Eisenberg, Jim Melton, Krishna Kulkarni, Jan-Eike Michels, Fred Zemke. SQL:2003 Has Been Published. ACM SIGMOD Record 33, No. 1 (March 2004).
3. С.Д. Кузнецов. Три манифеста баз данных: ретроспектива и перспективы. http://www.citforum.ru/database/articles/manifests/
1 Статья основана на приложении к книге С.Д. Кузнецова с условным названием “Базы данных: модели и языки”, которая готовится к изданию в издательстве “Бином”.
2 Конечно, на практике такие ограничения устанавливаются в документации конкретной используемой СУБД, либо даже администратором конкретной базы данных.
3 Конечно, результатом выполнения оператора выборки в SQL всегда является таблица.
4 Анонимный строчный тип - это конструктор типов ROW, позволяющий производить безымянные типы строк (кортежей). Любой возможный строчный тип получается путем использования ROW. При определении столбца, значения которого должны принадлежать некоторому строчному типу, используется конструкция ROW ( fld1, fld2, …, fldn ), где каждый элемент fldi, определяющий поле строчного типа, задается в виде тройки fldname, fldtype, fldoptions. Подэлемент fldname задает имя соответствующего поля строчного типа. Подэлемент fldtype специфицирует тип данных этого поля. В качестве типа данных поля строчного типа можно использовать любой допустимый в SQL тип данных, включая другие строчные типы. Необязательный подэлемент fldoptions может задаваться для указания применяемого по умолчанию порядка сортировки, если соответствующий подэлемент fldtype указывает на тип символьных строк, а также должен задаваться, если fldtype указывает на ссылочный тип. Степенью строчного типа называется число его полей.
5 Заметим также, что одно и то же условие может соответствовать указанному требованию при одном наборе строк таблиц T1 и T2 и не соответствовать в других ситуациях.