Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
2. Дано представления с вычислениями.
CREATE VIEW view2
AS
SELECT Название_блюда, Тип_блюда, Стоимость,
Стоимость*0.05 AS Налог
FROM Блюдо
WHERE Стоимость>100
Предпринимается попытка изменить в представлении
запись.
UPDATE view2 SET Стоимость=Стоимость*1.5
WHERE Тип_блюда='Десерт'
Результатом выполнения команды является следующее:
в таблице Блюдо будет изменена стоимость десертов дороже
100 руб.
в таблице Блюдо будет изменена стоимость десертов
стоимость десертов в представлении view2 будет успешно
изменена
команда не будет выполнена
3. Дано представления с данными из разных таблиц.
CREATE VIEW view3
AS
SELECT Блюдо.Название_блюда,
Компонент.Название_компонента, Компонент.Вес
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо;
Предпринимается попытка добавить в представление
запись.
INSERT INTO view3(Название_блюда,
Название_компонента, Вес)
VALUES('Щи','Капуста',0.350)
Результатом выполнения команды является следующее:
в таблицы Блюдо и Компонент будут добавлены записи
запись будет добавлена только в таблицу Блюдо
данные в представление view3 будут успешно добавлены
команда не будет выполнена
231
Лекция 10 Представления
4. Дано представление с группировкой.
CREATE VIEW view4
AS
SELECT Повар, Sum(Стоимость) AS Общ_Стоимость
FROM Блюдо
GROUP BY Повар
HAVING Повар=”Иванов”
Предпринимается попытка удалить записи:
DELETE FROM view4
WHERE Повар=’Петров’
из таблицы Блюдо будут удалены блюда, приготовленные
поварами Ивановым и Петровым
из таблицы Блюдо будут удалены блюда, приготовленные
поваром Петровым
команда будет выполнена. Представление не изменится
команда выполнена не будет
Курс
232
Основы SQL
Вариант 4
1. Дано представление.
CREATE VIEW view1
AS
SELECT КодГорода, Название, Тариф
FROM Город
WHERE Регион=’Поволжье’
WITH CHECK OPTION
Предпринимается попытка выполнить команду вставки
данных в представление.
INSERT INTO view1(КодГорода, Название, Тариф)
VALUES (842,'Симбирск',2.70)
Результатом выполнения команды (при отсутствии каких-
либо ограничений при создании таблицы) является
следующее:
в таблицу Город будет добавлена запись
в таблицу Город будет добавлена запись
данные в представление view1 будут успешно добавлены
команда не будет выполнена
233
Лекция 10 Представления
КодГорода Название Тариф Регион
842 Симбирск 2.70 Поволжье
КодГорода Название Тариф Регион
842 Симбирск 2.70 NULL
2. Дано представления с вычислениями.
CREATE VIEW view2
AS
SELECT Название, Регион, Тариф, Тариф*0.05
AS Налог
FROM Город
WHERE Город.Тариф>10
Предпринимается попытка изменить в представлении
запись.
UPDATE view2 SET Тариф=Тариф*1.5
WHERE Регион=’Поволжье’
Результатом выполнения команды является следующее:
в таблице Город будет изменен тариф на телефонные разговоры
с городами Поволжья, если он больше 10 руб.
в таблице Город будет изменен тариф на телефонные разговоры
с городами Поволжья
тарифы на телефонные разговоры с городами Поволжья
в представлении view2 будут успешно изменены
команда не будет выполнена
Курс
234
Основы SQL
3. Дано представления с данными из разных таблиц.
CREATE VIEW view3
AS
SELECT Город.Название, Разговор.Фамилия,
Разговор.Продолжительность
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода;
Предпринимается попытка добавить в представление
запись.
INSERT INTO view3(Название, Фамилия,
Продолжительность)
VALUES('Самара','Иванов',10)
Результатом выполнения команды является следующее:
в таблицы Город и Разговор будут добавлены записи
запись будет добавлена только в таблицу Разговор
данные в представление view3 будут успешно добавлены
команда не будет выполнена
4. Дано представление с группировкой.
CREATE VIEW view4
AS
SELECT Регион, Avg(Тариф) AS Средн_Тариф
FROM Город
GROUP BY Регион
HAVING Регион=’Поволжье’
Предпринимается попытка удалить записи:
DELETE FROM view4
WHERE Регион=’Урал’
из таблицы Город будет удалена информация о городах
Поволжья и Урала
из таблицы Город будет удалена информация о городах Урала
команда будет выполнена. Представление не изменится
команда выполнена не будет
235
Лекция 10 Представления
Курс
236
Основы SQL
Лекция 11. Функции пользователя
Дается определение функций пользователя, приводятся примеры их
создания и использования. Рассматриваются различные типы функций.
Анализируются встроенные функции языка SQL.
Ключевые слова: хранимая процедура, функция пользователя, скаляр-
ная функция пользователя, входной параметр, табличная функция
пользователя, табличный тип данных, мультитабличная функция,
встроенные функции, математические функции, строковые функции,
функции даты/времени.
Понятие функции пользователя
При реализации на языке SQL сложных алгоритмов, которые могут
потребоваться более одного раза, сразу встает вопрос о сохранении разра-
ботанного кода для дальнейшего применения. Эту задачу можно было бы
реализовать с помощью хранимых процедур, однако их архитектура не
позволяет использовать процедуры непосредственно в выражениях, т.к.
они требуют промежуточного присвоения возвращенного значения пере-
менной, которая затем и указывается в выражении. Естественно, подоб-
ный метод применения программного кода не слишком удобен. Многие
разработчики уже давно хотели иметь возможность вызова разработанных
алгоритмов непосредственно в выражениях.
Возможность создания пользовательских функций была предоста-
влена в среде MS SQL Server 2000. В других реализациях SQL в распоря-
жении пользователя имеются только встроенные функции, которые
обеспечивают выполнение наиболее распространенных алгоритмов:
поиск максимального или минимального значения и др.
Функции пользователя представляют собой самостоятельные объек-
ты базы данных, такие, например, как хранимые процедуры или триггеры.
Функция пользователя располагается в определенной базе данных и дос-
тупна только в ее контексте.
В SQL Server имеются следующие классы функций пользователя:
• Scalar – функции возвращают обычное скалярное значение, каждая
может включать множество команд, объединяемых в один блок с
помощью конструкции BEGIN…END;
• Inline – функции содержат всего одну команду SELECT и возвраща-
ют пользователю набор данных в виде значения типа данных table;
• Multi-statement – функции также возвращают пользователю значение
типа данных table, содержащее набор данных, однако в теле функции
237
Лекция 11 Функции пользователя
находится множество команд SQL (INSERT, UPDATE и т.д.). Именно с
их помощью и формируется набор данных, который должен быть воз-
вращен после выполнения функции.
Пользовательские функции сходны с хранимыми процедурами, но,
в отличие от них, могут применяться в запросах так же, как и системные
встроенные функции. Пользовательские функции, возвращающие таб-
лицы, могут стать альтернативой просмотрам. Просмотры ограничены
одним выражением SELECT, а пользовательские функции способны
включать дополнительные выражения, что позволяет создавать более
сложные и мощные конструкции.
Функции Scalar
Создание и изменение функции данного типа выполняется с помо-
щью команды:
<определение_скаляр_функции>::=
{CREATE | ALTER } FUNCTION [владелец.] имя_функции
( [ { @имя_параметра скаляр_тип_данных
[=default]}[,...n]])
RETURNS скаляр_тип_данных
[WITH {ENCRIPTION | SCHEMABINDING} [,...n] ]
[AS]
BEGIN
<тело_функции>
RETURN скаляр_выражение
END
Рассмотрим назначение параметров команды.
Функция может содержать один или несколько входных параметров
либо не содержать ни одного. Каждый параметр должен иметь уникаль-
ное в пределах создаваемой функции имя и начинаться с символа ”@”.
После имени указывается тип данных параметра. Дополнительно можно
указать значение, которое будет автоматически присваиваться параметру
(default), если пользователь явно не указал значение соответствующего
параметра при вызове функции.
С помощью конструкции RETURNS скаляр_тип_данных указыва-
ется, какой тип данных будет иметь возвращаемое функцией значение.
Дополнительные параметры, с которыми должна быть создана функ-
ция, могут быть указаны посредством ключевого слова WITH. Благодаря
ключевому слову ENCRYPTION код команды, используемый для создания
функции, будет зашифрован, и никто не сможет просмотреть его. Эта
Курс
238
Основы SQL
возможность позволяет скрыть логику работы функции. Кроме того, в теле
функции может выполняться обращение к различным объектам базы
данных, а потому изменение или удаление соответствующих объектов мо-
жет привести к нарушению работы функции. Чтобы избежать этого, тре-
буется запретить внесение изменений, указав при создании этой функции
ключевое слово SCHEMABINDING.
Между ключевыми словами BEGIN…END указывается набор команд,
они и будут являться телом функции.
Когда в ходе выполнения кода функции встречается ключевое слово
RETURN, выполнение функции завершается и как результат ее вычисления
возвращается значение, указанное непосредственно после слова RETURN. От-
метим, что в теле функции разрешается использование множества команд
RETURN, которые могут возвращать различные значения. В качестве возвра-
щаемого значения допускаются как обычные константы, так и сложные вы-
ражения. Единственное условие – тип данных возвращаемого значения дол-
жен совпадать с типом данных, указанным после ключевого слова RETURNS.
Пример 11.1. Создать и применить функцию скалярного типа для
вычисления суммарного количества товара, поступившего за определен-
ную дату. Владелец функции – пользователь с именем user1.
ALTER FUNCTION user1.sales(@data DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @c INT
SET @c=(SELECT SUM(количество)
FROM Сделка
WHERE дата=@data)
RETURN (@c)
END
В качестве входного параметра используется дата. Функция возвра-
щает значение целого типа, полученное из оператора SELECT путем сумми-
рования количества товара из таблицы Сделка. Условием отбора записей
для суммирования является равенство даты сделки значению входного
параметра функции.
Проиллюстрируем обращение к функции пользователя: определим
количество товара, поступившего за 02.11.01:
DECLARE @kol INT
SET @kol=user1.sales ('02.11.01')
SELECT @kol
239
Лекция 11 Функции пользователя
Функции Inline
Создание и изменение функции этого типа выполняется с помощью
команды:
<определение_табл_функции>::=
{CREATE | ALTER } FUNCTION [владелец.] имя_функции
( [ { @имя_параметра скаляр_тип_данных
[=default]}[,...n]])
RETURNS TABLE
[ WITH {ENCRIPTION | SCHEMABINDING} [,...n] ]
[AS]
RETURN [(] SELECT_оператор [)]
Основная часть параметров, используемых при создании табличных
функций, аналогична параметрам скалярной функции. Тем не менее, со-
здание табличных функций имеет свою специфику.
После ключевого слова RETURNS всегда должно указываться ключе-
вое слово TABLE. Таким образом, функция данного типа должна строго
возвращать значение типа данных table. Структура возвращаемого
значения типа table не указывается явно при описании собственно ти-
па данных. Вместо этого сервер будет автоматически использовать для
возвращаемого значения table структуру, возвращаемую запросом
SELECT, который является единственной командой функции.
Особенность функции данного типа заключается в том, что структу-
ра значения table создается автоматически в ходе выполнения запроса,
а не указывается явно при определении типа после ключевого слова
RETURNS.
Возвращаемое функцией значение типа table может быть использо-
вано непосредственно в запросе, т.е. в разделе FROM.
Пример 11.2. Создать и применить функцию табличного типа для
определения двух наименований товара с наибольшим остатком.
ALTER FUNCTION user1.itog()
RETURNS TABLE
AS
RETURN (SELECT TOP 2 Товар.Название
FROM Товар INNER JOIN Склад
ON Товар.КодТовара = Склад.КодТовара
ORDER BY Склад.Остаток DESC)
Курс
240
Основы SQL