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

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

Создайте новую скалярную пользовательскую функцию, так как об этом сказано выше. В окне новой пользовательской функции наберите следующий код ( рис. 12.5):

увеличить изображение
Рис. 12.5.

Перейдем к рассмотрению вышеприведенного кода ( рис. 12.5). Код состоит из следующих групп команд:

1. CREATE FUNCTION [Последний день месяца] - определяет имя создаваемой функции как "Последний день месяца";
2. @MyDate - определяют параметр процедуры MyDate. Параметру можно присвоить значения дат или времени (Тип данных DateTime);
3. RETURNS DateTime - показывает, что функция возвращает дату или время (Тип данных DateTime);
4. DECLARE @Year Int, DECLARE @Month Int, DECLARE @Day Int - объявляются переменные @Year, @Month и @Day для хранения целочисленных значений года, месяца и дня введенной даты (Тип данных Int).

   DECLARE @TmpDate VarChar(10) объявляет переменную "TmpDate" для хранения промежуточного значения даты в строке длинной до 10 символов (Тип данных VarChar(10)).

   DECLARE @Result DateTime объявляет переменную "Result" для хранения результата - даты последнего дня месяца (Тип данных DateTime).
5. SET @Year=DatePart(yy, @MyDate), SET @Month=DatePart(mm, @MyDate), SET @Day=DatePart(dd, @MyDate) - определяются части введенной даты и помещаются в переменныне @Year, @Month и @Day. Для определения частей даты используется функция DatePart, имеющая следующий синтаксис: DatePart(<часть даты>, <дата>). Здесь "часть даты" - это закодированная специальными символами определяемая часть даты (yy - год, mm - месяц, dd - день), "дата" - это дата, части которой определяем.

6. IF @Month=12
   	BEGIN
   		SET @Month=1
   		SET @Year=@Year+1
   	END
   ELSE
   	BEGIN
   		SET @Month=@Month+1
   	END

   Вышепреведенный фрагмент кода выполняет следующие действия: Если номер месяца равен 12 то установить номер месяца ( @Month ) равным 1 и увеличить год ( @Year ) на 1, иначе увеличить месяц на 1.
7. SET @TmpDate=Convert(Varchar, @Month)+'/01/'+Convert(Varchar, @Year), SET @Result=Convert(DateTime, @TmpDate) - переводит числовые значения даты в дату в строковом формате и записывает ее в переменную @TmpDate, затем переводит дату в строковом формате в тип данных даты и времени и помещает ее в переменную @Result. Для конвертации используется функция Convert, имеющая следующий синтаксис:

   Convert(<тип данных>, <значение>), здесь "тип данных" это тип данных в который переводится "значение".
8. SET @Result=DateAdd(dd, -1, @Result) - из даты, хранимой в переменной @Result вычитается 1 день, для этого используется функция DateAdd, имеющая следующий синтаксис:

   DateAdd(<часть даты>, <количество периодов>, <дата>) - здесь "часть даты" - это закодированная специальными символами определяемая часть даты (см. функцию DatePart ), "количество периодов" - это количество частей даты прибавляемой к введенной дате (параметр "дата" ).
9. RETURN @Result - возвращает значение, хранимое в переменной @Result.

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

Проверим работу функции "Последний день месяца" выполнив ее. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT dbo.[Последний день месяца] ('12/07/08') и нажмите кнопку

увеличить изображение
Рис. 12.6.

Появится результат выполнения новой скалярной пользовательской функции: 2008-12-31 ( рис. 12.6).

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

увеличить изображение
Рис. 12.7.

Рассмотрим структуру кода табличной пользовательской функции. Табличная пользовательская функция состоит из следующих разделов:

1. Область определения имени функции (Inline_Function_Name);
2. Параметры, передаваемые в процедуру ( @Param1, @Param2 );
3. RETURNS TABLE показывает что функция является табличной, то есть возвращает таблицу;
4. Тело самой пользовательской функции, состоит из команды SELECT языка программирования запросов T-SQL.

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

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

увеличить изображение
Рис. 12.8.

Из кода представленного на рис. 12.8 видно, что данная табличная функция не имеет параметров и реализуется командой

SELECT ФИО, [Дата рождения], Возраст = DateDiff(yy, [Дата рождения], GetDate())
FROM Студенты

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

DateDiff(<часть даты>, <начальная дата>, <конечная дата>).

Здесь "часть даты" - это закодированные специальными символами единицы измерения (часть даты) (yy - год, mm - месяц, dd - день), "начальная дата" - дата начала периода и "конечная дата" - дата конца периода. В нашем случае в качестве начальной даты берем дату рождения студента, а в качестве конечной даты берем текущую дату (функция GetDate() ).

Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией. После появления сообщения "Command(s) completed successfully." закройте окно с кодом.

Проверим работоспособность новой табличной пользовательской функции. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT * FROM dbo.[Функция отбора по возрасту]() и нажмите кнопку

увеличить изображение
Рис. 12.9.

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

Замечание: Обратите внимание на тот факт, что мы работаем с табличной функцией как с обыкновенной таблицей.

На этом мы заканчиваем рассмотрение пользовательских функций и переходим к рассмотрению целостности данных, диаграмм и триггеров. По окончании выполнения главы 6 обозреватель объектов будет иметь следующий вид ( рис. 12.10):

Рис. 12.10.