9.5. Понятие домена

Домен – это набор допустимых значений для одного или нескольких атрибутов. Если в таблице базы данных или в нескольких таблицах присутствуют столбцы, обладающие одними и теми же характеристиками, можно описать тип такого столбца и его поведение через домен, а затем поставить в соответствие каждому из одинаковых столбцов имя домена. Домен определяет все потенциальные значения, которые могут быть присвоены атрибуту.

Стандарт SQL позволяет определить домен с помощью следующего оператора:

CREATE DOMAIN <имя_домена> [AS] <тип_данных> [ DEFAULT <значение>]

[ CHECK (<допустимые_значения>)]

Каждому создаваемому домену присваивается имя, тип данных, значение по умолчанию и набор допустимых значений. Следует отметить, что приведенный формат оператора является неполным. Теперь при создании таблицы можно указать вместо типа данных имя домена.

Удаление доменов из базы данных выполняется с помощью оператора:

DROP DOMAIN <имя_домена> [ RESTRICT |CASCADE]

В случае указания ключевого слова CASCADE любые столбцы таблиц, созданные с использованием удаляемого домена, будут автоматически изменены и описаны как содержащие данные того типа, который был указан в определении удаляемого домена. Альтернативой доменам в среде SQL Server являются пользовательские типы данных.

Глава 10. Обобщенные табличные выражения (сте).

Многие проекты, над которыми трудятся разработчики, включают в себя написание сложных операторов SQL, отличающихся от операторов SELECT/FROM/WHERE базовых типов. В одном из таких случаев приходится внутри предложения FROM писать запросы, использующие производные таблицы. Общепринятая практика позволяет разработчику взять набор строк и непосредственно в операторе SELECT объединить эти строки с другими таблицами, представлениями и пользовательскими функциями. Другая возможность состоит в использовании представления вместо производной таблицы. Оба этих варианта имеют свои преимущества и недостатки.

При работе с SQL Server (начиная с версии 2005) существует третья возможность — использование обобщенных табличных выражений (CTE). Выражения CTE помогают повысить удобочитаемость (и, таким образом, возможность обслуживания) кода, не ухудшая производительности. Кроме этого, по сравнению с предыдущей версией SQL Server они значительно облегчают написание рекурсивного кода в T-SQL.

Синтаксис рекурсивного запроса (cтандарт SQL:1999):

WITH [ RECURSIVE ] <имя_запроса> [ ( <список столбцов> ) ] AS (<запрос select> ) <запрос, использующий имя_запроса>;

1. Представления, производные таблицы и выражения cte.

Выражения CTE могут оказаться полезными, когда запросам необходимо делать выборку из набора данных, не представленного в виде таблицы в БД. Например, может понадобиться написать запрос к набору агрегированных данных, в котором вычисляется стоимость блюд, зависящая от состава продуктов и трудозатрат на приготовление блюда. Агрегированные данные могут объединять таблицы Блюда, Состав, Наличие и вычислять сумму по каждому блюду. Может потребоваться выдать запрос к агрегированному набору строк (найти самое дешевое блюдо). Одно из решений заключается в том, чтобы сначала создать представление, которое собирает агрегированные данные, а затем написать запрос к этому представлению. Другая возможность состоит в выдаче запроса к агрегированным данным с помощью производной таблицы. Это реализуется перемещением оператора SQL в предложение FROM и выдаче запроса к нему.

Представления обычно используются для разбиения больших запросов, чтобы их можно было сформулировать в гораздо более удобочитаемом виде. Затем к представлению можно направить запрос с использованием других операторов SELECT в рамках всей базы данных. Такая абстракция обеспечивает более удобный доступ к набору строк, представленному представлением, и не возникает необходимости в дублировании данных или хранении их во временной таблице.

Представление может также повторно использоваться по всей базе данных при условии, что разрешения допускают это. Например, создается представление, определяющее стоимость все блюд:

CREATE VIEW

Цена (Блюдо, Стоимость)

AS

SELECT b.Блюдо,

cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость

FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )

JOIN Наличие n ON s.Продукт = n.Продукт

GROUP BY b.Блюдо, b.Труд;

после чего оно используется другим оператором, для нахождения самого дешевого блюда:

Результат
Блюдо	Цена
Кофе черный	2,33

SELECT Блюдо, Стоимость

FROM Цена

WHERE Стоимость = (SELECT MIN(Стоимость) FROM Цена);

Однако в тех ситуациях, когда требуется собрать данные для однократного использования, представления, возможно, будут не лучшим решением. Поскольку представление является объектом БД, который существует и является доступным в рамках всей БД всем пакетам (запросам, хранимым процедурам и т.д.), создание представления, используемого только одним пакетом T-SQL, – это явный перегиб.

Другая возможность состоит в создании производной таблицы. Производную таблицу можно создать простым перемещением оператора SELECT в предложение FROM, заключенного в круглые скобки. К ней можно обращать запросы или присоединяться к ней точно так же, как к таблице или к представлению.

SELECT Блюдо, Стоимость

FROM (SELECT b.Блюдо,

cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость

FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )

JOIN Наличие n ON s.Продукт = n.Продукт

GROUP BY b.Блюдо, b.Труд

) Цена

WHERE Стоимость =

(SELECT MIN( SELECT b.Блюдо,

SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость

FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )

JOIN Наличие n ON s.Продукт = n.Продукт

GROUP BY b.Блюдо, b.Труд)

FROM Цена1);

Данный код, обеспечивает тот же запрос, что и предыдущий, но вместо представления в нем используются производные таблицы. Хотя производные таблицы доступны только в рамках оператора, в котором они существуют, в общем случае таблицы усложняют прочтение и, вследствие этого, обслуживание запроса. Эта проблема умножается, если производную таблицу (как в нашем примере) требуется использовать многократно в пределах одного и того же пакета, поскольку для повторного использования производную таблицу потребуется копировать и вставлять.

В такой ситуации прекрасно подходит выражение CTE, поскольку оно обеспечивает большую удобочитаемость T-SQL (подобно представлению), но все-таки может использоваться более одного раза в запросе, непосредственно следующим за ним в этом же пакете. Безусловно, это невозможно за пределами указанной сферы действия. Кроме этого, CTE является конструкцией уровня языка — это означает, что SQL Server не создает внутренних временных или виртуальных таблиц. Запрос, лежащий в основе выражения CTE, можно вызывать каждый раз, когда на него появляется ссылка в следующем непосредственно за ним запросе.

Следовательно, эту же ситуацию можно описать с помощью выражения

WITH

Цена_СТЕ (Блюдо, Стоимость)

AS

(SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость

FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )

JOIN Наличие n ON s.Продукт = n.Продукт

GROUP BY b.Блюдо, b.Труд)

SELECT Блюдо, Стоимость

FROM Цена_СТЕ

WHERE Стоимость = (SELECT MIN(Стоимость) FROM Цена_СТЕ);

Цена_CTE собирает агрегированные данные и затем его использует запрос, непосредственно следующий за выражением CTE. Применение выражения CTE в коде, делает запрос удобочитаемым (подобно представлению), но не создает системный объект для хранения метаданных.

Выражение CTE начинается ключевым словом WITH. За ключевым словом WITH следует имя выражения CTE, за которым идет необязательный список псевдонимов столбцов. Псевдонимы столбцов соответствуют столбцам, возвращаемым оператором SELECT, входящим в выражение CTE. За необязательными псевдонимами столбцов следует ключевое слово AS — оно является обязательным. А за ключевым словом AS следует выражение запроса, определяющее выражение CTE, заключенное в круглые скобки.

Непосредственно за выражением CTE следует оператор SELECT, ссылающийся на выражение CTE посредством псевдонимов столбцов.

Поскольку выражение CTE предназначено для того, чтобы на него ссылался другой запрос, который затем может повторно обработать данные любым способом, запрос CTE не может содержать такие операторы, как ORDER и COMPUTE.

Выражение CTE не обязательно должно использоваться оператором SELECT; оно доступно для использования любому оператору, который ссылается на набор строк, генерируемый выражением CTE. Это означает, что за выражением CTE могут следовать операторы SELECT, INSERT, UPDATE или DELETE, использующие это выражение CTE.

Более того, за выражением CTE может следовать еще одно выражение CTE. Этот прием используется для построения выражения CTE на основе другого CTE, если в набор строк требуется собрать промежуточные результаты. При создании выражения CTE, строящегося на основе другого выражения CTE, определения выражений CTE разделяются запятой.

WITH

Цена (Блюдо, Стоимость) -- первое СТЕ

AS

(SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость

FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )

JOIN Наличие n ON s.Продукт = n.Продукт

GROUP BY b.Блюдо, b.Труд),

Мин_Цена(Стоимость) -- второе СТЕ

AS

(SELECT MIN(Стоимость) FROM Цена)

SELECT Блюдо, Цена .Стоимость --запрос, использующий СТЕ

FROM Цена JOIN Мин_Цена

ON Цена .Стоимость= Мин.Цена .Стоимость;

Этот запрос дает те же самые результаты, что и предыдущие варианты!