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

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

Функции могут вызываться на выполнение как непосредственно встроенные в запрос (например, могут входить в состав оператора SELECT), а хранимые процедуры не предоставляют такой возможности.

Итак, с учетом сказанного, создадим простую пользовательскую функцию.

По мнению автора, один из наиболее удобных способов использования функции состоит в том, что с ее помощью подготавливг1Ются данные для ввода в поле типа datetime информации о том, что некоторое событие произошло в какой-то определенный день. Обычно при решении такой задачи возникает проблема, связанная с тем, что в поле типа datetime имеется конкретная информация о времени суток, в связи с наличием которой затрудняется сравнение хранимого значения со значением, содержащим только одну дату. Безусловно, с этой проблемой мы уже сталкивались в предьщущих главах, когда требовалось реализовать некоторые операции сравнения значений дат.

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

DECLARE ©Counter int

SET ©Counter = 1 WHILE ©Counter <= 10 BEGIN

INSERT INTO Orders

VALUES (1, DATEADD(mi,©Counter,GETDATE0), 1)

SET ©Counter = ©Counter + 1

Следует отметить, что в таблицах Employees и Customers должны находиться согласующиеся строки. Это условие будет соблюдаться в случае выполнения всех примеров в главах 4 и 5, но если, скажем, при наличии в таблице Customers строки со значением CustomerNo, равным 1, в таблице Employees будет отсутствовать строка со значением EmployeelD, равным 1, возникнет нарушение ограничения внешнего ключа (которое определено с помощью внешних ключей, которые были добавлены в главе 5).

Итак, при выполнении этого сценария происходит вставка десяти строк, в каждой из которых содержится сегодняшняя дата, но строки, следующие друг за другом, отличаются по времени на одну минуту.

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

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

SELECT * FROM Orders

WHERE OrderDate = GETDATE()

Ho, к сожалению, этот запрос не возвратит ни одной строки. Это связано с тем, что функция GETDATE () возвращает не только дату, но и текущее время, вплоть до миллисекунды. Это означает, что вероятность получения каких-либо данных с помощью запроса, в котором используется функция GETDATE () в чистом виде, является очень низкой, даже если интересующее нас событие произошло в тот же день (чтобы операция сравнения завершилась успешно, сравниваемые события должны произойти в одну и ту же минуту, если используются данные о времени типа smalldatetime, и в течение одной миллисекунды, если используется полный формат datetime).

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

Соответствующий оператор может выглядеть примерно так:

SELECT * FROM Orders

WHERE CONVERT(varchar(12), OrderDate, 101) = CONVERT(varchar(12), GETDATE(), 101)

Ha сей раз будут получены все строки, в которых столбец OrderDate содержит сегодняшнюю дату, независимо от того, в какое время дня был введен заказ. Но, к сожалению, этот код нельзя назвать наиболее удобным для чтения. А если в программе приходится предусматривать подобные операции сравнения для целого ряда дат, то соотве1Ствующий сценарий приобретает действительно сложный вид.

Поэтому рассмотрим способ вьшолнения тех же действий, но с помощью простой пользовательской функции. Вначале необходимо создать саму функцию. Эта задача осуществляется с помощью оператора нового типа CREATE FUNCTION, а применяемый при этом синтаксис во многом напоминает синтаксис создания хранимой процедуры. Например, указанную функцию можно реализовать с помощью такого кода:

CREATE FUNCTION dbo.DayOnly(©Date datetime)

RETURNS varchar(12)

BEGIN

RETURN CONVERT(varchar(12), ©Date, 101)

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

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

SELECT * FROM Orders

WHERE dbo.DayOnly(OrderDate) = dbo.DayOnly(GETDATE())

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

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

Пользовательские функции, которые возвращают таблицу

Возможности применения пользовательских функций в СУБД SQL Server не ограничиваются лишь возвратом с их помощью скалярных значений. Эти функции обеспечивают возврат гораздо более важных объектов - таблиц. Из этого следуют такие возможности, которые трудно представить себе сразу же, но отметим, что возвращаемая таблица в большинстве обстоятельств доступна для применения в основном с использованием таких же способов, как и любая другая таблица. Результаты, возвращаемые функцией, можно включать в состав операндов операции JOIN и даже применять к ним условия конструкций WHERE. Благодаря этому открываются действительно заманчивые перспективы.

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

USE AdventureWorks GO

CREATE FUNCTION dbo.fnContactList()

RETURNS TABLE

RETURN (SELECT ContactID,

LastName + , + FirstName AS Name, EMailAddress AS email FROM Person.Contact)

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