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

Цель: научиться работать с пользовательскими функциями

Теперь рассмотрим создание и применение пользовательских функций. В БД "Microsoft SQL Server 2008" все пользовательские функции находятся в папке "Functions" расположенной в папке "Programmability" в обозревателе объектов ( рис. 12.1).

Начнем с создания скалярных пользовательских функций. Для создания новой скалярной пользовательской функции в обозревателе объектов, в БД "Students", в папке "Programmability", щелкните ПКМ по папке "Functions" и в появившемся меню выберите пункт "New/Scalar-valued Function". Появится окно новой скалярной пользовательской функции ( рис. 12.2)

Синтаксис скалярной пользовательской функции похож на синтаксис хранимой процедуры (см. "Интерфейс информационных систем. Создание интерфейса пользователя" ). Однако имеется ряд существенных отличий ( рис. 12.2):

1. Область определения имени функции (Inline_Function_Name);
2. Параметры, передаваемые в процедуру (@Param1). Определение параметров аналогично определению параметров в хранимой процедуре (см. "Таблицы. Типы данных и свойства полей. Создание и заполнение таблиц" );
3. Тип данных значения возвращаемого процедурой;
4. Область объявления переменных, используемых внутри функции. Объявление переменных имеет следующий синтаксис:

   DECLARE @<Имя переменной> <Тип данных>
5. Тело самой пользовательской функции, содержит команды языка программирования запросов T-SQL;
6. Команда RETURN возвращающая результат выполнения функции. Имеет следующий синтаксис:

   RETURN @<Имя переменной с результатом>

   Переменная должна быть того же типа данных, который был указан в пункте 3.

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

Рассмотрим более подробно код данной скалярной пользовательской функции ( рис. 12.3):

1. CREATE FUNCTION [Функция средних трех величин] - определяет имя создаваемой функции как "Функция средних трех величин";
2. @Value1 Real, @Value2, @Value3 - определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить целые числа (Тип данных Int);
3. RETURNS Real - показывает, что функция возвращает дробные числа (Тип данных Real);
4. DECLARE @Result Real - объявляется переменная @Result для хранения результата работы функции, то есть дробного числа (Тип данных Real);
5. SELECT @Result=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и помещает результат в переменную @Result ;
6. RETURN @Result - возвращает значение переменной @Result.

Остальные фрагменты кода рассмотрены выше ( рис. 12.2).

Для создания функции, выполним вышеописанный код, нажав кнопку

(Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение "Command(s) completed successfully.". Закройте окно с кодом, щелкнув мышью по кнопке закрытиярасположенной в верхнем правом углу окна с кодом функции.

Проверим работу созданной скалярной пользовательской функции. Для запуска пользовательской функции необходимо создать новый пустой запрос, нажав на кнопку

(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду SELECT dbo.[Функция средних трех величин] (3, 5, 4) и нажмите кнопкуна панели инструментов ( рис. 12.4).

В нижней части окна с кодом появится результат выполнения новой скалярной пользовательской функции: 4 ( рис. 12.4).