Внедренные табличные функции

Лабораторная работа № 5. Объекты SQL Server.

Пользовательские функции

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

Пользовательские функции обладают следующими преимущества:

· С их помощью можно внедрить в запросы сложную логику.

· Создавая новые функции, можно проектировать сложные выражения.

· Эти функции обладают всеми достоинствами представлений, поскольку могут использоваться в предложении FROM инструкции SELECT и в выражениях и могут быть задействованы в схеме. К тому же пользовательские функции могут принимать параметры, в то время как представления — нет.

· Они обладают достоинствами хранимых процедур, так как могут быть скомпилированы и оптимизированы таким же способом.

Главным аргументом против использования пользовательских функций является вопрос переносимости. Пользовательские функции привязаны к SQL Server, и любую базу данных, использующую множество таких функций, будет сложно или даже невозможно перенести на другую платформу СУБД без существенной переработки. Эта задача усложняется тем, что также должны быть переписаны и все инструкции SELECT, в которые внедрены пользовательские функции. Если в будущем планируется развертывание базы данных на других платформах, то лучше заменить все пользовательские функции представлениями или хранимыми процедурами.

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

· Пользовательские функции могут возвращать значения, относящиеся к большинству типов данных SQL Server. Не допускается использовать в качестве типов возвращаемых значений лишь такие типы, как text, ntext, image, cursor и timestamp.

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

Пользовательские функции во многом аналогичны функциям, используемым в классических языках программирования. Эти функции принимают несколько параметров и возвращают одно значение. Различия между пользовательскими функциями SQL Server и функциями многих процедурных языков программирования состоят в том, что в них передача параметров осуществляется по значению, поэтому для них не предусмотрен способ передачи параметров, подобный применению ссылки или передачи указателей. Тем не менее пользовательские функции удобны тем, что позволяют возвращать данные в виде специальной таблицы.

Пользовательские функции подразделяются на три типа:

· Скалярные, возвращающие одно значение.

· Внедренные табличные, аналогичные представлениям.

· Сложные табличные, создающие в программном коде результирующий набор данных.

Скалярные функции

Скалярными называют те функции, которые возвращают одно значение. Эти функции могут принимать множество параметров, выполнять вычисления, но в результате выдают одно значение. Эти функции могут использоваться в любых выражениях, даже участвующих в ограничениях проверки. Значение возвращается функцией с помощью оператора return — эта команда должна завершать скалярную функцию.

В скалярных пользовательских функциях не допускаются операции обновления базы данных, но в то же время они могут работать с локальными временными таблицами. Они не могут возвращать данные BLOB (двоичные большие объекты) таких типов, как text, image и ntext, равно как табличные переменные и курсоры.

Скалярные функции создаются, изменяются и удаляются с помощью тех же инструкций DDL, что и другие объекты, хотя синтаксис немного отличается, чтобы определить возвращаемое значение:

CREATE FUNCTION имя_функции (входные_параметры)
RETURNS тип_данных
AS
BEGIN
текст_ функции
RETURN выражение
END

В списке входных параметров должны быть указаны типы данных и, в случае необходимости, значения по умолчанию, аналогично хранимым процедурам (параметр = умолчание). Параметры функции отличаются от параметров хранимых процедур тем, что даже если определены значения по умолчанию, параметры все равно должны присутствовать в вызове функции (т.е. параметры с определенными по умолчанию значениями все равно обязательны). Чтобы запросить значение по умолчанию при вызове функции, ей передается ключевое слово default.

Следующая скалярная функция выполняет простую арифметическую операцию; ее второй параметр имеет значение по умолчанию:

CREATE FUNCTION dbo.Multiply (@A int, @B int = 3)

RETURNS INT

BEGIN

RETURN @A * @B

END

Скалярные функции могут использоваться в любом месте выражений, где допустимо одно значение. Пользовательские скалярные функции должны всегда вызываться с помощью двухкомпонентного имени (владелец.имя). В следующем примере продемонстрирован вызов ранее созданной функции Multiply:

SELECT dbo.Multiply(3,4)

SELECT dbo.Multiply(7, DEFAULT)

Следующий код создает функцию, возвращающую имя заданного продавца в формате Фамилия И.О.

CREATE FUNCTION getProdavecFIO (@ProdavecID int)

RETURNS varchar(50)

AS

BEGIN

DECLARE @result varchar(50)

SELECT @result = SurName + ' ' + SUBSTRING(FirstName, 1, 1) + '.' + SUBSTRING(ParentName, 1, 1) + '.'

FROM Prodavec

WHERE ProdavecID = @ProdavecID

RETURN @result

END

Протестируем созданную функцию:

SELECT dbo.getProdavecFIO(1) AS ProdavecFIO –- данные по продавцу с идентификатором 1

SELECT dbo.getProdavecFIO(ProdavecID) AS ProdavecFIO

FROM Prodavec

ORDER BY SurName, FirstName

-- данные по всем продавцам из таблицы

Задание для самостоятельной работы: Создайте скалярные пользовательские функции, возвращающие:

· Количество товаров в каждой группе товаров по заданному уникальному идентификатору товара;

· Суммарную стоимость товаров в заданном заказе (Order) по коду заказа.

Внедренные табличные функции

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

Внедренная табличная функция не имеет в своем теле блока BEGIN ... END — вместо этого возвращается результирующий набор данных инструкции SELECT в виде таблицы с заданным именем:

CREATE FUNCTION имя_функции (параметры)
RETURNS Table AS
RETURN (инструкция_SELECT)

Создадим функцию, возвращающую данные по товарам.

CREATE FUNCTION getPriceList ()

RETURNS TABLE

AS

RETURN

(

SELECT TovarID, TovarName, ProizvoditelName, CurrentPrice, GrupName

FROM Tovar t

INNER JOIN Grup g on t.GrupID = g.GrupID

INNER JOIN Proizvoditel p on p.ProizvoditelID = t.ProizvoditelID

)

Для извлечения данных с помощью функции getPriceList вызовите ее в предложении FROM инструкции SELECT:

SELECT *

FROM dbo.getPriceList()

ORDER BY GrupName, TovarName

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

CREATE FUNCTION getTovary (@GrupID SMALLINT)

RETURNS TABLE

AS

RETURN

(

SELECT TovarID, TovarName, ProizvoditelName, CurrentPrice

FROM Tovar t

INNER JOIN Proizvoditel p on p.ProizvoditelID = t.ProizvoditelID

WHERE t.GrupID = @GrupID

)

В качестве параметра передается уникальный идентификатор группы товаров, по которому скомпилированная инструкция SELECT в функции вернет только товары, принадлежащие указанной группе:

select * from dbo.GetTovary(3)