С этого момента созданная функция может использоваться по такому же принципу, как таблица:

SELECT *

FROM fnContactListО

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

CREATE FUNCTION dbo.fnContactSearch(©LastName nvarchar(50))

RETURNS TABLE

RETURN (SELECT ContactID,

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

WHERE LastName LIKE ©LastName + %)

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

SELECT *

FROM fnContactSearch(Ad)

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

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

В данном примере приходится сталкиваться с одной из очень распространенных проблем в мире реляционных баз данных - с задачей построения иерархий. Предпо-

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

На первый взгляд эта задача может показаться довольно простой. Например, если бы требовалось найти всех служащих, которыми руководит служащий с идентификатором Terri Duffy, то можно было бы написать запрос, в котором выполняется соединение таблицы Employee с самой собой, а также формируется соединение с таблицей Contacts для получения имен вместо обычных идентификаторов, примерно так: Use AdventureWorks

SELECT е.EmployeelD, ее.LastName, ее.FirstName, е.ManagerlD FROM HumanResources.Employee AS e JOIN Person.Contact AS ec

ON e.ContactID = ec.ContactID JOIN HumanResources.Employee AS m

ON m.EmployeelD = e.ManagerlD JOIN Person.Contact AS mc

ON m.ContactID = mc.ContactID WHERE mc.LastName = Duffy AND mc.FirstName = Terri

Опять-таки может показаться, что по условиям задачи должны быть получены примерно такие результаты:

EmployeelD LastName FirstName ManagerlD

3 Tamburello Roberto 12

(1 row(s) affected)

Однако задача на этом не исчерпывается. Требуется функция, которая возвращала бы информацию обо всех уровнях иерархии, расположенных ниже заданного значения идентификатора служащего Terri Duffy, иными словами, не только о служащих, которые подчиняются Terri, но и о тех, которые подчиняются подчиненным Terri Duffy, и т.д. В частности, анализ всех данных в базе данных AdventureWorks показывает, что служащий Robert Tamburello, который непосредственно подчиняется служащему Terri Duffy, имеет в своем подчинении других служащих, но информация об этом не обнаруживается в результатах этого запроса.

Безусловно, читатели могут предположить, чтю реилепие такой утючненнюй задачи не представляет какой-либо сложности, - достаточно лишь предусмотреть еще одно соединение таблицы Employees с самой собой. Безусловно, подобное решение было бы осуществимо при наличии весьма небольшого набора данных или в любой другой ситуации, когда количество уровней иерархии ограничено, ню такие условия складываются далеко не всегда. Но вполне может быть так, чтю в компании есть служащие, подчиняющиеся служащему Terri Duffy, а также другие служащие, подчиняющиеся служащим, котюрые подчини-

тотся служащему Terri Duffy, и т.д., иными словами, глубина дерева подчиненности может увешчиеатыл практически неограниченно. Как же поступить в таком случае Поиску ответа на этот вопрос будет посвящена остальная часть данного раздела.

В действительности требуется функция, которая возвращала бы информацию обо всех уровнях иерархии, расположенных ниже заданного значения идентификатора служащего EmployeelD (следовательно, идентификатора служащего, выполняющего роль руководителя, - ManagerlD). Способ, позволяющий наилучшим образом решить эту задачу, представляет собой классический пример рекурсии. Если в каком-то блоке кода вызьтается сам этот код, то такой вызов рассматривается как рекурсивный. В этой главе уже рассматривался такой пример рекурсивного кода, как хранимая процедура spTriangular. А в данном случае возникает задача, алгоритм решения которой может выглядеть так, как описано ниже.

1. Составить список всех служащих, которые подчиняются интересующему нас служащему, выполняющему роль руководителя.

2. Для каждого служащего, внесенного в список в шаге 1, составить список подчиняющихся ему служащих.

3. Повторять шаг 2 до тех пор, пока не удастся больше найти служащих, подчиняющихся кому-либо служащих, внесенньсс в списки в ходе вьшолнения предыдущих шагов.

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

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

Реализуем описанный замысел рекурсивного алгоритма в виде функции. Следует отметить, что в объявление этой функции внесено несколько изменений. Дело в том, что на этот раз требуется связать с возвращаемым значением имя переменной (в данном случае ©Reports), поскольку к нему приходится обращаться каждый раз, когда для выработки результата могут использоваться различные операторы. Кроме того, необходимо объявить возвращаемую таблицу; это позволяет СУБД SQL Server определить, предпринимается ли попытка вставки данных в эту таблицу перед ее возвратом в вызывающую процедуру:

CREATE FUNCTION dbo.fnGetReports (©EmployeelD AS int) RETURNS ©Reports TABLE (

EmployeelD int NOT NULL,

ManagerlD int NULL

BEGIN