Типы определяемых пользователем функции
Любая определяемая пользователем функция состоит из двух частей: заголовка и текста. Функция принимает ноль и более параметров и возвращает либо скалярное значение, либо таблицу.
Заголовок определяет:
· имя функции с необязательным именем схемы или владельца;
· имя и тип данных параметров функции;
· тип данных возвращаемого значения и необязательное имя;
Текст определяет действие или логику, которую выполняет функция, и может содержать одну или несколько инструкций Transact-SQL, реализующих логику функции.
Пользовательские функции MS SQL Server подразделяются на 3 категории:
· Скалярные функции
· Возвращающие табличное значение функции
· Встроенные функцииСкалярные функции
Пользовательские скалярные функции возвращают одно значение типа данных, заданного в предложении RETURNS. Встроенная скалярная функция не имеет тела, скалярное значение является результатом одной инструкции. Скалярная функция из нескольких инструкций имеет тело, ограниченное блоком BEGIN...END, и содержит последовательность инструкций Transact-SQL, возвращающих одно значение. Такие функции могут возвращать любые типы данных, кроме text, ntext, image, cursor и timestamp.
CREATE FUNCTION function_name
(
[ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH [ ENCRYPTION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
function_name
Имя пользовательской функции. Скобки после имени функции обязательны даже при отсутствии параметров
@parameter_name
Параметр пользовательской функции. Может быть объявлен один или несколько параметров.
Для функций допускается не более 1 024 параметров. При выполнении функции значение каждого из объявленных параметров должно быть указано пользователем, если для этого параметра не определено значение по умолчанию.
Имя параметра всегда должно начинаться со знака @. Параметры локальны в пределах функции, то есть в разных функциях могут быть использованы одинаковые имена параметров. Аргументы могут использоваться только вместо констант. Они не могут использоваться вместо имен таблиц, имен столбцов или имен других объектов базы данных.
Parameter_data_type
Тип данных параметра. Для параметров функций Transact-SQL допустимы любые типы данных за исключением типа данных timestamp, нескалярные типы cursor и table также не могут быть указаны в качестве типов данных параметров.
[ = default ]
Значение параметра по умолчанию. Если определено значение default, функция выполняется даже в том случае, если для данного параметра значение не указано.
Если параметр функции имеет значение по умолчанию, то для него должно быть указано ключевое слово DEFAULT для получения функцией значения по умолчанию. Применение ключевого слова DEFAULT следует отличать от использования аргументов со значениями по умолчанию в хранимых процедурах, когда не указанный аргумент неявно принимает значение по умолчанию.
return_data_type
Тип возвращаемого значения скалярной пользовательской функции. Для возвращаемых значений функций Transact-SQL допустимы любые типы данных за исключением типа данных timestamp, нескалярные типы cursor и table также не могут быть указаны в качестве типов данных параметров.
ENCRYPTION
Указывает, что компонент Database Engine преобразует исходный текст инструкции CREATE FUNCTION в скрытый формат. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст.
SCHEMABINDING
Указывает, что функция привязана к объектам базы данных, которые содержат ссылки на нее. Если аргумент SCHEMABINDING указан, нельзя изменить базовые объекты таким способом, который может повлиять на определение функции. Сначала нужно изменить или удалить само определение функции, чтобы удалить зависимости от объекта, который требуется изменить.
Привязка функции к объектам, на которые она ссылается, удаляется только в следующих случаях:
· При удалении функции.
· При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.
EXECUTE AS
Определяет контекст безопасности, в котором должна быть выполнена функция, т.е. какую учетную запись компонент Database Engine использует при проверке разрешений на объекты, на которые ссылается функция. Это повышает гибкость и безопасность управления разрешениями на цепочки владения между пользовательскими функциями и объектами, на которые они ссылаются.
Пользователям необходимо будет предоставлять только разрешения на саму функцию, без выдачи явных разрешений на объекты, на которые она ссылается. Только пользователь, от имени которого выполняется модуль, должен будет иметь разрешения на объекты, к которым этот модуль обращается.
Допустимые значения EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }.
Function_body
Указывает серию инструкций Transact-SQL, которая в совокупности не вызывает побочных эффектов вроде изменения содержимого таблиц и формирует возвращаемое значение функции.
Для скалярных функций аргумент function_body представляет собой серию инструкций Transact-SQL, которые в совокупности вычисляют скалярное значение.
scalar_expression
Указывает скалярное значение, возвращаемое скалярной функцией.
Пример:
Следующий пример демонстрирует создание скалярной функции из нескольких инструкций. Функция имеет один входной параметр ProductCategoryID и возвращает одно значение – максимальную цену товара указанной категории.
CREATE FUNCTION getMaxPrice (@ProductCategoryID int)
RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = MAX(Price)
FROM Product
WHERE ProductCategoryID = @ProductCategoryID
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
--Использование созданной функции
SELECT ProductCategoryName, getMaxPrice (ProductCategoryID) AS MaxPrice
FROM ProductCategory
WHERE ProductCategoryID = 1;