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

Цели:

1. Изучить порядок создания пользовательских функций
2. Освоить применение пользовательских функций

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

1. Скалярные функции - функции, которые возвращают число или текст, то есть одно или несколько значений;
2. Табличные функции - функции, которые выводят результат в виде таблицы.

Для создания новой пользовательской функции используется команда CREATE FUNCTION имеющая следующий синтаксис:

CREATE FUNCTION <Имя функции>
([@<Параметр1> <Тип1>[=<Значение1>],
 @<Параметр2> <Тип2>[=<Значение2>], . . .])
RETURNS <Тип>/TABLE
AS
RETURN([<Команды SQL>])

Здесь:

• Имя функции - имя создаваемой пользовательской функции.
• Параметр1, Параметр2, .. - параметры передаваемые в функцию.
• Значение1, Значение2, … - значения параметров по умолчанию.
• Тип1, Тип2, . .. - типы данных параметров.

После служебного слова RETURNS в скалярных функциях ставится тип данных результата, который возвращает скалярная функция, либо ставится служебное слово TABLE в табличных функциях.

После служебного слова RETURN ставится SQL команда самой функции.

Замечание: После служебного слова RETURN может быть несколько команд, которые располагаются между словами BEGIN и END. В этом случае служебное слово RETURN не ставится.

Замечание: Тип данных параметра должен совпадать с типом данных выражения, в котором он используется.

Замечание: Если используются несколько SQL команд и BEGIN и END, то перед END нужно ставить команду RETURN <результат функции>.

Пример (скалярная пользовательская функция): Функция для вычисления среднего 3 чисел:

CREATE FUNCTION Среднее
(@X1 Int,@X2 Int,@X3 Int)
RETURNS Real
AS
BEGIN 
DECLARE @Res Real
SET @Res =(@X1+@X2+@X3)/3
RETURN @Res
END

Замечание: Команда DECLARE создает переменную Res для хранения дробных чисел (тип данных Real ).

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

CREATE FUNCTION Среднее
(@X1 Int,@X2 Int,@X3 Int)
RETURNS Real
AS
RETURN (SELECT (@X1+@X2+@X3)/3)

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

SELECT Среднее (6, 3, 3)

Результат будет 4.

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

CREATE FUNCTION Возраст
(@CurDate Date)
RETURNS TABLE 
AS
RETURN (SELECT ФИО, [Дата рождения], Возраст = DATEDIFF (yy,[Дата рождения], @CurDate)
FROM Студенты)

Данная функция вызывается следующим образом:

SELECT * FROM Возраст ('12/17/2007')

В результате отобразятся студенты с их возрастом на 17 декабря 2007 года.

На этом мы заканчиваем рассмотрение пользовательских функций. Дополнительную информацию можно найти в "лабораторной работе №6" .